Unique customer ID Code

[Evos_World]

Hi

Using my current accounting software, every time I add a customer it gives me a unique code which consists of the first 4 letters of the company name followed by a 2 digit code i.e. Boggs, Joe would be BLOG01 and Bloggs, Stephen would be BLOG02 as BLOG01 already exists. Is this possible with Airtable?

To add to this, if a Company name doesn’t exist, I want it to create the code using the surname.

So far the following code will take the information from the Company field, remove any unwanted characters or spaces, change the text to uppercase and add a 2 digit number which is taken from the ID field (Auto number).

UPPER(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Company}," “,”"),",",""),".",""),"/",""),"?",""),";",""),":",""),"’",""),"-",""),"!",""),"@",""),"&",""),"_",""),"+",""),"+",""),4)) & RIGHT(‘0000’ & Autonumber,4)

This next code will fill the cell with the first name unless there is content in the Company field.

IF({Company}>"", {Company}, {First Name})

What I can’t figure out is:

How do I combine Surname and First Name on the second part of the code?

How do I combine the first code to the second code so if company does exists then create the code from that otherwise create the code using the surname?

How do I then lock that cell so that if I change the company name or surname then the code will remain the same?

How do I stop it duplicating?

Thanks

[W_Vann_Hall]

At the moment, your options are limited, in that Airtable does not currently support persistent calculations (that is, a formula field that, once calculated, retains the value even if the value of variables used in the formula change — essentially the equivalent of an Excel cell with automatic recalculation toggled off), so you won’t be able to lock the ID. It also does not presently support enforced unique values for a field, so you can’t guarantee non-duplicate items.

That said, there are workarounds that may or may not prove workable, depending on your need and application. Typically, if I want to force persistence on a calculated value, I’ll mirror the formula field with a compatible data field and physically duplicate the value from the impermanent formula field to the [more] permanent data field.

If the fields are adjacent in a grid view, such duplication can be performed with a three-stroke Ctrl-C/RtArrow/Ctrl-V sequence or by selecting and dragging the fill handles one cell to the right. More importantly, a Zapier Zap, Integromat Scenario, or the equivalent can be defined to duplicate the value automatically whenever a new record is added to the base. Presumably, a more-complex Zap or Scenario could be created that could receive its inputs from Airtable, mull them over, and either enter the appropriate ID or generate a new one, as needed.

For sake of your users’ sanity, I suspect using a third-party SaaS integration service would be your best approach – either that, or come up with a different ID algorithm. That’s a reasonably sophisticated process flow your current accounting package performs, and it would be very difficult, if not impossible, to replicate it in Airtable as the platform now stands.