Forum Discussion

NirajShah's avatar
NirajShah
Qrew Cadet
7 years ago

Automation to add one record to another table when something is triggered in another table

I have three tables: Purchase Orders, Cost Sheets, and Entries. The relationship is such that a Purchase Order can have multiple entries and Cost Sheets can have multiple entries. I have an "Approval" button on the Cost Sheets table, when triggered, automatically approves all entries associated with that cost sheet.
On the entries table, users are required to select a Vendor for each entry. Each entry is associated with a Cost Sheet, such that one cost sheet may have anywhere from 1 to infinite number of entries that may also belong to 1 to infinite number of vendors. 
The challenge I am having is how to automatically generate a purchase order for each __unique __vendor when a cost sheet (and entries) are approved. Currently, the automation fails since the related vendor field is required to be unique, and once I remove that requirement, the automation runs but creates multiple purchase orders for the same vendor.
APIs and custom code
Forms
Formulas and functions
  • AnnSwelgin2's avatar
    AnnSwelgin2
    Qrew Member
    7 years ago
    do you have your automation trigger set to vendor or record ID? have you tried setting it to the opposite of what you have now? Automations are still new to me but I am curious if that will solve the issue.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    A general approach could be to have a summary field to calculate the Minimum Record ID# of the Entry (or maybe you call them Cost Sheets) for the vendor which does not yet have a Purchase Order created.

    Then you would have an Automation fire to Create a Purchase Order for that Vendor.  (I'm not quite sure yet how you plan to attach the entries to the Purchase Order....)

    so that will have solved for the first Purchase Order.

    The run the Automation again and it should find the next Entry which needs a Purchase order for the next Vendor. 

    So provided that in fact you do not have an infinite number of Unique Vendors but a reasonable maximum of say 10 per Cost Sheet, then just run the steps 10 times in the same automation.  You get 50 steps per Automation.  You may need to create a dummy table to summarize up the highest Record ID# of the Purchase orders so the Automation or saved table to table import that the Automation runs, knows where to attach the Entries.