How to force a recalculation?

[Cecile]

I was told to stay away from scripting if possible. So I am trying to find a way for a field to recalculate "on demand" the "on demand" being when generating a report or when a user visits the tab were the field is displayed (in a portal).

I have a relation between Project-ProjectStatus-byProjectID
In my table Project Status, there is a date where status is set (Status Began) and a date when the status ends (Status Ended). Those are set by script trigger when certain events occur in the system. Since the last status normally doesn't have an end date, in order to calculate the duration in working days up to now of that status, which is particularily relevant if the status is "on hold", I have created a calculation field that gives current date if StatusEnded date field is empty. That EndForCalc field is the one used in the relation to calculate working days.

Problem is that I don't know how to force recalc so it is still showing last week's date, as you can see on the last row, when I was first playing with this.

[Malcolm]

Calculation fields for UI use. Tell it to be unstored, and always displaying Get(CurrentDate). Unless there is an end date. If there is an end date, display that instead.

If ( Isempty( End Date ) ; Get ( CurrentDate ) ; End Date )

You'll need an unstored calc for the number of days too. Same thing, default to Get(CurrentDate) - Start Date. If there is an End Date, display the static, stored job Duration.

If ( Isempty( End Date ) ; Get ( CurrentDate ) - Start Date ; JobDuration )

[bdbd]

Were you told why to stay away from scripts? A daily script is a less problematic and surer approach to me.

[Cecile]

This is Exactly what I have in my field definition. But it doesn’t spontaneously recalculate on record load or when the portal is displayed. In fact entering something in another field of the same table doesn’t trigger it either.

[Malcolm]

Portal refresh?

[Cecile]

Just added a button refresh portal (did name the portal) and nothing happen

[Cecile]

Wasn't told. Though I believe it has to do with the existing script that generates the current report. I'm adding a new column to that report. I guess they don't want to add more calculations than there is already. A daily script would not be interfering with the report script. I will ask if I have permission to add this daily script. I assume it would be on server correct?

[bdbd]

Yes, this script should be scheduled on the server. There is ideally already a master daily script from which you could call your maintenance script.

As for the supposition that adding to an existing script is due to calculation performance… I don't buy it. Unstored calculations perform calculations one way or another when working as expected. They can even take up more resources than a script, considering you have a lot of control when executing a script.

[Malcolm]

This sort of data can be really sticky. Have you experimented with commit, page refresh and cache reloading? Sometimes the answer is to go and touch the data.

@bdbd's suggestion of a script is good. Locate records in that table that have an empty end date, then modify the duration. Run it once a day to keep the doctor away.

As with all server side scripts, you will need to document it and ensure that they are maintained across server restarts, system upgrades, and so on.

[Cecile]

I need a huge HAT OF SHAME.

My field definition was exactly as you said but... a date type. Not a calculation field type set to data type date.

I am crawling under a rock now....

[Cecile]

Not that the solution is to crawl under a rock... but to use a calculation field when you need a calculation field. Not an auto-calculation inside a date type field. ehm...

[bdbd]

So you recommend we say to students and inexperienced users to wear a hat of shame, a huge one at that, whenever they make mistakes?

[Cecile]

Nah we say that to someone who 6 years after being that newbie who did make such error for not understanding the difference (normal when you begin) has mental lapses and can laugh about it. I would never shame someone beginning or inexperienced.

Although I wish we could have a nice stiker saying beginners look here! that we could post on questions like these because they would show that there is room for anyone at any level on the Soup!

[Malcolm]

[quote="Cecile, post:13, topic:3352"]
I wish we could have a nice sticker saying beginners look here! that we could post on questions like these [/quote]

How about using the Gotcha/Warnings badge

[Cecile]

Didn't know we had one. Scary...

[Kirk]

I've been converting virtually all my unstored calcs into auto-enter calcs with a LET based trigger field using a timestamp field. Overnight, looping through all the records and setting the timestamp, forces a recalc of all the formerly unstored auto-enter calcs, creating, in effect, static field content. (note: Using a timestamp ensures that the data will have changed; you could use a Get (UUID) if desired for the same effect).

Let
[ ~TriggerTS = z_TriggerTS
]
;
ANY CALCULATION HERE
)

Any change to the z_TriggerTS field, will force the auto-enter to re-evaluate.

Then, during user interaction, every COMMIT sets that timestamp trigger field for the current record, keeping the data in the same state as if it were an unstored calc, but without the performance limitations.

There are potential "gotchas" in this design approach, where referenced unstored calcs that did not get converted, cause some radical performance degradation exacerbated by the force recalcs, but it is managable.... and YOU control when the data updates.

A more ideal [performant] approach would be to always set fields through scripts, but that increases complexity / potential for errors.

[Malcolm]

@kirk, why have you chosen to use the Let() function instead of the built-for-purpose Evaluate() function?

[Kirk]

The jest of the technique is that if a LET variable gets set, the entire calculation recalcs without dependencies on the trigger field. It’s sole purpose is to force the calc.

Some external stimulus needs to be the impetus to force the recalc.

[Cecile]

I will be adding a bit to this story.

Today I had to test something that involved that temporary date. To my stupefaction instead of today's date in the field was Jan 31. The day I had my epiphany and changed the date field for a calculation field.

Obviously, despite being a calculation field, it was not updating.
I tried changing some data from other fields of the table, i put a button on the tab panel to refresh the object trying successively the tab panel, the portal and the field itself. I tried setting the field to itself (not really a change worth updating but who knows), commit record. I tried putting triggers on the tab and on the portal. Nothing got that field to update.

So it is evident that I got the impression in January that I had achieved my goal but was fooled by the auto update that occured when and BECAUSE I changed the field type.

Then while I was looking at the data viewer, I was only getting values for the first row (at the bottom, it is sorted in reverse order, for the purpose of another developer's scripts needs) I remembered (not 100% sure this is actually accurate) that only the first related record (the one in a portal) gets updated when the active record loads or gets updated. Which would explain why i can't get any value for the last record in the data viewer, no matter on which row I click in the portal.
That doesn't explain why the record doesn't updates when I change values of fields in the same portal row...
So anyway, i went to a layout which table is the same as the portal's and tried the button and such other tricks get the record to update. It did not.

I went in the field definition and changed it for @Kirk 's method

Let ( 

	[ var_TriggerTS = z_TriggerTS;
	  var_status = If (Status = "Closed" or Status = "Complete" or Status = "Cancelled"; "final"; "temporary");
	  var_chgTemp = Case(
				 		IsEmpty (EndDateTemporary); "yes";
						not (IsEmpty (EndDateTemporary)) and var_status = "final"; "no";
						not (IsEmpty (EndDateTemporary)) and var_status = "temporary"; "yes"
				   );
	  var_false = EndDate;
	  var_true = If (var_chgTemp = "yes"; Get(CurrentDate); EndDateTemporary)
	];

	If (IsEmpty (EndDate); var_true; var_false)
)

Of course now it updated. But I might be fooled again because by changing the calculation, it forces recalc of the field for all the records. I will only know it worked if tomorrow when I click on the button that sets z_TriggerTS, the field updates for the specific record I'm on.

It would be best if I could test right away but I have a bout of mental fatigue (or laziness) and can't think of a simple and quick way to overite the value in the calc field, to see if it indeed changes when i trigger the timestamp. (And that is not what i was supposed to waste my time on today to work on today, but it is prerequisite that it works however.)

[Kirk]

Not all records should update; only those that you set the timestamp trigger in the let calc that forces the entire calc to reevaluate.

I update the z_TriggerTS timestamp fields all at once in a scheduled script overnight AM so that anything dependent on a date change, would be reflected in the result based on today.

BUT I also have that field set to update on a OnRecordCommit script trigger. That way, any update that would be impacted as individual records change, would have live updates of the calculation.

However, for calcs based on the current day, the scheduled script keeps them correct; the onRecordCommit effort is for any other formerly unstored calculation that is now an on demand stored [auto-enter] calc field. If I need to do an update on >1 record, that runs as a PSoS, for performance.