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