RECORD_ID() Help

[Mark_Baker]

Hi Guys,

I am creating a base with a column which uses the Record ID formula but having some trouble.

I want to create a unique ID for each new record with a string at the front from which comes from another column. For example, when a new record is created it draws the string BB from company name column and then adds the unique id so would end up looking like BBrecnzjghg09818 or something. Is this possible to do?

If someone also books an appointment in the future I want to be able to search the base and identify that record (individual) and so that I do not end up with a whole bunch of duplicates for an individual.

Not sure if this makes sense but thought I would reach out!

Thanks,
Mark

[Jeremy_Oglesby]

Sure, you can do this with a formula field (or a couple).

First, you’ll want a formula field that creates the “Company” part of your ID string. Do this with if statements:

IF(
   {Company} = "Airtable",
   "AT",
   IF(
      {Company} = "Google",
      "GL",
      IF(
         ... etc
      )
   )
)

Then, in your ID field, you’ll just combine that with a call to RECORD_ID() via concatenation like so:

IF(
   {Company Initials},
   {Company Initials}
)
&
`RECORD_ID()`

[Mark_Baker]

Hmmmmmmm I tried the first formula and obviously I am doing something wrong here?

Really appreciate your help Jeremy!

[Jeremy_Oglesby]

Hard to tell because I’m looking at your screenshot on my phone… but it looks to me like you might have an erroneous comma at the very end before the three closing parentheses.

If there are no further conditions to be processed in an IF() statement, then you don’t need additional commas, and the system is very strict about the syntax of that.

[Elias_Gomez_Sai]

Maybe the SWITCH() function would be cleaner? :thinking:

[Jeremy_Oglesby]

Good point… SWITCH() would be a lot cleaner.

[Mark_Baker]

Switch function works perfectly and is a lot cleaner! However, when I combine the Company ID and Unique ID with the below

IF({Company ID}, Record_ID())

It just shows the unique id and not the Company ID at the front of the string…I tried your formula Jeremy for this but it didnt want to work. Any thoughts?

[Mark_Baker]

I understand why it is just showing the Record_ID but how do I combine them to show both?

[Jeremy_Oglesby]

IF(
   {Company ID},
   {Company ID}
)
&
RECORD_ID()

[Mark_Baker]

Found the answer!!

CONCATENATE({Company ID},{Unique ID})