Inspection Report entries

[rajivvaishnav]

Hello Friends,

I am trying to create an app to capture inspection data for a manufacturing unit, and here is the final result I am planning to generate via PDF :

Everything works fine, I have created a table structure like this :

https://docs.google.com/spreadsheets/d/1tTKJv75PMpHgc4pqH5Bi7YFQ2YYitBUOqjftFULaDxY/edit?usp=sharing 

Entries are working fine but I am totally lost as how to create the pdf report from my data, as you see in the above attached image, We take inspection readings of various parameters of the product every hour like 9am, 10am, 11 am etc, but In my database I have time as rows(records) but I want all readings at every hour in columnar format, how can I achive this.

Can someone please help.

Regards

Rajiv

[WillowMobileSys]

First the data tables you have are formatted correctly for data input.  Placing readings in a row format will allow for filtering and aggregation of the reading data in many different ways as needed.

Reporting is different animal and sometimes the report format is not served well by the data input format.  This is one of those times.

There are 4 options I would consider to solve this issue:

1)  First, consider arranging the PDF format to be more easily served by the data input format.  For example, instead of the reading time slots being in columnar form, have, under each parameter row, an inline table with the time slots shown in rows.  An advantage might be that you can show other reading details as well - if there are any.

2)  Collect each Reading into a LIST column (probably a virtual column) that can be easily retrieved in the PDF template.  This Virtual Column would have an expression that grabs the Reading Values for each Parameter in the Time Slot order.   Then in the PDF Template, you can assume the values are in the expected order and simply use the INDEX() function to grab each one - e.g.  INDEX([Reading Values], 1) for the 9am cell,  INDEX([Reading Values], 2) for the 10 am cell, INDEX([Reading Values], 3) for the 11 am cell, and so on.

3)  Construct a second dedicated report table in the format needed for the PDF, or at least close to it.  This can be done with a process just before generating the PDF.  An app script would probably be best.  Personally, I would add it as a background table and each time a reading is inserted, I would also add it to the report table.  A couple of advantages here:
     a)  The time to create the report table is spread out, instead of done in a single process that might be slow.
     b)  It would allow to see, in an AppSheet view, a  "near real-time" report and possibly eliminate generating PDF's altogether. 

If PDF's are indeed required, this dedicated table would make the PDF generation quick and easy.

4)  As a last resort, you could insert into EACH PDF Template Reading cell an expression to grab the specific Reading value for the parameter.  Sp, for example, in the 9 am template cell - a SELECT() expression is inserted that grabs the 9 am Reading for the Parameter plus other report criteria,  similar expression into the 10 am template cell,  similar expression into the 11 am template cell, and so on until all time slot cells have their own expression to grab the specific reading value.  I say last repost because, while this approach requires NO CHANGES int he app, it would be slow in generating the report.  It's possible automation step could hit the processing limit and fail depending on the number of parameters being reports on.

I hope this helps!