Use multiple tables in one PDF Template

[RogerCreasy]

I have a pricing app that uses 4 tables -

1. JobBasics - Contains basic info about the job being priced e.g. customer, description, timestamp
2. JobDetails - Contains the details about the job being priced, i.e. the quantity of each item on the job
3. JobPrice - contains the price the customer is being charged for each item on the job. We store this, rather than using a lookup so that we can reference the then-current price we charged the customer
4. JobAmt - the Quantity from job details * the price from JobPrice - stored rather than calculated on the fly for reporting purposes. I am using this table as my primary table for the template.

Each table has a key column "PriceID". The relationship is 1:1 for the rows

Each Job can have 75+ items included. We are doing separate tables to get past the AppSheet 100 column limit.

I need to generate a PDF that can be sent to the customer. This report must have the quantity, price, and total. I am using a Google Doc with a table as the template. 

I cannot get the template to use multiple tables. I have successfully added the JobAmt data with no problem. S0, my basic template "works".

I tried in one Google Doc table cell:

<<Start: JobDetails[PriceID]>><<[PriceID].[Inches]>><<End>>

 I also tried using a SELECT a few different ways.

Can anyone give me pointers?

[Suvrutt_Gurjar]

You may want to further elaborate about tables how those are referenced to each other. In general you should be able to display related table rows( child table key values captured by the system generated reverse reference column ) by using <<START: [Related Childs] >>....... << END]>> statements to show related children records in the template.

For example, in the template below, "orders" table is child table of the "Customers" table and "Order details" table is child table of the "Orders" table.

As you can see, the customer specific orders are selected by the SELECT() statement on the Orders table and all teh line items ( or Order Details) for each "order" are selected by the [Related Order Details] which is a reverse reference column in the "Orders" table.

The article below very well explains the <<START>> expressions.

Use Start expressions in templates - AppSheet Help

[RogerCreasy]

@Suvrutt_Gurjar Thanks for the response. I didn't realize I had to set up relationships between the tables. 

I added a reference column to JobDetails that references JobAmt. then changed my template to

<<Start: [Related JobDetails]>><<[SideEjectPunch]>><<End>>

I no longer get errors. However, saving to JobDetails does not update the related table column. How do I accomplish this? 

[Suvrutt_Gurjar]

The related columns are system generated columns in the parent table. As you add child yable records the column will populate. 

[RogerCreasy]

The problem I am running into is that the Reference Column on the Child table is not updated with the Parent reference

[Suvrutt_Gurjar]

You may want to share a few screenshot.

Typically when a table is referenced through a reference column, all the rows of the parent or referenced table are displayed as enum option in the referenced column.

In the example below, the Order table references the Customers table through the reference column [Customer Name] in the Orders table.

The Orders table form's [Customer Name] column then displays all the Customer names as enum options so that the user can select any one of them. 

The Orders form looks like below.

[RogerCreasy]

I have a one-to-one relationship.  Using your example - Customer Name has a Key of 'a7b5406f' (your Order ID)
I need to automatically set the reference to customer on save

[RogerCreasy]

I have to span multiple tables for the data to get past the 100 column limit. I have to use all of these tables in one PDF template

[RogerCreasy]

I was trying to set a reference to a row in a different table before that row existed. I added actions to set the reference after the row was added.

@Suvrutt_Gurjar Thanks for your responses. They helped guide me to the solution

[Suvrutt_Gurjar]

Great to know you got it working. Thank you for the update.