Date order not quite right in a linked record

[Sebastian_Page]

I am a historian, constructing a historical database (1940s-60s). When I click on my linked record for a person or institution, some of which will have to link to 1000+ records, I notice that the records are almost arranged in date order - but not quite.

As I scroll down, I will notice up to 20 or 30 records running from earlier to later, as I would like, then a record from a decade earlier will appear next, followed by another run of (successful) earlier-later sequencing, though often for yet another subset of years.

Naturally, I would like the entire contents of a linked record to run from earlier to later.

I cannot see what other sort criterion the linked record might be using. It certainly isn’t the random 6-digit number I’ve created for each record ID. What is odd is how the linked record is almost in date order.

[kuovonne · Accepted solution]

If the only reason you have an autonumber is to have unique ids, and you do not use those numbers elsewhere, you can change the primary field to something else. I suggest you change the primary field to one of the formulas the I posted (or something similar) so that you can take advantage of the Batch Update block to sort linked records.

Airtable does not require unique primary fields, although it is good practice. Since the date is not unique, you can use a formula field for the primary field and concatenate the date with whichever fields will uniquely identify the record. The only fields I saw in your second screen capture were the date and the comments, so that is what I showed in my formula. You know your data best, so you can determine which fields to include in the formula. If you need help with such a formula, please post a screen capture showing all the relevant fields.

[Sebastian_Page]

Ah, that changes things! Since I can see some records that share a date, datestr, date comments - or lack thereof - and format, am I to surmise to Airtable just goes through as many columns for each record as it needs to, to establish uniqueness? (By that yardstick, all of my records would indeed be unique.)

Thanks for your help!

[kuovonne]

Airtable uses an internal record_id to uniquely identify each record. Thus, even if two records had identical values for all fields, Airtable could tell them apart. You can see this record id using the formula function RECORD_ID().

[Sebastian_Page]

That’s very reassuring to know!

[Scott_Brasted]

In case anyone else looks at this answer in the future. I had the same problem and. I found a workaround that does not require a pro subscription. 

Created three new formula fields in the table I needed to sort. One for Year, One for Months and One for Days. Then sort on those fields and it will overcome the limitation of sorting on linked date fields. In my case:

YEAR({meeting date})

DATETIME_FORMAT({Meeting Date}, 'MM') 

DAY({meeting date})

I have done this several times in several tables and it always has worked for me. I hope this helps.

Best, Scott