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

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?

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.

1 Like

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

Hi,

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

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

don’t forget to add Old value

image

and then use substitute trick to count stats

2 Likes

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!

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.

1 Like

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.

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.

1 Like

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

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

1 Like

Hm, not sure if I fully understand the question. You can see on this screenshot, the Counter is set to 0 and the Formula is returning a 1. ( Counter+1 )

Try doing the Trigger Test again and post a screenshot of the result please

1 Like

I just realised what the mistake was. I based my Trigger Condition on Status from the “Car Information” table, instead of the Lookup field in the “Car Dealership Statistics” table. Can’t believe I haven’t noticed for so long.

Thank you Mohamed!
Also thank you Scott and Alexey!

1 Like

I thought it might be something like that :slight_smile:

1 Like

Yeah, you suspected immediately. Spot on!

1 Like

That was going to be my next suggestion. Lol.

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.