PDF generation - Header, body - list, footer

[Witan]

Hi, I'm still struggling with one thing. I want to make sure if it is possible to achieve this using AppSheet and what would be the best approach.

I use AppSheet to track inspections (Table 1). Each inspection can have up to 150 defects (defects are stored as individual records in Table 2, where one row = one defect). Each defect has around 10 characteristics (each characteristic = one column in Table 2, including an image).

I need to generate an inspection report that includes a header with some static introductory information, a body where defects and their characteristics are listed (e.g., detection date, defect description, defect image, responsible person, etc.), and a footer with some static closing information.

The issue is that I want to format the defects in well-structured tables, where the columns of the table do not directly correspond to the columns in the database table. For instance, if the database table has 10 columns, the table in the report might have only 4 columns but with 3 rows.

I want to define my own table structure with placeholders and have AppSheet generate it for all matching records (e.g., for a specific time period).

I am considering using Google Apps Script, but I am worried that this might not be an elegant solution and could be slow. The idea would be to create a template for the repeating table and have the script copy and populate it with data. With 100 or 200 defects, this might be quite cumbersome.

I hope my objective is clear.

Thank you for guiding me in the right direction.

[TeeSee1 · Accepted solution]

https://support.google.com/appsheet/answer/11539957?sjid=6251626693971718936-AP#create-template

You can do that by creating a Google Doc template (can be other types but gDoc is probably easy and good enough).

Something like this. This is a typical parent - child tables layout with the child table broken into tow rows (4 with header rows included). You can actually dynamically choose which columns to display by using IF expressions. 

Give it a try and see what you get meets your requirements.

[MCC_Travis]

With so many potential defects, you may also want to look into pagination. This template should give you evertyhing you need to accomplish your goal. You should be able to manipulate a single column into multiple columns using a variant of this formula in each column of your template:

<<Start:
TOP(ORDERBY(SELECT(table[id], CONDITIONS,[ORDERCOLUMN]), 20*([num]+1))
-
TOP(ORDERBY(SELECT(table[id], CONDITIONS,[ORDERCOLUMN]), 20*[num])
>>

Then manipulating these portions to get the right data in each column:

20*([num]+1))
20*[num]) 

The pagination probably won't work properly with the one-to-many columns in play and may require some changes, but I still feel this is a good place to start.