Help with PDF Template Not Displaying Related SaleItems Data in AppShee

AppSheet AppSheet Q&A
, ,
1

Description
I’m struggling with an issue in my AppSheet app where the PDF generated by a bot isn’t displaying the LineItemID values from related SaleItems records, even though the data is visible in the AppSheet data preview. I’d really appreciate your expertise to help me resolve this.

Details

  • App Context: I’m building a Sales invoice system with two main tables:
  • Sales: Contains columns like InvoiceNumber, InvoiceDate, PatientID, TotalAmount, Discount, FinalAmount, PaymentMode, and InvoicePDFLink. It has a virtual column Related SaleItems (type: List, formula: REF_ROWS("SaleItems", "InvoiceNumber")) that lists related sale items.
  • SaleItems: Contains LineItemID (key), InvoiceNumber (Ref to Sales), ItemID, Category, BatchNumber, ExpiryDate, MRP, Quantity, Discount, and LineTotal. The InvoiceNumber links each sale item to a specific invoice.
  • Goal: Generate a PDF invoice that includes LineItemID values (and eventually other details like ItemID, Quantity, etc.) from the SaleItems table for each invoice.
  • Issue: The PDF shows InvoiceNumber and summary fields correctly, but the section for LineItemID values remains blank, despite the data being present in the preview.

What I’ve Tried

I’ve attempted the following steps without success:

  1. Basic Template with Loop: Used <<Start: [Related SaleItems]>> - <<[LineItemID]>> <<End>> in the template, but it showed nothing.
  2. SELECT Expression: Switched to <<Start: SELECT(SaleItems[LineItemID], [InvoiceNumber] = [_THISROW].[InvoiceNumber])>> - <<[LineItemID]>> <<End>>, which also resulted in a blank section.
  3. Rebuilt the Bot: Created a new bot from scratch with a “Data Change > Adds only” trigger on the Sales table, a “Create a PDF” task, and a fresh Google Docs template. The template included the SELECT expression, and the PDF was saved to InvoicePDFLink, but still no LineItemID values.
  4. Manual Sync and Delay: Manually synced the app and added a condition (COUNT(SELECT(SaleItems[LineItemID], [InvoiceNumber] = [_THISROW].[InvoiceNumber])) > 0) to ensure data was available, but the PDF remained blank.
  5. Template Recheck: Recreated the template section manually, reuploaded it, and cleared browser cache, yet the issue persisted.
  6. Alternative Columns: Tested with <<[Quantity]>> instead of <<[LineItemID]>>, but that was blank too.
  7. Logs and Permissions: Checked the bot execution logs (no clear errors) and verified table permissions, but found no issues.

Help Needed

I’d love your guidance on:

  • Why the Related SaleItems loop and SELECT expression aren’t pulling data into the PDF, even though it’s visible in the preview.
  • Any potential misconfigurations in my bot, template, or table relationships that I might have missed.
  • Alternative approaches to display related SaleItems data in the PDF (e.g., different expressions or workarounds).
  • Suggestions for debugging this further, especially if it’s an AppSheet-specific issue.

Additional Information

  • AppSheet Version: Latest (as of April 21, 2025).
  • Data Source: Google Sheets.
  • Observation: The bot runs successfully, and the PDF generates with other fields (e.g., InvoiceNumber), but the Items section is empty.

Thank you in advance for any insights or solutions you can offer. I’m happy to provide more details or screenshots if needed!

Best,
atypicaldoc

2

First, we need to know how your Bot has been implemented and is triggered. There are a few ways you could have kicked off the PDF Generation step in the bot and depending on which way you have done it will guide what template expressions need to be used.

I’ll assume the most common situation…

Generally, an order is divided into an Order parent table and then an Order Details child table. The desire is to enter the Order and its details, tape Save on the Order and then generate the Invoice.

However, when AppSheet ADDS new parent/child entries, the parent row is always added first. So, if you have a Bot triggering on the Order being added, it will get triggered BEFORE any of the children are physically added to the table.

There is a simple efficient way to trigger the bot only AFTER all children have been added. Include a “Generate” flag column in your Order table. Create a Grouped action to set this flag column to TRUE and then back to FALSE and attach that action to the Form Saved behavior of the Order Form View. Adjust the Bot trigger to run when this flag is set.

Now what happens is the Order parent row is saved, then its children rows are saved and then any attached actions are run. When the flag is set, all rows would have been saved and available for your Bot to use.

NOTE: the action to reset the Generate flag to FALSE is best practice. You will likely find later needs to EDIT the Orders and re-generate the invoice. Having this flag set correctly will become more important then.

I hope this helps!!!