Cell Metadata - Last Mod Time

Patrick-Kennedy · ‎Dec 27, 2021

It’s curious to me that the last mod time for any cell is not available through the scripting, REST, or metadata API as part of any records’ metadata.

For example, I would think that for Airtable to display the data in a last mod time field, especially comparatively when more than one field is specified in the config, the last mod time would need to be stored somewhere. Perhaps I am just thinking about it incorrectly.

That the cell values update accordingly when the last mod time field is reconfigured (e.g. a specific field has a date from months ago, but when the last mod time field is reconfigured to show all fields and something was updated today, it shows a date-time value from today) makes me think the data must be somewhere… just not accessible :frowning: :man_shrugging:

Per the API doc on lastModifiedTime, the cells are read only and creating or updating such fields is unsupported.

Where or how does Airtable gets this data when a last mod time field is created and configured? I.e. where or how does Airtable get the data of any field’s last mod timestamp?

Context: I have a bunch of records to merge which all have been inconsistently updated. The table has close to 200 fields, about 50 of which are modifiable and in need of updating by comparison. I’d like to compare the last modified times per record, per field as the most recent mods are likely to be the most accurate. Is my only option to manually create last mod time fields for all 50 fields? If so, that seems kind of cray, but definitely not as craycray as the maintenance of this table’s records.

:confounded: :grinning_face_with_sweat: :upside_down_face:

kuovonne · ‎Dec 27, 2021

You could do that. You could also create a formula field that uses the LAST_MODIFIED_TIME function if you want to consolidate all that information in a single

Are you also using the Dedupe App? An initial run through of that app can give you a better idea if you really need to know the last modified time of every editable field, or only some.

Patrick-Kennedy · ‎Dec 27, 2021

Thanks @kuovonne,

Unfortunately I will need to make comparisons for each duplicate set, modifiable field by modifiable field as the data entry has been inconsistent. FWIW, I add an “all fields” last mod time to all the tables I work with (including created time, created by, last mod by).

Unfortunately, the dedupe tool is not up to the task here either, e.g. record A has field 1 more recent but dupe record B has field 2 more recent 😕

kuovonne · ‎Dec 27, 2021

You could use the Dedupe tool in conjunction with a method for identifying which record has the most recent value for a given field. You can use two windows for this: one for the Dedupe app, and another where you can manually filter for the records identified by the Dedupe app, and then view the last modified times. This is why I suggested consolidating all of the last modified times in one formula field.

CONCATENATE(
    "Field 1: " & LAST_MODIFIED_TIME({Field 1}),
    "Field 2: " & LAST_MODIFIED_TIME({Field 2}),
    "Field 3: " & LAST_MODIFIED_TIME({Field 3})
)

Patrick-Kennedy · ‎Dec 27, 2021

Thanks @kuovonne, I appreciate the suggestions.