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.

[Marc_Dillon]

You are very much on the wrong path here.

Back up. All the way before the whole mysql view, you don't need that. You just need to use expressions in your PDF template to lookup and display all values from the triggering record and related records. Presumably they are all related tables with proper references and or [Related...] virtual columns? If so, it's just a case of using some START expressions and/or dereferences.

That could be the end of my reply, but I guess I can also shed some light on some of your other concerns and mixups:

That's not what Advanced-Inputs is supposed to be used for.

If you're triggering on Adds, then yah Appsheet would not have the record from the mysql view available to be used. It is triggering the bot immediately on the just-added record, it's not going to wait and perform another sync that would be needed to pull in the updated view records. A trigger to copy the data to another table is certainly not any kind of fix to this issue, that just adds on more processing that would need to be waited for.

Typically on questions such as these, you really should include the template used in the provided information.

[Witan]

In the end, I need (want) a record of all PDF-printable data in the database in the form it was at the time of printing. I.e. anyway, I want view -> move to the real table (Record).
In the end, I solved it by running the bot directly for the Record table, where of course it is no longer necessary to define Advanced -> Inputs, but it is enough to give the name of the column as a placeholder.
Anyway, the PDF is generated in about 10 to 15 seconds. It's quite long. There is no problem in the database, there it is a matter of milliseconds.

I guess I don't understand the advanced inputs?
What is it used for?

So how and where can I define a placeholder if I want data from a different table to be inserted into it than the one I'm running the bot for (CREATE PDF)?

And I still have to generate the PDF and work with the images. To my great surprise, I found out in another discussion that it is practically impossible to change the size of the images in the PDF 😞

Anyway, thanks for the reply.

[Marc_Dillon]

Well if your solution works for you, then feel free to stick with that, but it really isn't necessary at all, you can look up and display any data from the entire app from a template.

---

@Witan wrote:

Anyway, the PDF is generated in about 10 to 15 seconds. It's quite long. There is no problem in the database, there it is a matter of milliseconds.

---

If this is in response to my statements about the sql view record not being available on adds, then you missed the point. It's not about processing time, it's about order of operations. The template would be evaluating before the app would have the updated sql view available to be used.

Advanced inputs are for passing data between 2 processes.

You don't define "placeholders" anywhere. You just use the correct expressions in the template.

It most certainly is not "practically impossible" to change the size of images in a PDF. You would need to utilize tables. Put the images in the table cells and adjust the dimensions and padding and margins and whatnot. Don't be afraid to nest table. It's similar to how one would build an HTML website. It can be tough, but not impossible.