How to change the date of legacy data (being imported into Jestor) while at the same time having a field for automatic dates?

Ask the community Fields and database
#1

So I have this old table in Excel with dates for Action Plans, which I want imported into Jestor.

If I import that table into Jestor, I will have the standard columns for Created By (user) and Created In (date), which are automatic.

All dates will appear as the date of import and the users will be the Jestor Automation.

The dates of the action plans will be another column of the date type.

But that column won´t be automatic like the Created In date.

So, is there any way to have a Date column with legacy dates from the imported data and AT THE SAME TIME, new records have automatic current dates?

#2

Sorry, but I didn’t understand what you mean by that. Could you explain please?

#3

I will try. :slight_smile:
Hmmm…

1 - Ok, the only way to get AUTOMATIC current date, is the standard CREATED AT field, right? So if I today create a record I should have created last week, the date the record was CREATED is there. (so the boss can see I did not do what I needed at the correct date)

2 - Date fields that you can create don´t have automatic date option. The user can SELECT the date (and if he wants, select past week to say he did his job on time)

3 - If I import a table, all records will have the CREATED AT date as the date the data was imported right? So, if I import 300 records today from Excel and they have a DATE column, the resulting Jestor table will have a standard CREATED AT date column where all records are 15/06/2022 and another Date Column with the original dates of the olds records in Excel

4 - Now I want NEW records created at Jestor. And I want their automatic creation date (so users can´t lie when they created the record)
The only way to get automatic creation date at Jestor is to use the standard CREATED AT column.

Ok, so I create a few new records, for days 16/6, 17/6, 18/6, 19/6 and 20/6.

Now I want to order all those table records, imported and new, by date.

If I order by the CREATED AT column, I will have 300 records with date 16/6 that were imported, and the 5 new records.

If I order by the Date column where I inserted the original creation date (in Excel) of those 300 old records, then the user can insert ANY date he wants in the date column of the new records inside Jestor, because I can´t make it automatic.

#4

I thought of a not ideal solution for the above dilemma, while writing the text above.

I can create a DATE column and a FORMULA column.
The old records all come with the Date Column already filled (and the Created At also filled but all with same date)
The new records will have the correct CREATED AT column with real date of their creation, but I don´t want users to choose the date in the date column.

So what I do is:
1 - HIDE the DATE column. So users creating new records can´t insert or change dates in it
2 - have a formula column. If DATE column has a date, then = DATE. (thus, old records will show their real creation date at the formula column)

If Date column is BLANK (new records, since the date column is hidden to users) then equals CREATED AT column (gets creation date of new records)

#5

Ok, got it!

The best solution is kind of what you did, using formula and two date field!

Any software migration you would have this problem cause the created at is an automatic no editable field.

1 Like