Appsheet reference to a child table in Bot created PDF - Google Sheets

[kwasco1]

All, I am working on an app that has an end result as an invoice.

It takes items from bills and converts them to Invoices. 

The table Structure is: 

Table: [Bills] is a child set of data to table: [Parent Bill]

Then Table: [Parent Bill] is a child to Table: [Invoices] 

I need to show all the data from all the Bills on the PDF that comes from Invoices. The first screen shot is showing the working table [Parent Bill] as the primary data source with the table showing [bills] data. 

This pdf works great for bill review and partial invoice review. 

But... when I one level above to the [invoices] table, I can not get my references straight, this is what I tried, but I am lost. 

This PDF comes from the [invoices] table but should show all [bills] details from all of the [Parent bill] tables. 

Thanks all for the help! 

[Suvrutt_Gurjar · Accepted solution]

---

@kwasco1 wrote:

Thank you, this is my first request to the community.

---

Welcome to the AppSheet community !

It is still not clear if the field names such as "QTY" ([Total Units]) etc. are from grandchild table or not. Anyway assuming those are from grandchild table, please try as below. Please share screenshots of grandchild table as well if needed.

if understanding of your requirement is correct, you wish to print all related records from the grandchild table "Bill" for an "Invoices" table record.

If so, please create  a VC called say [All Bills]  in the grandparent table "Invoices" with the following details of this VC configuration

 A) list type with 

B) element type as ref and

C) reference table name as "Bills" with

D) an app formula expression something like 

SPLIT(TEXT([Related Parent Bills][Related Bills by Parent Bill ID])," , ")

Then your template expression can be something like below

This bot will invoke from the grandparent "Invoices" table.

[Suvrutt_Gurjar]

if you could update what you are trying to achieve with the expression in the second table that is failing, the community could suggest better. Also please mention the names of the parent and child tables while describing your requirement. 

[kwasco1]

Thank you, this is my first request to the community. I have updated my original post to better detail my issue. 

[Suvrutt_Gurjar · Accepted solution]

---

@kwasco1 wrote:

Thank you, this is my first request to the community.

---

Welcome to the AppSheet community !

It is still not clear if the field names such as "QTY" ([Total Units]) etc. are from grandchild table or not. Anyway assuming those are from grandchild table, please try as below. Please share screenshots of grandchild table as well if needed.

if understanding of your requirement is correct, you wish to print all related records from the grandchild table "Bill" for an "Invoices" table record.

If so, please create  a VC called say [All Bills]  in the grandparent table "Invoices" with the following details of this VC configuration

 A) list type with 

B) element type as ref and

C) reference table name as "Bills" with

D) an app formula expression something like 

SPLIT(TEXT([Related Parent Bills][Related Bills by Parent Bill ID])," , ")

Then your template expression can be something like below

This bot will invoke from the grandparent "Invoices" table.

[kwasco1]

That worked well, thank you! and Yes, you had the tables correct, for the hierarchy. 

One more question, I have the Total Invoice column for each line, what is the best way to sum that? 

I appreciate all your help! 

[Suvrutt_Gurjar · Accepted solution]

Please try

SUM([All Bills][Total Invoice])

[kwasco1]

Thank you! I sincerely appreciate your help! 

[Suvrutt_Gurjar]

You are welcome. Best wishes for your app creation.

[kwasco1]

One more question, is there a way to sort the columns by date? I have added a date column to the PDF and would like to sort by that column. Thank you. 

[Suvrutt_Gurjar]

Please add a VC column called say [Related Bills by Work Date]  in the "Parent Bill" table with an expression something like 

ORDERBY([Related Bills by Parent Bill ID], [Date of Work], TRUE) if you wish records ordered by descending date 

or

ORDERBY([Related Bills by Parent Bill ID], [Date of Work], FALSE) if you wish records ordered by ascending date.

Then your [All Bills] expression in the "Invoices" table can be something like

SPLIT(TEXT([Related Parent Bills][Related Bills by Work Date]  )," , ")

ORDERBY() - AppSheet Help

Dereference expressions - AppSheet Help