Re: Capture the First/Original Value of a Date Field of a record before it is Updated and "Preserve" the original date in a separate Date Field Column

Mitul_Soni · ‎Nov 17, 2022

Thanks in advance to everyone that helps me with this!

For some context, I’ve built a Base with 2 tables to track my Customer order for items that we manufacture on per order basis. Here is the link to a Template Base

Customer Order logs
Factory Orders Status Tracker for the orders that are created to manufacture items to fulfill the customer’s order.

The Functionality I’m trying to Implement is in the Factory Order Status Table. The factory creates a record in the table for each Item Style# for every Order and after a few days, they add a “Factory Ship Date” in a Date field.

Now, I want to capture the Original Date/value that was entered in this “Factory Ship Date” field and copy it over to “Original Factory Ship Date” in case the Factory changes the date Ship date due to delays, so that I can track the Actual Ship date vs. the “Original Ship date” that was reported by the Factory.

Additional Help: An additional thing that would be nice to have is seeing how many times the “Factory Ship Date” field has been updated by using “Last modified” field and adding an increment of “1” to a Number field every time Last Modified is triggered.

Update: We also use Make.com(Integromat) in case that can help make this possible

Karlstens · ‎Nov 19, 2022

I wrote this automation, seems to do the trick - it triggers upon the Factory Ship Date changing;

There’s an increment for the count, and an OR operator that will fill in the original date if it’s empty.

let inputConfig = input.config();

let table = base.getTable("Factory Open Order Status");
let myRecord = await table.selectRecordAsync(inputConfig.recordId);


await table.updateRecordAsync(inputConfig.recordId, {
   //Increment the number of times a user updates the Factory Ship Date Field.
    "Factory Ship Date Updates": ((myRecord?.getCellValue("Factory Ship Date Updates")  || 0) + 1 ),
    
   //If Original Factory Ship Date is emtpy, update it with the Factory Ship Date value. 
    "Original Factory Ship Date" : myRecord?.getCellValue("Original Factory Ship Date") || myRecord?.getCellValue("Factory Ship Date")
})

Remember to create the input field when you build this Automation;

Within your Table, it also helps if you lock the permissions of fields that the Automation is updating, preventing staff from editing them.

These settings work well;

Also, if you ever have the need to return an earlier or later date, here is one of my preferred techniques;

let table = base.getTable("Factory Open Order Status");
let myRecord = await table.selectRecordAsync(inputConfig.recordId);

const originalDate = 
	new Date(
	  Math.min(
		...[
		  new Date(myRecord.getCellValue("Factory Ship Date")),
		  new Date(myRecord?.getCellValue("Original Factory Ship Date")),
		],
	  ),
	).toLocaleDateString('en-CA');

Initially, I had thought to use it for your stated problem, but then realised if anyone changed the “Factory Ship Date” value to an earlier date then it would incorrectly update the Original Factory Ship Date. :joy: That’s when I realised a simple OR statement will work.

One thing that I think your table might need, is an extra Date Check field if you’d like to capture the actual previous date to store in “Previous Actual Factory Ship Date” - I don’t think there’s a way to solve this problem programmatically without an extra date field to use for comparison. In attempting to solve it without the extra field, “Previous Actual Factory Ship Date” simply ends up displaying the same value as “Factory Ship Date” - making it rather useless.

See Solution in Thread

Kamille_Parks · ‎Nov 17, 2022

If the field might be updated more than once, the {Original Factory Ship Date} field would need to be a Long Text field, not a Date field to store multiple values.

Create an Automation that triggers when “{Factory Ship Date} is not empty” if you add new records via a grid/gallery/etc. view, or “record is created” if you add new records via a Form view or an external integration. Include a Update Record action step that inserts the value of {Factory Ship Date} into the {Original Factory Ship Date} field.

Create a {Last Modified} field, {Number of Updates} number field, and {Number + 1} field.

The {Number + 1} field will be a formula: {Number of Updates} + 1

Create an Automation that is triggered on “When record updated” and watch the field {Factory Ship Date}. Include an Update Record action step that:

inserts the value of the {Number + 1} field into the {Number of Updates} field.
inserts the value of {Factory Ship Date} into the {Original Factory Ship Date} field. If you’re storing multiple values, add a comma and then insert the value of the {Original Factory Ship Date} so you keep the list of dates that was already there.

Karlstens · ‎Nov 19, 2022