Can someone help me with de-referencing table...

references
(Brady Lovell) #1

Can someone help me with de-referencing tables to a Google Docs template?

-> I have a table called ‘Load Board’ that includes column’s [Customer], [City], and [State].

-> I have a table called ‘Customers’ that includes the columns [Name], [Street], [City], [State], [Zip], and [Phone Number].

-> I have a gDoc template with <>, <>, and <>.

The gDocs template is pulling the data from the ‘Load Board’ columns like it should, but I also want to be able to include the [Street], [Zip], and [Phone Number] column’s of the ‘Customers’ table in the gDoc template.

I’ve read over the workflow templates help section, but am still having trouble. I’ve set the [Customer] column to a Ref column type, and reference the table ‘Customers’ inside the column definition.

I’ve set the gDoc to read…

<> <<[Customers].[Street]>> <>, <> <<[Customers].[Zip Code]>> <<[Customers].[Phone Number]>>

…and am getting nothing. When I delete all of the de-references, the workflow gets fired and the document gets generated and sent. But I need the document to have those de-referenced values in it. Anyone have any idea what I am doing wrong, or possibly over-looking? Any and all help will be greatly appreciated!

Thanks, -BL

(Brady Lovell) #2

@praveen I’ve been able to figure a lot out on my own. Yall do a fantastic job of having plenty of instructional material available. But this one here has got me beat. I’m having a hard time wrapping my mind around what’s causing this not to work. I may need to get someone to log in and take a look. The <<[Customers]… de-reference was a typo when I originally posted this thread.

Here’s is my exact gDoc expression…

<> <<[Customer].[Street]>> <<City_17>>, <<State_18>> <<[Customer].[Zip]>> <<[Customer].[Phone Number]>>

<> displays the [Customer] ‘Ref’ column from my ‘Load Board’ table. Customers include “(edit)”, “(edit)”, etc…where there are 30 rows can have the word “(edit)” or “(edit)” and then their corresponding addresses, zip codes, and phone numbers, within the ‘Customers’ table.

Not sure if having the same name like “(edit)” , “(edit)” , “(edit)” in the [Name] column of the ‘Customers’ table is causing the issue.

Does the [Name] column of the ‘Customers’ table have to be the same as the ‘Ref’ column [Customer] from the ‘Load Board’ table?

I don’t know. I’m stuck.

(Praveen Seshadri (AppSheet)) #3

Got it. A Ref column needs to hold a Key value of the referenced table. it sounds like you don’t have a Key.

In the app itself, if you click on a Ref column, it should take you to a corresponding Customer row detail. Does that happen correctly?

One good way to debug these things is to use Virtual Columns. Add a virtual column (CustomerStreet) with formula [Customer].[Street] Check that is actually holds the expected information (you can see it in the app or you can use the View Data button to see the data for this table).

(Praveen Seshadri (AppSheet)) #4

The Ref must identify a unique row in the other table

(Brady Lovell) #5

@praveen Thanks for steering me in the right direction, I have finally figured it out!

(Praveen Seshadri (AppSheet)) #6

You should use <<[Customer].[Street]>>

rather than <<[Customers].[Street]>>

You are taking the value of the [Customer] column (which is a reference) and de-referencing it (i.e. following the reference) to get the value of the corresponding [Street]