Refresh prompt when you change pages?

Sunday 16 July 2017 @ 2:03 pm

This strange behavior has nagged at me for a while. I refresh a report that has subreports and preview the first page. When I move to the next page I get a prompt that says:

“Change in record selection formula”

and the choices are “Use Saved Data” or “Refresh Data”.

Now it is obvious that I haven’t changed the selection formula by clicking the “next page” arrow, and yet I have seen this behavior in several reports. Usually it doesn’t cause a problem and I just ignore it.

But today I worked with a customer who wanted to fix that behavior so we took the time to do some experiments. After a few tries it finally dawned on me that there was one way that a formula will change, all by itself, between one subreport and the next. When a formula references the current time it will change slightly as you page through the report. Sure enough, the selection formula in the subreport was limited to activity in the last 15 minutes:

{DateTime.Field} in DateAdd ('n', -15, CurrentDateTime) to CurrentDateTime

To fix the problem we need a single DateTime value to use in all of the subreports. So we decided to calculate a value in the main report and pass it down to the subreports as a parameter. To make sure the value didn’t change on each page we wrote a formula to combine the functions DataDate and DataTime like this:

// {@DataDateTimeEnd}
DateTime(DataDate, DataTime)

The advantage of using the DataDateTime is that once the report is refreshed that value doesn’t change from one page to the next. The CurrentDateTime function can return different values as the report goes through the pages.
I then calculate a second DateTime that is 15 minutes before the value above:

// {@DataDateTimeBegin}
DateAdd ('n', -15, {@DataDateTimeEnd})

Last, I pass these values down to the subreports as linked parameters and use them in the selection formula like this:

{DateTime.Field} in {?Pm-DataDateTimeBegin} to {?Pm-DataDateTimeEnd}

We still get the last 15 minutes of transactions, but without any prompts.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro