The main problem, I gather, you are seeking to solve is to generate PO PDF’s - one per Supplier.
As @Suvrutt_Gurjar points out, you will need some identifier somewhere to identify if the PDF’s have been generated or not. Set when PDF ready to go, reset once sent.
As @1minManager highlights, You also will need to have a plan to contend with modifications/changes. These will likely be Supplier specific so that you only regenerate the PDF for the Supplier whose order was changed. So you’ll need some way of separating the PO by Supplier.
I see two high level ways to deal with these problems from a data perspective
-
Have 3 levels “Master PO table” - parent of “Supplier PO Table” - parent of “PO Details Table”. NOTE: Supplier PO Table is different than the Suppliers table. In this scenario you track generation of the PDF in the “Supplier PO Table”. The Bot is then easy as it runs against this “Supplier PO Table” table only - for adds or changes. Any changes made to the “PO Details” would need to set proper flagging in the “Supplier PO Table” but is easy with the proper Parent/Child relationships.
While the PDF generation is simplified, there will likely need to be some work to keep entry simple for the users. For example, you may want to go straight to the PO Details from the Master PO and just enter the Supplier on each row - probably like you are doing now. This would then require some behind the scenes action to automatically create the “Supplier PO” rows when saving the order entries. Not complicated.
-
Use your current design - an Orders Table - parent of “Lineitems” table. But you need to “know” which suppliers have been added to the PO for PDF generation. I would recommend doing this with a Virtual Column that lists all the Suppliers found in the child “Lineitems” table.
The Bot becomes a little more complicated. You would need to run it against the “Supplier” table selecting those Suppliers found in the PO Suppliers list (and for only the PO’s flagged to run). This gives you a PDF generated only for each supplier that is part of the PO. Then in the PDF Template, you would need to insert a START/END block that selects the “Lineitems” for just that Supplier. When it comes to changed “Lineitems” rows, you will need to design someway to generate those PDF’s without regenerating for any unchanged Suppliers. There are ways to do it - e.g. a Changed Suppliers Virtual Column. But all of these things makes the Bot creation and implementation a bit more complicated as well as changes to data structure to drive the Bot.
This is a very high-level description of the two approaches. Please ask questions if anything needs more clarity.
My advice: Use the first option, it has the most power and flexibility while using AppSheet features to your advantage to keep things simple.
LinkedIn
Twitter
