Skip to main content
Solved

Date order not quite right in a linked record


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.

Best answer by kuovonne

Sebastian_Page wrote:

My only use for the autonumber was to create a unique ID for each record, as the obvious alternative, the date, is not unique: I might have up to 20 separate records for events that happened on the same date. As such, could I safely delete the autonumber column and set the date as primary? (Indeed, is that the purpose of the formula you’ve just shown me?)


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.

View original

14 replies

Hi @Sebastian_Page,


I believe Linked Records appears in the order they were linked in. Meaning, everytime you link a record it will appear in the end of the list.


If you want to have a better view, maybe use the Kanban or Gallery view and customize the cards as you like.


If this helps you, please mark it as Solution so others can see it.


BR,

Mo


  • Author
  • Participating Frequently
  • 8 replies
  • May 30, 2020

Thanks for helping! That certainly explains the “runs” of correct dates, in that each one represents my logging a specific manuscript collection, itself arranged in date order, before starting on the next one.


Having now looked at those different views, I just wonder whether I’ve hit the limits of AirTable’s functionality for my particular needs. Being able to view everything in date order for a specific entry in a linked-record column - e.g. for following the activities of a certain person - is just as important to me as being able to arrange the underlying records - the overarching historical events - by date.


Sebastian_Page wrote:

Thanks for helping! That certainly explains the “runs” of correct dates, in that each one represents my logging a specific manuscript collection, itself arranged in date order, before starting on the next one.


Having now looked at those different views, I just wonder whether I’ve hit the limits of AirTable’s functionality for my particular needs. Being able to view everything in date order for a specific entry in a linked-record column - e.g. for following the activities of a certain person - is just as important to me as being able to arrange the underlying records - the overarching historical events - by date.


How about you use the Group By function in the original table instead? You can then sort it by date as well.


  • Author
  • Participating Frequently
  • 8 replies
  • May 30, 2020

I’m very new to AT, so hadn’t even thought of that! It almost worked, but produced a different entry for my person of interest (i.e. the linked record) whenever he appeared alongside a different person/people in that same column. Using “group by” alerted me to “filter,” however, which seems to do the job.


Thank you for helping such an unabashed rookie!


Sebastian_Page wrote:

I’m very new to AT, so hadn’t even thought of that! It almost worked, but produced a different entry for my person of interest (i.e. the linked record) whenever he appeared alongside a different person/people in that same column. Using “group by” alerted me to “filter,” however, which seems to do the job.


Thank you for helping such an unabashed rookie!


Anytime 🙂


kuovonne
Forum|alt.badge.img+17
  • Brainy
  • 5987 replies
  • May 30, 2020

Depending on how your primary fields are setup for your linked record, if you have a Pro subscription, you can use the Batch Update block to sort the linked records in order.


To setup the primary field for this, set the primary field in the linked record to a formula that starts with the date formatted in year-month-day order. If you need help with setting this up, please post a screen capture of your table showing the primary field and the date field.


  • Author
  • Participating Frequently
  • 8 replies
  • May 30, 2020

I do have pro, and have now installed the Batch Update block, but cannot see where to go from there.


The wider screenshot shows what I’m looking at when I consult my base most of the time: these are historical events, drawn from different sources, all rearranged into chronological order. There is a “Person,” “VG,” who, like all the other Persons, can be the “Author” or the “Recipient,” and/or the “Subject” of each record. I am mostly interested in Subject when I look at his linked record, as an Author or Recipient must always be a Subject, but the reverse is not necessarily true. If the fix somehow had to collate these three columns in the linked record only (i.e. not under any of the base’s main tabs), that would be fine, even preferable.


The narrower screenshot shows the problem, and is the linked record for VG. I’m looking at the entries under “Subject,” and would like them to be in chronological order (earlier-later), like under my main tab (“Event”). Only, this is where I get stuck.


Thanks for whatever help you can offer!



kuovonne
Forum|alt.badge.img+17
  • Brainy
  • 5987 replies
  • May 30, 2020

It looks like the primary field is an Autonumber.


In order to use Batch Update as I suggested, you would need to change the primary field to a formula field. In this process, you would loose the Autonumber. You will need to decide if you really need to keep the field as an Autonumber with its current value. (If the current value isn’t necessary, there are workarounds.)


Here is one formula for the primary field:


DATESTR(date)


Here is another possibility that shows a bit more info.


DATESTR(date) & IF(Comments, " " & Comments)


Once the primary field is set up, open the Batch Update block, select the table with the linked record field and a view. For the actions, select the linked record field, and select “sort links” as the action. When you click “update record” the records in the linked record field will be sorted.


  • Author
  • Participating Frequently
  • 8 replies
  • May 30, 2020

My only use for the autonumber was to create a unique ID for each record, as the obvious alternative, the date, is not unique: I might have up to 20 separate records for events that happened on the same date. As such, could I safely delete the autonumber column and set the date as primary? (Indeed, is that the purpose of the formula you’ve just shown me?)


kuovonne
Forum|alt.badge.img+17
  • Brainy
  • 5987 replies
  • Answer
  • May 30, 2020
Sebastian_Page wrote:

My only use for the autonumber was to create a unique ID for each record, as the obvious alternative, the date, is not unique: I might have up to 20 separate records for events that happened on the same date. As such, could I safely delete the autonumber column and set the date as primary? (Indeed, is that the purpose of the formula you’ve just shown me?)


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.


  • Author
  • Participating Frequently
  • 8 replies
  • May 31, 2020

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
Forum|alt.badge.img+17
  • Brainy
  • 5987 replies
  • May 31, 2020
Sebastian_Page wrote:

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!


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().


  • Author
  • Participating Frequently
  • 8 replies
  • May 31, 2020
kuovonne wrote:

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().


That’s very reassuring to know!


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


Reply