Skip to main content

RECORD_ID() Help

  • October 4, 2018
  • 12 replies
  • 46 views
M

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

    12 replies

    J
    Forum|alt.badge.img+18

    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()`
      M
      • Mark_Baker
      • Author
      • Participating Frequently
      • October 4, 2018

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

      Really appreciate your help Jeremy!

      J
      Forum|alt.badge.img+18

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

      Really appreciate your help Jeremy!


      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.

      E
      Forum|alt.badge.img+17

      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()`

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

        J
        Forum|alt.badge.img+18

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


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

          M
          • Mark_Baker
          • Author
          • Participating Frequently
          • October 4, 2018

          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?

          M
          • Mark_Baker
          • Author
          • Participating Frequently
          • October 4, 2018

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

          J
          Forum|alt.badge.img+18

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


          IF(
   {Company ID},
   {Company ID}
)
&
RECORD_ID()
          M
          • Mark_Baker
          • Author
          • Participating Frequently
          • October 4, 2018

          Found the answer!!

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

            J
            Forum|alt.badge.img+18

            Found the answer!!

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


            The & is a shorthand for CONCATENATE().

            You can just use:

            {Company ID} & {Unique ID}
            M
            • Mark_Baker
            • Author
            • Participating Frequently
            • October 4, 2018

            The & is a shorthand for CONCATENATE().

            You can just use:

            {Company ID} & {Unique ID}

            Champion, thank you for your help Jeremy!

              M
              Forum|alt.badge.img+20
              • M_k
              • Inspiring
              • October 4, 2018

              Hi

              As usual, I have learned something new. I might give this a try.

              MK

                Terms & ConditionsAccessibility statement