Change Primary Field

[Mark_StDenis]

I currently have a membership database in ACCESS and the membership number is the primary key. I want to get this into Airtable and keep the current membership number as the primary field so that it auto increments when a new record is added. I have read the article " How can I change the primary field in my table?", but it does not work.
Or is there some other way of ding this?
Thanks for your help.
Mark

[JonathanBowen]

Hi @Mark_StDenis - there is a way to achieve, this, but you’ll need a few additional fields (which you can hide). You can’t start an Airtable auto-incrementing field at an arbitrary point, so you’ll need to workaround the constraint. Here’s how I would do it:

1. Add all of your existing members to Airtable with their member number in “old member number”
2. Create a new number field with a default of X (I will come back to this)
3. Create a autonumber field (which will start from 1)

Now in your primary field have a formula:

IF({Old member number}, {Old member number}, {New member number} + Autonumber)

If there’s an old member number this will display. If you get a new member, then the member number will be the “new member number” default plus the autonumber.

I my example, I set the default to be 102, i.e. the max of the existing number. But in fact, I hadn’t thought this through - for continuity it should be the max old number minus the number of old members plus 1 (I think). Anyway you can play around with the default.

As you add more members in Airtable you’ll get this:

[kuovonne]

Welcome to the Airtable community!

JonathanBowen described one excellent method for having an auto-incrementing number in Airtable.

Another option that uses three fields instead of four is to create a bunch of blank records until the Autonumber increments to past the largest of the old member numbers. Once autonumber is large enough, you can delete the blank records and use the following formula.

IF({Old member number}, {Old member number}, {autonumber})

---

I do want to caution you that Airtable handles linked records very differently from Access. Access connects records based on a primary/foreign key system. Airtable does not. All linked records in Airtable must be explicitly linked (either manually or through code).

While you can use Access’s automatically generated primary keys to help you rebuild record links in Airtable, they will not be useful for maintaining links.

After converting a base from Access to Airtable and recreating links, I usually discard primary keys that were generated by Access, unless they are used outside of the database itself.

Airtable also does not enforce the requirement for a unique primary key, unlike Access.

[Mark_StDenis · Accepted solution]

@JonathanBowen Thanks for the reply. This solution will work for us. It will be playing around to get the sequencing correct. If some number are missing in between that will be fine. This is better than trying to create a whole new membership number scheme.