Best practices for triggering design sheet equations to recalculate


#1

What is your method of choice for programmatically triggering the value of a cell to recalculate based on an equation in the design sheet? I feel like I must be missing something obvious.

We have a database in which we’re using procedure-style programming (we run into this same issue with our other databases in spreadsheet mode, too). So as to not get bogged down in details, I’m going to modify an example from the manual. I have the fields “total”, “A”, “B”, and “C”. A, B, and C get updated using formulafill, grabbing numbers from other databases. The design sheet equation for “total” is “=A+B+C”. Three methods I’ve looked into:

A) After changing the values in A, B, and/or C, I want to trigger “total” to update. Our current method is to run a little macro:

Field ("total")
firstrecord
loop
    copycell
    pastecell
    downrecord
until info("stopped")

Copy/paste is the only way we could figure out to trigger the value to update. I don’t love this solution, mainly because it uses the clipboard, and I have to remember not to copy and paste anything in other applications while I’m waiting for the macro to run. This macro takes 5 minutes to run through 1133 records in a shared database.

B) I’ve tried formulafilling “total” with “A+B+C”, but don’t really like this solution because it means the equation is in the design sheet as well as in a macro, and I have to remember to update it in both places when it changes. Takes 4.5 minutes to run.

C) Automatic Calculations Wizard. Doesn’t work for procedure mode formulas, also I don’t think it could be triggered by a macro, so it wouldn’t be ideal even if we could switch modes, which we can’t, because procedure mode is needed for other fields.

I’m seeking other options that don’t use the clipboard. Bonus points if there’s a speed improvement.


#2

FormulaFill is bound to be the fastest way. Rather than put a formula in the design sheet, you could put a procedure call. That procedure could be responsible for calculating the formula for a single record, or for all the records, based on how it was triggered.

After your main procedure updates A, B, and C, it would use SetTrigger to set the trigger to something like “Fill” and then the procedure that is called could have something like

if info("trigger") contains "Fill"
    Field Total
    FormulaFill A+B+C
else
    Total = A+B+C
endif

That would put both formulas within two lines of one another.

In this loop, copycell and pastecell could be replaced by a single command. Cell «»

The cell command will put a value into the current cell, and it will trigger any automatic formula associated with that field. It will not, however, trigger an automatic procedure.


#3

FYI - The Automatic Calculation Wizard actually uses an undocumented method to find out the formula and then uses formulafill with that formula. It won’t help you currently, but Panorama X has a documented method to find out the formula for a field.


#4

Thank you! This is an easy replacement in my code. Your first suggestion is good too, but I’m reluctant to take equations out of the design sheet for job scope reasons… We have one person whose domain includes managing the design sheet equations, and she also dabbles in macros but I think it’s handy to have the equations all plainly visible in the design sheet.