How do I set up a Time Stamp field?

Panorama X
1

Greetings all! I’m very new to Panorama—so I hope you’ll have patience with some rather basic questions…

This first one is about how to set up a Time Stamp field in my database. I realised this was a possibility when I saw it on the General tab in the Database Options. But “Time Stamp Field” said “none”, and in one database the dropdown list had two of my own date fields—but they didn’t work to record a time stamp; and in the other database there were no fields in the dropdown list. So that seemed like a dead end…

I then searched for Time Stamp in Help, and there I found the “timestampstr(” function. I tried to use this as a procedure in a new Text Display field, but it said that “timestamptstr” (with or without one or two parentheses at the end) was an “unknown statement”.

So I’m stuck: How do I get a field on my form that will display the time stamp when that record was last edited?

Thanks for your help!

2

Create your field (TimeStampField) to receive the time stamp, then set a procedure:
TimeStampField = timestampstr()
The field will then show something like: 2019.02.05.08.24.49.700

3

I’m not sure why your date fields were listed. I couldn’t duplicate that. In order for a field to be designated as a time stamp field, it needs to have an “Integer” datatype. Once the field has been designated as a time stamp field, editing any cell in the current record will cause a number to be put into that field. The number is the number of seconds since January 1, 1904. Panorama calls this number a “superdate”. There are several functions for converting superdates to user friendly text. You can find them discussed in the Help file.

4

First things first. To use a field as a time stamp field it has to be set up as a Number(Integer) field. So, try to create a new field set to the Number(Integer) setting and then choose this new field in the Database Options panel. Now every time you add a new record or modify any field in a record a new number will be entered in the field you set as the Time Stamp field. This number is a superdate that contains the date and current time at the time of modification. You can see this superdate info in readable terms using the superdatestr( function from a procedure or you can set up a second text field to show the readable value by having a .ModifyRecord procedure with the formula: TimeStampDisplay=superdatestr(MyTimeStamp) - replacing TimeStampDisplay and MyTimeStamp with the actual names of your Display and Time Stamp fields. Note that a current Time Stamp will be entered into the Time Stamp field when a new record is added but the Time Stamp Display field will not be updated until this new record is actually modified and the new Time Stamp is automatically updated.

If you want to fill your existing records with the current time and date Time Stamp you can go to your Time Stamp field and from the Morph tool menu or the Field/Morph menu choose “Morph Current Field…”. When the dialog opens have the Start with Formula option set and enter supernow() as the formula (note you can also check the Modify Empty Cells Only option to retain any current values). Click the Morph button and the field will fill with the current superdate. You can go to your Display field and do the same thing there but use the formula superdatestr(MyTimeStamp) to show the superdate strings - again using the actual name of your Time Stamp field.

The Time Stamp field can be hidden if you prefer and only show the Display field in the data sheet. Also note that the Time Stamp field will be updated anytime a field has its editing cell opened and closed even if no actual modification has been made.

5

Thanks, cmcp—that worked like a charm!

6

That explains it! In my original database (which I imported from Bento) I had those date fields marked as integers because they were years of publication—i.e., just a simple year number, no months or days.

But can I designate an existing field containing data as a TimeStampField? I couldn’t do it following cmcp’s instructions: I had to set up a new field called TimeStampField.

7

Thanks, that explains how the Database Options Time Stamp setting is supposed to work (though I did it manually following cmcp’s instructions). So I assume if I’d done it that way, Panorama would have set the formula and procedure of the new field automatically?

I’m grateful for the rest of your comments, and will maybe return to them in future; but I have to admit that they go above my head at the moment! I’m very much a newbie, still learning the basics. I appreciate your input, though.

8

Craig (cmcp) wasn’t describing how to designate a field as a time stamp field, he was describing how to create a procedure that used the timestampstr() function, and put the results in a text field.

9

OK, I see the distinction. It works out alright for me, though, since the new field I created can now replace the timestamp field that got imported from my old database app, which used a different kind of notation that I think would be incompatible: entries of the form “2013-10-18T21:11:57”.

10

If you want to continue using that format, you could change

TimeStampField = timestampstr()

to

TimeStampField = datepattern(today(),"YYYY-MM-DD")+"T"+("0"+timepattern(now(),"hh:mm:ss"))[-8,-1]
11

Wow! Didn’t know that was possible! I’d be happy to do without the fractions of a second that Panorama’s timestampstr( function displays; but I also wouldn’t want the exact raw format of my old db’s time stamp with the “T” in the middle—i.e., “2013-10-18T21:11:57”. My old database displays the date more normally, as “18 Oct 2013, 21:11”. I’d prefer something like “2013-10-18, 21:11:57”. How would that change your formula? Just replace +“T” with +", "?

Thanks for your help with this.

12

Yes. That would do it.

13

Lots of good answers here :slight_smile:

To clarify a bit, there are actually two features that have been described on this thread. The first is setting up a .ModifyRecord procedure in your database, which allows you to run your own custom code any time a database value is changed within a record. This is a valuable technique, but be careful not to run too much code at that point, or you will slow Panorama down.

There is also a second procedure you can set up, .ModifyFill, that gets triggered by operations that modify an entire field of data at once. If you don’t set up this procedure, “mass” changes made by the Field>Morph menu will not trigger any custom code.

Both of these special procedures are covered in this help page:

The second feature discussed on this thread is the Time Stamp Field. This feature allows you to designate a field that will be updated with the current date and time (in SuperDate format) any time that data is modified, whether by data entry, morphing, etc. This feature works with no custom programming on your part. So it’s a bit faster and simpler than the previous method, but all you get is a number (seconds since 1904) – you can’t customize what happens.

This thread has brought to light that the Time Stamp Field feature is not documented in the Panorama X Help. It was documented in the Panorama 6 Handbook, and it works pretty much the same as it did in Panorama 6, but that documentation was never brought over to the Panorama X help (other than a mention in one of the beta release notes). I have made a note in the bug report database to correct this oversight.

All this is a bit complicated further by the fact that you can combine both of these techniques, using both techniques at the same time, as Dave Thompson did in one of the posts above.

14

Great, thanks Dave. I’ll try that.

15

Thanks, Jim, it would be good to have something about it in the Help.

16

I agree, this was strictly an oversight.

Below is the documentation of this feature from page 301 of the Panorama 6 Handbook (http://www.provue.com/classic/Panorama%20Handbook.pdf). However, instead of using the Design Sheet to set this up as described below, you use the General pane of the File>Database Options dialog. Other than that, the feature works exactly the same.

Automatic Time/Date Stamping

Using the design sheet you can designate a field as a time stamp field. Once a field has been designated as a time stamp field, Panorama will automatically copy the current date and time into this field every time any other field in the record is modified. Setting up a time stamp field allows you to reliably track when each record in the database was last modified.

To set up a time stamp field you must have the design sheet open. Choose the Time Stamp Field command from the Special menu, then use the pop-up menu to select the field that will become the time stamp field. This field must be an integer (numeric 0 digits) field and it must already exist in the data sheet.

Once the time stamp field is set up, Panorama will automatically update the time and date every time any cell in that database is modified. The value stored in the cell is actually the number of seconds since midnight, January 1, 1904. This combination of date and time into a single number is called a SuperDate.

As you can see, it’s pretty difficult to look at a SuperDate and make much sense of it. Fortunately, you can convert a SuperDate into a regular Panorama date with the regulardate( function, and into a regular time (seconds since midnight) with the regulartime( function. Here is a formula that converts a SuperDate in the field LastModified into a readable date and time.

datepattern(regulardate(LastModified),"mm/dd/yy")+" @ "+
  timepattern(regulartime(LastModified),"hh:mm:ss am/pm")
17

Thanks for giving that documentation, it makes the process a lot clearer.

In there it says:

—and goes on to give a formula to do that conversion.

I’m wondering if this could help with another of my date fields, also inherited from my previous database app. This is called “Date Created”, and records the date and time the record was first entered in the database. It has the same format as the time stamp field, i.e. “YYYY-MM-DDThh:mm:ss”. I’d like this to be displayed in the same format as the time stamp field, e.g. “2013-10-18, 21:11:57”. Can I use the formula you quoted above, substituting (Date Created) for (LastModified) and adapting the date and time format? (Just wanting to check in case it could corrupt my data…)

Would I enter it preceded by "«Date Created» = " on the Procedure panel of the graphic properties?

18

The Time Stamp field doesn’t actually have a format – it’s an integer. So the actual value in the field is just a number.

I think what you are saying is that you have a text field with text in the format YYYY-MM-DDThh:mm:ss. So the first thing you need to do is parse the data into a format Panorama understands. Does the T in there mean that there is literally a T in the data, for example

2013-10-18T21:11:57

If yes, then the parsing could be done quite simply, this formula would produce a superdate.

superdate(date(before(«Date Created»,"T")),time(after(«Date Created»,"T"))

Then you could produce the text format you wanted like this (I’m assuming you made a SDate field for the superdate value):

datepattern(regulardate(SDate),"YYYY-MM-DD")+", "+timepattern(regulartime(SDate),"HH:MM:SS")

Or, if you wanted to just transform straight into the text format you are looking for, you can skip the superdate completely:

datepattern(date(before(«Date Created»,"T"),"YYYY-MM-DD"),timepattern(time(after(«Date Created»,"T"),"HH:MM:SS")

Though in this particular case, you could also go with a simple text replacement:

replace(«Date Created»,"T",", ")

You can’t corrupt your data in the sense that Panorama can’t read the data, but with the wrong formula you can certainly turn your data into nonsense. That’s when Panorama X’s multi-level Undo feature is a real lifesaver!

19

Yes, that’s what I meant. The string above is exactly the what I have in the “Date Created” field.

So, sorry for the simple questions—newbie still trying to get my head around the way Panorama works!—but when you say “parse the data” you mean literally change what’s in the “Date Created” field—either putting the output in a new field (“SDate”), or altering the data in the original field? I’m trying to distinguish that from “formatting” actions that leave the underlying data unchanged, but alter the way it appears in a form (if I’ve understood the process correctly!).

If your last suggestion of a simple text replacement is of the former kind, i.e. changes the actual data in the “Date Created” field, I assume I would set that up as a procedure to be run on that specific field. Then I wouldn’t need to do anything in the form except reproduce the text as it stands in the “Date Created” field.

Again, apologies for the very basic questions, but this is a useful learning exercise for me, and I appreciate your help and that of others on the forum.

20

Parsing the data means to extract pieces of information from a larger whole. “2013-10-18T21:11:57” contains both date and time information, separated by the “T”.

before(«Date Created»,"T")

extracts the text that precedes the “T”. The date( function recognizes that text as a date and converts it to the format that Panorama uses to store dates, (a number.)

after(«Date Created»,"T")

extracts the text that follows the “T”. The time( function recognizes that text as a time and converts it into a number, (the number of seconds since midnight.)

The superdate( function combines those numbers to produce the superdate, which is a single number. The original data was text, and would have to be in a text field. The result of the formula is a number, which will have to go into an integer field.

His last suggestion was a formula for changing the data. The result of that formula could go right back into the same field and replace it, or it could go into a separate field, leaving the original field intact.

With numbers and dates the same values can be displayed in a variety of ways, without changing the underlying number. Formulas that operate on text will be making changes. They can extract pieces of it, or combine text to form a new string, or replace some text with some other text, or any combination of the above. That sentence just scratches the surface. Panorama has dozens of functions that deal with text in one way or another.