Hello there members of TechNet,

As some of you may (or may not) have seen, I will be doing a session at this year's DevCon entitled "Calculations: TechNet Special". We ran a poll (https://fmdev.filemaker.com/polls/1082) and it helped me formulate an idea of what I should cover.

I'm going to be talking about Calculation performance, and how using Case, Let, Evaluate, and ExecuteSQL can effect performance. I have a few good examples that I've collected over the years, but since this is supposed to be a TechNet special, I figured I'd reach out to the community at large for more examples.

I'm looking for two things:

1. Examples of calculations that you've optimized yourself and/or lessons you've learned over the years. It would be great if you had the before or after calculations, or the technique you're using to help with performance (script triggers, scheduled scripts, etc...). I'll already be covering examples like using Let to only calculate a segment once and using Case to short circuit a calculation, so if you have good examples of calculations you've fixed with those techniques, I'd love to see them.

2. Examples of calculations that you're currently having issues with and would like me to review. I can't promise that I'll provide insight to everyone that replies, but I'll try to give a glance at every example that is sent my way and help if I can. And if it works really well, then you will see your stuff promoted at DevCon during my session.

Thanks in advance to anyone that replies. I hope that we can all learn a little bit together. See you all at DevCon,

Matthew O'Dell

Technical Marketing Manager

FileMaker

1. the use of zero and one as boolean flags

2. writing formulae which meet all conditions without the need for if statements.

The important rules for zero and one are:

x ^ 1 = x

x ^ 0 = 1

x * 1 = x

x * 0 = 0

Use the power operator if the data is used for multiplication or division:

old: if ( discount allowed = "Yes" and not isempty(discount) ; discount; 1 )

new (%): discount ^ discount allowed

An example.

Let (

[

tax = (1 + tax rate) ^ tax status ;

discount = ((1 - discount rate) ^ discount allowed)

];

Price * tax * discount

)

Use the multiplication operator when performing addition and subtraction or when positive and negative values need to be preserved.

old : IF( discount allowed = "TRUE" ; discount amount ; 0 )

new ($): discount amount * discount allowed

An example.

Let (

[

discount = discount amount * discount allowed

];

( Price * tax ) - discount

)

I know it looks simple but it provides the ability to place a field into any calculation. The boolean value controls the action of the operator and I know that the formula is safe and valid for all values.

It has an additional benefit because there is no logical fork. In these examples the fork is simply one value or another. In real life the forked calculations may become complex and may be nested.

Malcolm