Best way to preserve Create Date when copying a record? (e.g. using Automation)

[itoldusoandso]

I need to copy a record from one table to another table (or from one base to another base). The records I need to copy are the ones I label as “Expired”. The act of copying them will be done manually, that’s not an issue. What I am looking for is how to preserve the date in the Create Date field when copying the record.

Right now copying the record would trigger the Create Date field to be updated with a new date (=now date). What I want is the new copied record to cary over the original create date.

One solution I came with is to:

1. In Table 1 (the record has Create Date field with the date)
2. In Table 1 add another field called “Original Date”
3. Set up Automation in AirTable or Integromat. It would trigger when this “Original Date” date field is empty.
4. When copying the record into Table 2, this Original Date will be carried over (although the Create Date will have a new date =now date).

I haven’t tested Airtable Automations if it will work but I assume it should (another option is to use Integromat but I have limited number of credits there so I’d rater stick with automations).

Wanted to check for other ideas before I do this. The solution I came up is not a pretty one so wanted to check if there is another better way doing this.

[Justin_Barrett · Accepted solution]

Let me make sure I’m understanding your process before making a suggestion.

You have records that, at some point, will reach an “Expired” status. Your table has a field named {Create Date} that is the “Created time” type, where you’re capturing the date the record was first created. Once a record has expired, you want to copy the entire record to a new table, and keep that {Create Date} value intact.

If that’s correct, here’s my suggestion: in the target table where these expired records are being copied, don’t use the “Created time” field type for the {Create Date} field. Just make it a regular date. No matter how you copy the data between tables, this will preserve the original date.

[kuovonne]

If the new table will also have original records that need to have their actual creation dates, you can add a formula field that checks if there is a manual created date override. It would show the manual/override creation date if there is one, and the actual creation date if there isn’t.

[itoldusoandso]

Justin_Barrett:

where these expired records are being copied, don’t use the “Created time” field type for the {Create Date} field. Just make it a regular date. No matter how you copy the data between tables, this will preserve the original date.

That’s a nice and simple solution. Earlier I copied the whole table structure from Table 1 into the Table 2 so the Create Date was carried over. I changed to normal date and now the original date is being carried over. That’s great. (Or even simpler, copy record in Table 1, create brand new Table 2 and past the record (allow expand the table fields).

The way I use this field is to calculate Amazon Warehouse storage fees. When the item is sold, I will copy the record into the “Sold table” but want still show the calculated storage fees.

(The storage fees in Amazon warehouse are calculated by the amount of days the item is being stored there. Once the item is sold, I want to keep the record for history / analysis but the storage fees should stop adding up because the item is sold.)

That’s good enough for me. Great.

@kuovonne

That would work as well and offer a fall-back option but it seems it would require a manual step of entering the “manual/override” date. Correct?
So if I don’t manually create the date in this field, then the fall back date will be chosen the new(er) create date.

[kuovonne]

This just a suggestion in case you were also creating records in the new table that weren’t copies. However, as that isn’t the case, you don’t need it.

[itoldusoandso]

Actually I came back to your suggestion again and I found benefit for it too. I have a follow up question related to that and I’ll ask in a new post. Thank you.