Subscribe Bookmark RSS Feed

how to create a time stamp column (record date)

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

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

10 REPLIES
David_Burnham

Super User

Joined:

Jul 13, 2011

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

Joined:

Jul 13, 2011

sorry didn't see the Erics post when I replied

-Dave
erichill

Staff

Joined:

Oct 1, 2013

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

Community Trekker

Joined:

Sep 29, 2014

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.

erichill

Staff

Joined:

Oct 1, 2013

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

audrey_shull

Staff

Joined:

Jun 23, 2011

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

Joined:

Jul 13, 2011

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
Wendy_Murphrey

Joined:

Jun 23, 2011

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
saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Thank you Wendy, started testing this morning and it seems to work ! I will test for few days and will feedback, many thanks