Help Needed: Generating Summary Table in PDF Invoice

[yomri001]

Please I am using AppSheet to manage orders for my business and have a bot set up to generate PDF invoices. I need to include a summary table in the PDF that shows Roll_QTY values for each order and the count of how many times each Roll_QTY appears in the order.

Tables:

1. Order Details:

   • Columns: OrderDetails_ID, Ref_Order_ID, Roll_QTY, Roll_Price_Per_unit, Ref_Fabric, Ref_Design, Ref_Color, Total_Price_Roll, OD_Cancel

2. Order:

   • Columns: Order_ID, Order_Total_Price, Order_Status, Order_Delivery_Place, Order_Date, Ref_Client_ID, Is_Delivered, Is_Canceled, Is_Invoice_Created, Invoice_URL, Is_Paid, Payment_Method, Discount, Order_Total_After_Discount

Goal: For each order, I want to include in the PDF a summary table with the following structure:

• First column: Unique Roll_QTY values.
• Second column: Count of how many times each unique Roll_QTY value appears in the selected order.

Example: If an order has 3 products with Roll_QTY values of 20, 20, and 30, the table should show:

• 20 in the first column and 2 in the second column (because 20 appears twice).
• 30 in the first column and 1 in the second column (because 30 appears once).

Issue: I'm having trouble creating the template expressions needed to generate this summary table in the PDF. When I attempt to use a Start expression in my Google Docs template, it requires a list of key references, which I can't seem to produce correctly with my current setup.

Request: Could someone guide me on how to set up the necessary Google Docs template to achieve this summary table in the PDF? Any detailed steps or expressions would be greatly appreciated.

Thank you! 🙏❤

[JMPeterson]

You should be able to do something along the lines of <<START: FILTER(Order,TRUE)>> at the very beginning followed by whatever table structure you want (<<[Order_Total_Price]>>, <<[Order_Date]>> etc.). Then a <<START:[Related Order Details]>> followed by the values you want (<<[Roll_QTY]>>, <<COUNT(FILTER(Order Details,[_THISROW].]Roll_QTY]=[Roll_QTY]))>> etc.). Then the two <<END>>, one after the last column in the Related Order Details, and the second after everything else to close out the initial Order <<START>>.

[yomri001]

Thank you @JMPeterson for your quick reply, but i think this will show duplicates values isn't it?

If we have values of 20, 20, 20, 30, 40 as roll_QTY related to the same order, and followed your method, i think we'll have this as a result:

20, 3

20, 3

20, 3

30, 1

40, 1

Sorry for the disturbing 🙏🏻

[JMPeterson]

No worries. Great catch. You should be able to wrap that SELECT() in a UNIQUE() to get it down to just one of each quantity type.

[yomri001]

Sorry but in your example, there is no SELECT statement, I really don't know where the UNIQUE should be to make that work, also I tried to use the example without the UNIQUE but it didn't work ... 😢

[TeeSee1]

This should help you.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Single-category-headers-in-workflow-templates-fo...

[yomri001]

Thank you so much @TeeSee1 for your reply, I tried the example on the post but It didn't get me to the result I want:

Here is the code: 

And here is the result:

I know that I have to change the code on the count column, it's just that I didn't know how to use the count in that column.

🙏🙏🙏 Thank you soo much for your time 

[TeeSee1 · Accepted solution]

You should use Steve's Formula.

Something like this....

Template

Result

[yomri001]

I can't thank you enough @TeeSee1 again for your help, THANK YOU SO MUCH, I REALLY APPRECIATE YOUR HELP 🙏 that really solved my problem