Skip to main content

Using a Lookup field Type

  • March 21, 2023
  • 3 replies
  • 20 views
A
Forum|alt.badge.img+2

I'm designing my new base and I want to create unique Task ID's that are prefixed with a letter/number combo that links to the site the task is for so DE2 and HGE. The Task ID will auto-create every time I create a new record

For example: 

Create New Record > Task ID is generated (DE2_000001)

Can anyone help out? When I done this at a previous company it seems that there was a Field called Site Code that looked up to somewhere then the ID was generated u sing a formula

3 replies

TheTimeSavingCo
Forum|alt.badge.img+31

How do you decide whether to use "DE2" or "HGE"?  Could you provide a screenshot of your table with the relevant fields?

For the number part, you can achieve that with an autonumber field and a formula field I think:

REPT('0', 6-LEN(Number & "")) & Number
Adam, Airtable Consultant - thetimesaving.company
A
A
Forum|alt.badge.img+2
  • amcculloch87
  • Author
  • New Participant
  • March 24, 2023

How do you decide whether to use "DE2" or "HGE"?  Could you provide a screenshot of your table with the relevant fields?

For the number part, you can achieve that with an autonumber field and a formula field I think:

REPT('0', 6-LEN(Number & "")) & Number

Thanks for the reply. That's a good point about how to tell which one to prefix with. For now I'm happy to generate the number just prefixed with DE2. 

So you'll be able to see that in image 1 that's where a formula has been added to bring together the code (so in my case DE2), and then how it brings in the rest of the ID with the _002861 (in my case it'd be _000001)

In the second image I think this is the lookup field that allows the formula to pull in the prefix (DE2). I just don't understand where DE2 needs to be set up in order for me to pull DE2 in to a lookup field. 
Also your explanation on how to create the numbers in that format do I need to set this up in a different table?

Thanks so much for your help 

2 Attachments
TheTimeSavingCo
Forum|alt.badge.img+31

Thanks for the reply. That's a good point about how to tell which one to prefix with. For now I'm happy to generate the number just prefixed with DE2. 

So you'll be able to see that in image 1 that's where a formula has been added to bring together the code (so in my case DE2), and then how it brings in the rest of the ID with the _002861 (in my case it'd be _000001)

In the second image I think this is the lookup field that allows the formula to pull in the prefix (DE2). I just don't understand where DE2 needs to be set up in order for me to pull DE2 in to a lookup field. 
Also your explanation on how to create the numbers in that format do I need to set this up in a different table?

Thanks so much for your help 


> I just don't understand where DE2 needs to be set up in order for me to pull DE2 in to a lookup field. 

Ah, I don't think I have enough information to answer that question I'm afraid, this is more of a question of how your base is set up than anything else.  I'm assuming whatever table your "Client Code" lookup field is linked to has a field that indiciates "DE2" or something, or maybe "DE2" is already an option in your "Client Code" field

> Also your explanation on how to create the numbers in that format do I need to set this up in a different table?
You'd want to set it up in whatever table you're creating records so that the autonumber field would work properly.  This won't work if you need it to start from 1 and the table already has records in it though, for that you can find a solution here

Adam, Airtable Consultant - thetimesaving.company
Terms & ConditionsAccessibility statement