Hello everyone,
Me and my team are working on a database for my business, to be frank we're new to Airtable, and there's something we can't quite figure out. There's this one table that is for recapping all of our clients' consultation sessions. After each client consultation sessions, that session will get added to this table as a separate entry, and be given a specific Consultation ID. The goal is to make something like this:
Consultation ID: EVA/COU/2024.06/000004-001
The things that matter here are the field "Unique ID" which is 000004, and the three digit after the dash, which in the example is 001, which should signify the amount of sessions they have with us, 001 here meaning it was the client's first ever session.
The field "Unique ID" is tied to the field "Participants" which is linked to a different table filled with details of our clients. Each Participant is assigned a Unique ID.
If they have following sessions with us, then we would like the three digits after the dash be auto-numbered and become 002, 003, and so on.
The problem we encounter here is that the final three digits in the Consultation ID doesn't increase when we add another entry for the same Participant, it stays at 001.
We tried many different formulas but they don't work. At this moment, the mitigation is to number them manually by adding an extra field called "Session #". Here is the current formula for the Consultation ID:
CONCATENATE(
"EVA/COU/",
{Formatted Date}, "/",
{Unique ID}, "-",
RIGHT("000" & (({Session #})),3)
)
This is the last formula they tried to put in but resulted in an ERROR:
CONCATENATE(
"EVA/COU/",
{Formatted Date}, "/",
{Unique ID}, "-",
RIGHT("000" & (LEN({Participants})),3)
)
Here is how the table looks like at the moment:
How should the formula for the Consultation ID look like to make it do what we imagined?
Any help would be appreciated. If more info is needed, don't hesitate to ask. Many thanks in advance.
Hm you're going to need an automation to help with this I'm afraid
In the "Participants" table, create a "Count" field to output the total number of sessions for that user:
Then create a lookup field in "Consultations" to display that value:
Then create an automation that will trigger when the lookup field is not empty, and the "Session #" field is empty:
It's action will be to paste the value from the lookup field into the Session # field:
And when it triggers, it'll look like this:
@TheTimeSavingCo has a great solution for automating the addition of a count field. And, he's right in that Airtable doesn't have a great solution for this. Yes, we can count the number of records, but Airtable doesn't have a native easy solution for "knowing" which record in the count you're looking at.
You'll still need to reformat this number using something like RIGHT("000" & INT({Session Number}), 3). You can then add a formula to the Consultation Name field:
For users of a Free Airtable account, there is a method that won't involve automation. It also won't work as a formula in the Consultation record's Name field.
In your Consultations table, you will create a UID formula field with a formula of RECORD_ID()
Then, in the Participants table, create a new Rollup field. You'll point it at the UID field in the consultations table, and use an ARRAYJOIN(Values, ';') formula.
Finally, you'll add another new field in Consultations. Again, choose a Rollup. This time, with a formula of:
CONCATENATE("EVA/COU/",LEFT(SUBSTITUTE(Date,'-','.'),7),'/',{Session ID},'/',RIGHT("000" & (FIND({UID} & ';',values&';') - FIND({UID},SUBSTITUTE(values&'',';','')) + 1),3))
The example base.
