Counting how many times a Single Select Field had hit a specific state?

[mocianto]

Hello guys! I am new to Airtable, and have been absolutely amazed by it’s possibilities. However, there is one specific thing where I got completely stuck for several hours and no YouTube videos or Forum topics helped me get through.

I am practicing on a table for a Car Dealership. Each record has 5 columns with Car Details, and then a Single Select Field where we can choose from “Available”, “Reserved” and “Sold”.

Then I have another table called “Car Dealership Statistics”, and here I would like to count how many times my cars went into “Reserved” and how many times they went into “Sold”. These numbers can be different because people can abandon their Reservations and the car becomes Available again.

I wanted to tackle this with a Counter column, a Formula column ( Counter+1 ) and an Automation that increases Counter by Formula when the field = Reserved, and then same when field = Sold.

However, I keep getting the message in my Automation that the Formula column can not be used to Dynamically increase Counter because it is computed. Any ideas?

[ScottWorld]

Welcome to the community, @mocianto!

Yes, what you described should work. If “counter” is a number field, then you should be able to update the counter field with the value from your formula field.

If it’s not working, please post screenshots or a video.

[mocianto]

Thank you for the warm welcome!
I am probably making some small stupid mistake, but here are the screenshots:

Car Information

Car Dealership Statistics

Automation Trigger

Automation Action

[Alexey_Gusev · Accepted solution]

Hi,

You should update Counter field and put there value of a Formula field

but I would write all events ‘Car X changed to Y’ into log (longtext field)

don’t forget to add Old value

and then use substitute trick to count stats

[mocianto]

Thank you guys. Alexey, that looks like a great solution. But I have a problem using the Formula field to increase my Counter. Keep getting a message that the Formula Field “can not be used because it is computed”. I am probably doing something wrong, but not exactly sure what.

I already posted a series of screenshots showing my Tables and Automations, but the post got marked as spam. Let me try with a LINK - hopefully this shows my mistake, thank you for taking the time to help me learn!

[ScottWorld]

You’re not in the right area. You’re trying to add a field. You’ve already specified the field above. You need to click on the blue plus sign. And also be sure to remove that Record ID tag.

[mocianto]

When I click the blue plus sign, there is no option to add the Formula at all.

Here is a video example what it looks like.

[ScottWorld]

If you recently added the formula field, you will need to re-test your trigger to load the new fields into the subsequent actions below.

[mocianto]

Re-tested it, even tried re-creating the automation from scratch just now, still not getting anything. I am completely lost here. :neutral_face:

[Mohamed_Swella1]

Is the formula for the test field returning any number or is it empty?