Skip to main content

Hi Guys,

I have a problem that I need some help with. I have looked at a similar problem that was solved on the forum but can’t think of how to make it work for my use case. Hopefully there are some airtable ninjas that will come to my rescue…

My company provides services to clients on their sites. We have about 4 services that we offer that the client can pick and choose from. Ever since the company started we have tallied up the number of times we have performed a service, for example for Client 1 it might be Service1, Service3 and Service 4; and for Client 2 it might be Service1 and Service2 only. Which services we provide are displayed in a multiselect field for that job.

I then have other columns/fields to the right e.g. Service1, Service2, Service3 etc that keep a tally of the number of times we have undertaken that service. Currently we do this manually but I would love to be able to select the serviced provided in the multiselect field and have the autonumber appear in the relevant column.


Can anybody see an efficienct way to do this?

Thanks,
Zac

Do you need to know Job 4 is the 3rd time “Service 1” was completed, or that as of today “Service 1” has been completed 4 times? The following ideas accomplish the latter.

Idea 1: make a separate table for Services and replace your {Services} field in the [Jobs] table with a Link to Another Record field pointing at your new table. Then all you have to do is add a Count field to the [Services] table to accomplish what you’re after.

Idea 2: replace your manual number fields with formula fields that follow this convention: {Service 1} has the formula IF(FIND('Service 1',{Service}),1,0). This will add a 1 everytime Service 1 is performed. You can use the summary bar at the bottom of the page to sum these to get the total times that service was performed.

I would 100% recommend making a new base for these services as @Kamille_Parks has recommended.

Do you need to know Job 4 is the 3rd time “Service 1” was completed, or that as of today “Service 1” has been completed 4 times? The following ideas accomplish the latter.

Idea 1: make a separate table for Services and replace your {Services} field in the [Jobs] table with a Link to Another Record field pointing at your new table. Then all you have to do is add a Count field to the [Services] table to accomplish what you’re after.

Idea 2: replace your manual number fields with formula fields that follow this convention: {Service 1} has the formula IF(FIND('Service 1',{Service}),1,0). This will add a 1 everytime Service 1 is performed. You can use the summary bar at the bottom of the page to sum these to get the total times that service was performed.


Hi Kamille - thanks for your reply.

Prior to asking on the forum, I had tried to implement Idea 2 as I thought this would work as well. It seems that when a cell has multiple dropdown options selected it cant ‘see’ past the first option. I tried converting this back to text with a formula into another field but still not working. Any ideas on how to get this working?

In answer to your first question it would be nice to see which site had the n-th service, but not imperative…

Thanks for your help.

Terms & ConditionsAccessibility statement