Create a PDF with values ​​from many tables

[Witan]

I am facing another issue with generating PDF reports.

I have a table for inspections. In this table, I record basic details such as:

• Employer (where the inspection was conducted)
• Employee (who conducted the inspection)
• Location (where the inspection took place)
• Date and time of the inspection
• And some additional information.

I want to generate a PDF report for each inspection.

However, I am having trouble with this. I believe I have everything set up correctly, but it is not working as expected.

The PDF needs to include information from various tables, totaling about 84 fields. Therefore, I decided to create a MySQL view that merges these tables and outputs all the necessary information for the PDF on a single row (also serving as a record of the inspection). I successfully connected this view as a data source to the app.

I then created a bot. I set the event to trigger on Adds or Updates to the inspection table. But I am having significant issues with the inputs for the bot. I thought I could define all 84 inputs for the PDF from the view table through Advanced -> Inputs. Each inspection in the table has a unique ID. I managed to do this. Everything seemed fine. I placed the inputs as placeholders in the Google Docs template. Everything looked good. I generated the PDF. It worked fine for the first one, fine for the second one, but the third one had errors. The names started getting mixed up. Essentially, incorrect names began appearing illogically.

The database is 100% correct. The view table is 100% correct. The PDF output is incorrect. It does not make any sense.

I tried numerous variations, but the same issue persists.

Eventually, I tried triggering the event with a condition that the "Trigger" column is set to true. I created an action that sets this value to true in the column and enters the inspection ID in another column named "InspectionToPrint". I then look up the inputs (placeholders) like this: LOOKUP([CheckID], "View_Records", "CheckID", "EmployerIDNumber").

That is, I take the CheckID entered in the column, and use it to find the corresponding row in the View_Records table (the MySQL view), and retrieve the value from the specified column in that row.

Yet, it still produces incorrect results. Despite the database being correct, the PDF output is nonsense.

I even thought MySQL might have some delay in creating the view, causing these mixed-up values. So, I tried creating a trigger in MySQL to copy data from the view to a regular table, added this table to AppSheet, and worked directly with it, but the result was still the same.

How can I define inputs correctly so that when creating or editing a record, they take the ID of the record and use it to find the correct value in another table?

I still feel that the ID is being returned incorrectly.

Or how else can I handle this "complicated" PDF generation?

Personally, I feel that defining inputs in the Advanced -> Inputs section simply does not work well and is prone to errors. But I hope I am wrong.

I am attaching some images to make it clearer what I am talking about. Note: I have checked the data in the database 100 times. Everything is correct, each ID is unique, and everything is properly linked by ID!

Thank you.