Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Update counter when action is triggered

Topic Labels: Automations
Solved
Jump to Solution
786 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Screen Shot 2021-07-13 at 12.17.11
Screen Shot 2021-07-13 at 12.17.24

Hi,
I’m a new user here and trying to create a basic order creation and tracking system. I have a field called Clients and another called client orders as attached above. Basically every time a user enters client name in the order tracking form, I need to generate a new order id in the format of. CUSTOMERNAME/1 and so the next time his name is added on the client order list it generates CUSTOMERNAME/2.

I created a field called Latest order Number in the Clients table to act as a counter. I was trying to figure out how to increment the counter corresponding to that client when that client name is selected in the Client Order page. I tried the automate feature on trigger, but it wont allow me to update a record in another table or apply any math formula on it. Is this possible to through a script? Appreciate any help on how to do this.

Thanks.

1 Solution

Accepted Solutions

Set up your tables to have fields like these:

[Clients] table:

  • {Max Order #}: a rollup field pointing to the [Client orders] table with the MAX(values) aggregation formula to find the last order number associated with that client.
  • {Next Order #}: a formula field that just adds 1 like this {Max Order #} + 1

[Client orders] table:

  • {Client Order #}: a number field
  • {Client Next Order #}: a lookup field pointing at the [Clients] table’s {Next Order #} field.

Then set up your Automation to trigger whenever the {Client Order #} is empty. Add an Update record action to copy the trigger record’s {Client Next Order #} into the {Client Order #} field.

See Solution in Thread

2 Replies 2

Set up your tables to have fields like these:

[Clients] table:

  • {Max Order #}: a rollup field pointing to the [Client orders] table with the MAX(values) aggregation formula to find the last order number associated with that client.
  • {Next Order #}: a formula field that just adds 1 like this {Max Order #} + 1

[Client orders] table:

  • {Client Order #}: a number field
  • {Client Next Order #}: a lookup field pointing at the [Clients] table’s {Next Order #} field.

Then set up your Automation to trigger whenever the {Client Order #} is empty. Add an Update record action to copy the trigger record’s {Client Next Order #} into the {Client Order #} field.

Took me some time to get the automation part correct, but yes this works perfectly! Thank you!