Unique referencing

[Brian_Barnes]

I am sure this is going to be the first of many questions as I build my clinic CRM/Visit and inventory manager.

The first formula I’m looking for is providing my patients with a unique identifier. The autonumber or record ID isn’t appropriate.

The unique reference/ID I was looking to contain the following:
First and Last Initial
Date of birth (ddmmyy)
Incremental 3 digit number (001

So my record ID (Patient Record Number (PRN)) would be BB031200001

Any help is greatly received

Bri

[kuovonne]

Welcome to the Airtable community!

Notice that the incremental 3 digit number is basically the same as an autonumber. Assuming that the first name, last name, date of birth, and autonumber are all fields in your base, you can use a formula to put it all together.

LEFT({first name}, 1) &
LEFT({last name}, 1) &
DATETIME_FORMAT({birthdate}, "DDMMYY") &
IF({autonumber} < 10,
  "00",
IF({autonumber} < 100,
  "0"
)) &
{autonumber}

[Bill_French]

Brian_Barnes:

So my record ID (Patient Record Number (PRN)) would be BB031200001

Does the patient data fall under HIPAA compliance?

[Brian_Barnes]

Thank you so much, a complicated formula but worked a treat.

[Brian_Barnes]

We don’t use HIPPA in the UK.

[kuovonne]

Brian_Barnes:

a complicated formula but worked a treat

You’re welcome. In the scheme of things this is a fairly straightforward formula. It just might look complex because it is spread out over a few lines.

I missed the part that said this is a CRM for a clinic. Glad that Bill caught that and that it isn’t an issue for you.

[Bill_French · Accepted solution]

Brian_Barnes:

We don’t use HIPPA in the UK.

Okay, then you must adhere to even more stringent safeguarding of patient information and would be subject to GDPR as well as the Data Protection Act (DPA), right? That said, patient health information (PHI) in Airtable is probably not recommended because it is not HIPAA, DPA, or GDPR-compliant.

If this is the case, you have only one out - you must fully anonymize the data making it impossible to identify or associate any of the information with any specific patient. And if that’s your plan, then you should probably steer clear of any use of patient initials in the ID.

It’s possible to use Airtable for medical research, but if anything is identifiable, it’s likely to create a lot of legal issues.

[Brian_Barnes]

Many thanks, all points taken on-board :blush: