Update counter when action is triggered

[divyendoshi2]

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.

[Kamille_Parks · Accepted solution]

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.

[divyendoshi2]

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