Calculation to show oldest and newest creation date?

[Fredrik]

Hi!

On a layout in browse mode I have added merge fields (based on an auto enter timestamp fields) to show creation and modification date/time for each record. That was very easy to do.

But then I wanted to add something similar to my list layout, and I can't figure out how to do it. I would like to create a calculation field to show the oldest creation date (the oldest record) and one for the newest creation date (the most newly created record) for the found set in the list.

I hope someone can help me with this:-)

[Vaughan]

I would like to create a calculation field to show the oldest creation date (the oldest record) and one for the newest creation date (the most newly created record) for the found set in the list.

You can use summary fields for this. However this causes a HUGE performance hit as the number of records in the found set increases, because FMI must look at every record and pull the field data from the server to calculate the max and min dates.

Unless such as display is really, unavoidably needed, don't show it on the normal list layouts.

I am working with a solution that has two summary fields on the main form view, and the table has over 100,000 records. It's a >15 second wait for the calculations to finish. Painful, but they like it that way. :(

[Fredrik]

Thank you very much for your speedy reply:-)

You can use summary fields for this.

This certainly works the way I wanted. I'm just a beginner and haven't really explored the summery fields yet. Thanks for enlightening me:-)

Unless such as display is really, unavoidably needed, don't show it on the normal list layouts.

And I'm just wondering if there is absolutely no other way of showing this information that is more efficient?

[eos]

And I'm just wondering if there is absolutely no other way of showing this information that is more efficient?

Depending on how you create records, you could have a script set a global field to the latest creation date after creating a new record. (I'm not sure how you would introduce an older record into the same table, other than manually changing the timestamp, so this datum should be fix - or shouldn't it?)

[LaRetta]

You could also handle it by including at the end of your find ...

Unsort records <- unnecessary immediately following a find

Go to record/request/page [ first ]

Set variable [ $$range ; GetAsDate ( creationTimeStamp ) ]

Go to record/request/page [ last ]

Set variable [ $$range ; $$range & "..." & GetAsDate ( creationTimeStamp ) ]

Go to record/request/page [ first ]

Type <<$$range>> onto your header of your report.

Edited June 12, 2012 by LaRetta

[Vaughan]

And I'm just wondering if there is absolutely no other way of showing this information that is more efficient?

Well, if you walk into a library and ask them how many pages there are in all the books they have, somebody has to count the number of pages in each and every book at least once. Yes it's a HUGE job, but you wanted to know and there is no other way of finding out.

An optimisation would be to remember the number of pages in each book so it only needs to be counted once (and remember to re-count the pages if the book changes) then add up the page count for each book -- still a big job. Further optimisation would be to do the counting as each book is entered into the library and remember the total, but the risk is that the total might not be 100% accurate because some books have been added but the total hasn't been updated recently.

So it becomes a compromise between accuracy and convenience.

Displaying the summary fields on the layout is MAKING the database re-calculate the total EACH TIME the layout is displayed. You'll have to concoct something else to avoid this. I rarely put such totals on a main interface layout. If that data is needed then the user generates a report to calculate the information. This avoids inconveniencing every other user all the time.

[Fredrik]

Thanks for the replies guys!

And Vaughan; your explanation was very clarifying.

I really appreciate you taking the time to explain such elementary subjects to a newbie:-)

[LaRetta]

"I would like to create a calculation field to show the oldest creation date (the oldest record) and one for the newest creation date (the most newly created record) for the found set in the list."

I guess I am missing something here ... if you want what you have explained in the sentence above then what I have explained is instant and does exactly that.

[Fredrik]

Hi, and I'm sorry that I haven't been able to give a reply sooner.

I guess I am missing something here ...

Well, it's more likely that I'm missing something I suspect. I guess I didn't quite understand your example in regards to my problem for some simple reasons that may or may not be relevant. You mentioned:

…at the end of your find ...

…of your report.

And this confused me since I my problem didn't include either of these two: a find or a report.

I wanted a field to put on my list layout and not a report. The field would be placed in a lower corner of the layout and would be showing always regardless of how many records the list was showing or how they were sorted or in which found sets. And as Vaughan suggested a Summery field solved my problem.

So if what you explained can do this in a more efficient way than a summary field that would be great. But could you please explain in more detail how I could do it then?

[LaRetta]

.. and would be showing always regardless of how many records the list was showing or how they were sorted or in which found sets. And as Vaughan suggested a Summery field solved my problem

Summary fields address only the found set as well.

I would like to create a calculation field to show the oldest creation date (the oldest record) and one for the newest creation date (the most newly created record) for the found set in the list.

You say for found set so I assumed you performed a find and that is why I said "after your find". As for using term report, a list is same thing. Anyway, if you want the results to display start and end in the entire table regardless of found set, you will need to then begin with 'show all records' with either of our suggestions.

If you need to know based upon all records, it would be easier to use self join (call it yourTable 2) based upon Cartesian product (X in the graph). Then first related record is your oldest creation timestamp. For the last timestamp create a calculation of

Last ( yourTable 2::creation timestamp )

You had asked if there wasn't more efficient way than Vaughan's suggestion of summary and I've used the technique I've described (jump to first then last) and it can be faster. :^)