Serial calculation Editor

While cleaning up my Filemaker Tools folder, I stumbled across a custom function that is a bit older,
but is actually too bad not to be described briefly here.

I remember that during the presentation of a Filemaker ERP the speaker showed at one point that
the solution included a formula editor for calculating sales prices of items. The distribution employee
could enter calculations (markups and markdowns or discounts) and the system immediately calculated
the sales price.

After the event, I immediately set out to write such an editor myself. And actually the whole thing is
based on a simple custom function with an “EVALUATE” command and my favorite command “WHILE” !

So, let’s code !

For our example we use 2 tables, first the “Main” table for the inital value “X” of type number and the
calculated result and second the table “Calculations” with (as not hard to guess) the calculations to be
done with the initial value.

Table “Main”:
UUID [Number Auto-enter calculation Get (UUIDNumber)]
X [Number]
Result [Calculation with the custom function]
Result verbose [Calculation with the custom function that outputs the calculations line by line here].

 

Table “Calculations”:
UUID_Main [Number indexed for the relationship between Main and Calculations]
Sort [Number indexed to calculate the Calculations in an order].
Calculation [Text – the actual formula with the placeholder “X” for the placeholder in the formula].

Now we have everything together to use a custom function to perform the individual calculations one
after the other in the order of sorting.

// value = inital value at start
// calcList = List of calculations with placeholder X
// verbose = Returns a line for each calculation
// verbosePrefix = Prefix of every line in verbose mode
// verbosePostfix = Postfix of every line in verbose mode

While ( 
[ 
	calcList = calcList ; 
	value = value ; 
	valueVerbose = "" ; 
	max = ValueCount ( calcList ) ; 
	i = 1 
] ; 

i ≤ max ; 

[ 
	line = GetValue ( calcList ; i ) ; 
	expression = If ( PatternCount ( Upper ( line ) ; "X" ) > 0 ; line ; 0 ) ; 
	valueVerbose = List ( valueVerbose ; verbosePrefix & 
	GetAsNumber ( Evaluate ( Substitute ( Upper ( expression )  ; ["X" ; value] ) ) ) & verbosePostfix) ; 
	value = GetAsNumber ( Evaluate ( Substitute ( Upper ( expression )  ; ["X" ; value] ) ) ) ; 
	i = i + 1 
] ; 

If ( verbose ≠ "" ; valueverbose ; value )

)

It is called with the list of calculations in the correct order.

The custom function executes a WHILE function (the details of a WHILE function please read in the
article “WHILE“) in the actually only a more complicated row:

Evaluate ( Substitute ( Upper ( expression ) ; ["X" ; value] ) )

In this line the placeholder X (you can easily replace it with another one) is replaced by the current value
(this changes with every pass of the WHILE function) and then calculated from a string with the Filemaker
function “EVALUATE”.

That is actually already everything.
Here I have added a verbose mode, which can be switched on as a parameter of the cf and then returns
the individual results as lines (optionally with a prefix and/or postfix).

If we put all this into a sample file, the result looks like this with a simple layout:

If you like to have an open version of the file, just send me an email.
You have any questions? Feel free to send me an email, I’ll try to answer as soon as possible.