Flexible calculation from formula records

 

This weekends post is just a quick article on using Filemaker’s ‘Evaluate’ command in a recursive
function that performs multiple calculations in sequence.

This is quite useful if you want to allow the user to calculate different pricing models, for example.
Let’s assume you want to increase the purchase price by 10 percent and give 5 euros discount.
Finally we add the value added tax of 19 percent.

We build the solution with 2 tables:
One table to calculate the result and a second table with the individual formulas in order of execution.

Table 1:
UUID (Text, Auto-enter Calculation) to assign the formulas in table 2.
In (number) Base value
Out (Calculation, Unstored) with the custom function

Table2:
UUID (Text, Indexed From Table1)
Sort (Number) Sorting for relation of tables
Formula (Text)

We connect the two tables using a write relationship of the two UUID fields.
The relationship is sorted by the Table2::Sort field.

The custom function looks like this:

cf_calc

// cf_calc ( calc ; value ; counter )

Let ( 
[ 
  max = ValueCount ( calc ) ; 
  line = GetValue ( calc ; If ( counter = "" or counter = 0 ; 1 ; counter ) ) ; 
  expression = If ( PatternCount ( Upper ( line ) ; "X" ) > 0 ; line ; 0 ) 
] ; 
  Case(
    max = 0 ; value ;
    counter < max; cf_calc ( calc ; Evaluate ( Substitute ( Upper ( expression )  ; 
      ["X" ; value] ) ) ; counter +1 ) ;
    counter = max; Evaluate ( Substitute ( Upper ( expression )  ; 
      ["X" ; value] ) ) ;
    ""
	)
)

The custom function uses ‘X’ as a placeholder for the calculation variable.

We now create the following 3 records in Table2:

Sort = 1
Formula = X + ( X * 10 / 100 )

 

Sort = 2
Formula = X – 5

 

Sort = 3
Formula = X + ( X * 19 / 100 )

 

In Table1 we put the calculation in the ‘Out’ field:

cf_calc ( List ( Formula::Formula ) ; In ; 1 )

 

That is all we need to do.

The user can now flexibly add as many formulas as he wants to create his calculation.

I have written another variation of the custom function that shows in detail each result of the calculations:

cf_calc_verbose

// cf_calc_verbose ( calc ; value ; counter )

Let ( 
[ 
  max = ValueCount ( calc ) ; 
  line = GetValue ( calc ; If ( counter = "" or counter = 0 ; 1 ; counter ) ) ; 
  expression = If ( PatternCount ( Upper ( line ) ; "X" ) > 0 ; line ; 0 ) 
] ; 
  Case(
    max = 0 ; value ;
    counter < max; "= " & Evaluate ( Substitute ( Upper ( expression )  ; 
      ["X" ; value] ) ) & ¶ & 
      cf_calc_verbose ( calc ; Evaluate ( Substitute ( Upper ( expression )  ; 
      ["X" ; value] ) ) ; counter +1 ) ;
    counter = max; "= " & TextStyleAdd ( Evaluate ( Substitute ( Upper ( expression )  ; 
      ["X" ; value] ) ) ; Bold + DoubleUnderline )  ; 
      ["X" ; value] ) ) ;
    ""
	)
)

The result in this case looks like this:

= 110
= 105
= 124,95

 

The sample file will look like this:

 

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.