cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
saitcopuroglu
Level IV

how to create a time stamp column (record date)

Hi,

I do not know which formula to use for the result of;

A data table each day entering data manually (which creates new rows) and I need a date variable column using as "record date" or "time stamp" which represents the time when the new data has been entered as new row.

Any help appreciated,

Many thanks in advance

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: how to create a time stamp column (record date)

One more idea...

  • Add a table script to the table called OnOpen.  Table scripts by this name are automatically run each time the table is opened.
  • In the OnOpen table script, add an OnAddRows subscription to the data table that will automatically assign the date to a specific column each time a row is added .

Attached is a simple example to demonstrate.  Please let me know how that works for you.

Wendy

Wendy

View solution in original post

David_Burnham
Super User (Alumni)

Re: how to create a time stamp column (record date)

I think it performs the task as you requested it.  The problem regarding rearranging the tables is common to all formulae.  But since your request was for a formula solution lets not give up yet.  First when you sort there is an option to prevent re-evaluation, or even better, drop the formula by not copying it

9338_sort.PNG

But on the basis that there may be some other operations you perform which trigger re-evaluation then the formula can be revised to evaluate Today() only if there is no current value:

9339_fmlmissing.PNG

-Dave

-Dave

View solution in original post

12 REPLIES 12
David_Burnham
Super User (Alumni)

Re: how to create a time stamp column (record date)

You can use the Today() function and format the column as a date time:

9293_timestamp-def.PNG

As new rows are added as part of the manual data entry the formula with me evaluated:

9294_timestamp.PNG

You could of course hide this column

-Dave
David_Burnham
Super User (Alumni)

Re: how to create a time stamp column (record date)

sorry didn't see the Erics post when I replied

-Dave
Eric_Hill
Staff

Re: how to create a time stamp column (record date)

Well, my post was wrong (all I did was set Initialize Data to Today(), which was not enough, you need the formula column), so I'm glad you replied.  I deleted my post so nobody would be confused.

Eric

saitcopuroglu
Level IV

Re: how to create a time stamp column (record date)

Dear David,

Dear Eric,

Thank you for your help but it does not seem to perform the task. Yes it does when entering data but if you make any arangments (like I do as sorting the data) the Record Date column with Today() formula resets itself.

If there is any other idea I would be grateful.

Eric_Hill
Staff

Re: how to create a time stamp column (record date)

Hey, saitcopuroglu,

It seems that neither approach (setting Initialize Data for the column to Today and creating a "Today()" formula column are satisfactory solutions to what you are trying to do.  Initialize Data is temporary, and the formula column could re-evaluate at a later time, invalidating the data.  It would be nice if creating an initializer for a column was persistent, so that it always initializes the data in a column when a row was added, but currently it doesn't work that way.

Your best bet may be to script the data entry into the table, so that your script initializes the timestamp column.

Eric

Re: how to create a time stamp column (record date)

 

Here's an idea. This idea is based on the notion that you will be entering data manually. I am making a big assumption that one more manual step will be OK with you.

1. Assume you are using a table like the one created by the JSL below, similar to the image above.

2. Every time you add a row, the "date w formula" column will update in real time, which contains the today() function suggested by Dave@Pega-Analytics,

3. After you are done, and before you close the table, use Copy and Paste to Copy the values from "date w formula" to the "static date" column. The values will be copied and will be static. Note: only copy the empty values in a single session.

4. Save the table, and this method can be repeated on future days as more data comes in and needs to be entered manually.

 

/

/JSL to create sample table (I hope the formatting persists)
dt = new table("example", Add Rows(4),
   New Column( "my raw data", Numeric, Set Values( [1, 2, 3, 4] )
   ),
   New Column( "date w formula", Numeric, "Continuous",
     Format( "m/d/y h:m:s", 23, 0 ), Input Format( "m/d/y h:m:s", 0 ),
     Formula( Today() )
),
New Column( "static date", Numeric, "Continuous",
    Format( "m/d/y h:m:s", 23, 0 ),
)
);
 

 

David_Burnham
Super User (Alumni)

Re: how to create a time stamp column (record date)

I think it performs the task as you requested it.  The problem regarding rearranging the tables is common to all formulae.  But since your request was for a formula solution lets not give up yet.  First when you sort there is an option to prevent re-evaluation, or even better, drop the formula by not copying it

9338_sort.PNG

But on the basis that there may be some other operations you perform which trigger re-evaluation then the formula can be revised to evaluate Today() only if there is no current value:

9339_fmlmissing.PNG

-Dave

-Dave
Stefan
Level III

Re: how to create a time stamp column (record date)

Nice workaround. Is there any way of putting in a timestamp when a cell is updated/changed? Eg from status "Fail" to status "Pass"?

Re: how to create a time stamp column (record date)

One more idea...

  • Add a table script to the table called OnOpen.  Table scripts by this name are automatically run each time the table is opened.
  • In the OnOpen table script, add an OnAddRows subscription to the data table that will automatically assign the date to a specific column each time a row is added .

Attached is a simple example to demonstrate.  Please let me know how that works for you.

Wendy

Wendy