Help

Column to check if a number has changed in another cell

1888 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Finck
6 - Interface Innovator
6 - Interface Innovator

I need a new column to do an if statement. Yes, if another field has changed from its original input value; no if it has not. But I have no idea how to do a validation check if the current value is the same as it first was added

4 Replies 4

Hi @Chris_Finck

I don’t think it’s possible, using the basic functions of Airtable, to compare a field value to its original input value.

Hi @Chris_Finck,

You might be able to achieve something like this using a “Last modified” field in conjunction with a formula field.

You can have the “Last modified” field watch only for changes to this number field in question - it will display a time stamp of when that field was last modified, and will update with a new time stamp every time a change is made to it.

Here’s where it might get a little tricky, though… depending on how your records are initially created, the initial time stamp in that field may not be an exact match with the result of a CREATED_TIME() function, which we are going to compare it with. If the records in question are always created via a shared form or some API action, then they might be identical, and a direct comparison might work. But let’s assume you create your records directly in Airtable, by clicking to create the new row, and then filling in the data — in this case, the CREATED_TIME() function will reflect the moment you clicked to create the new row, and the “Last modified field that is watching for changes to your number field will reflect the moment you entered the first datum into that number field.

So, if there is some known tolerance, such that the time stamp for this number will always be within, say, 1 hour of the record creation time, and you are only ever concerned with changes made after that initial hour, we can capture than tolerance in a formula:

IF(
   {Your Last Modified Field},
   IF(
      DATETIME_DIFF(
         {Your Last Modified Field},
         CREATED_TIME(),
         'hours'
      ) > 1,
      "Yes",
      "No"
   ),
   "No"
)

You and I had the same logic basically. I ended up solving my creating an original time field and every time a new Record was created it would run automation to copy the time input to this original time field. Then I created another column that would check if those were the same date and time. The premise being if the scheduled pick up time field changed it would not be the same as the original static field which would then return and if then statement of yes or no.

Hmm, I interpreted this as a need to compere the value of the field with the value of the original input, not only if that field has changed since the input. Overthought it I guess :man_shrugging:

But nice solve.