Hi there! I am trying to build a Purchase Order Approval App, where in a staff creates a PO request (which includes different line items, supplied by different suppliers). Now, when I approve this order, I want to generate a separate PDF for each supplier that was mentioned in the PO…So far I’ve been able to generate one single PDF per Purchase Order, but can’t figure out how to do it supplier wise,…Any ideas?
Solved! Go to Solution.
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.
Welcome to the AppSheet community!
If you could let us know more about your data schema, for example how the suppliers in each PO are listed, the community could help you better. Are those records of each supplier coming from a different table , meaning the table other than where the PO is composed?
Right, so I have
I have a similar App where we the user can pick up to 5 suppliers from a list of about 30. How it works is they create a pruchase order. Then they click an action button to place the order. That action is an “Excecute mutliple actions…” type that simply writes “Supplier 1 Order”, “Supplier 2 Order” etc into a column. I have a Bot that triggers on
OR(
[Column Name]="Supplier 1 Order",
[Column Name]="Supplier 2 Order",
...
Hope this helps
Thanks but in my case the Supplier Name & No. of suppliers per order is dynamic
Yeah so it mine… 5 is just the maximum number of suppliers
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.
Hey! Thanks a lot for such a detailed solution! I had the first idea in bits, but you’ve filled in the blanks for me…Just that I’m not able to come up with a way so as to direct the user from Master PO directly to PO Details (bypass Supplier PO and then use a bot to create those entries)…could you guide me?
@WillowMobileSystems
First, make sure you have the Parent/Child relationship established. Do this:
Next do this:
Next:
Create a set of actions that:
Attach the top action to the Master PO_Form - Form Saved Behavior so it executes.
That is the basic gist. There are some details you will need to figure out for your use case along the way. If you get stuck on a particular part of the implementation, don’t hesitate to ask.
Okay , thank you for the details. Please add a column called say “StartReport” of number type in the LineItems table.
You could then run a reference action on the “LineItem” table from the Orders table to start creating PDFs. This reference action will change the “StartReport” column of each related child LineItems record. Please base your BOT based on change in “StartReport” column. Since the reference action will change "“StartReport” column in each of the related child records, it will start a bot for each of those records.
Please take a look at the sample app below for the reference actions concept.
On starting a BOT on pressing a button , please take a look at the below article’s section " Sending Email from an Action Button". In your case the button is on the parent table Orders ( the inistiatng reference action) and the referenced action that changes the number column is in child table, LineItems.
Supplier PO Table
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |