Generating Multiple PDFs

[Rachit_Taparia]

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?

[Suvrutt_Gurjar]

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?

[Rachit_Taparia]

Right, so I have

1. A Suppliers table with a single column: [Supplier Name]
2. An Items table with a single column: [Item Name]
3. An Orders table with key [PO ID], which links to the LineItems table
4. A LineItems table which has [LineID] as the key, and an [OrderID] which is a reference of the Orders table. So basically, every line item links back to the order, and is a PartOf the Orders table. Every LineItem has a [Supplier Name] column which is a ref of the Suppliers table, and an [Item Name] column which is a ref of the Items table.

[1minManager]

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

Simon@1minManager.com

[Rachit_Taparia]

Thanks but in my case the Supplier Name & No. of suppliers per order is dynamic

[1minManager]

Yeah so it mine… 5 is just the maximum number of suppliers

[WillowMobileSys · Accepted solution]

Rachit_Taparia:

Thanks but in my case the Supplier Name & No. of suppliers per order is dynamic

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

1. 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.

2. 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.

[Rachit_Taparia]

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

[WillowMobileSys]

Rachit_Taparia:

could you guide me?

First, make sure you have the Parent/Child relationship established. Do this:

1. in the Supplier PO table, make sure the PO-ID column is a REF to the Master PO table AND that the “Is Part Of” property is on.
2. In the PO Details table:
   • make sure the SPO-ID column is REF to the Supplier PO table
   • Make sure that the PO-ID column is a REF to the Master PO table AND that the “Is Part Of” property is on. This is probably unexpected but is what allows PO Details INline Table to show in the Master PO Form

Next do this:

3. In the Master PO_FORM, add the [Related PO Details] column. This should show the PO Details_Inline view which is likely defaulted as an Inline table.

Next:

4. Create a set of actions that:

   • Transitions from Master PO table to PO Details table using the “execute an action on a set of rows” type action
   • Add additional actions Inspects each saved PO Detail row and checks if a row is present in the Supplier PO table for the PO-ID and SPO-ID.
   • If not present, then add row.
   • If present, no action necessary-simply skip.

5. 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.

[Suvrutt_Gurjar]

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.

AppSheet

AppSheet : mobile apps from spreadsheets : Template: for the industry

This app shows how to use reference actions. Reference actions are used to modify a row in one table via an action defined on another table. In this example, we have a Parent table and a Child table. The Parent table has an action called "+1 All"...

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.

help.appsheet.com

Workflow

Send Email, SMS, or Notifications on Add, Update, or Delete.

[Rachit_Taparia]

Master PO table

[Rachit_Taparia]

Supplier PO Table

[Rachit_Taparia]

PO Details Table