Make as few calls to database fields as possible!
When using fields in calculations, each time the value is retrieved, FileMaker has to read from disk in order to get the information. Even if you use the same field twice in a row, FileMaker will read that field twice from the disk!
Here are some examples where you could really speed things up.
// Calculation 1
Case (
table_1::field_1 < 1; "green";
table_1::field_1 < 10; "red";
table_1::field_1 < 100; "blue";
"orange"
)
// End Calculation 1
In this case, multiple comparisons to table_1::field_1 are being made. Each time it compares the field to a value, it is retrieving it from hard disk. Instead, set a variable to the contents of the field, and then compare the variable to the values:
// Calculation 1 Revision
Let ( $table1_field1 = table_1::field_1 ;
Case (
$table1_field1 < 1 ; "green" ;
$table1_field1 < 10 ; "red" ;
$table1_field1 < 100 ; "blue" ;
"orange"
)
)
// End Calculation 1 Revision
This places the contents of the field into memory for fast access, and on average may speed up the calculation 2x!
// Calculation 2
If ( table_1::field_1 = "animal" or table_1::field_2 = "dog" or table_1::field_3 = "labrador" ; 1 ; 0 )
// End Calculation 2
In this one, three separate fields are being compared with the "or" operator. The calculation will always retrieve values from all three fields even if the first condition is met. Rewrite this as a case statement:
// Calculation 2 Revision
Case (
table_1::field_1 = "animal" ; 1 ;
table_1::field_2 = "dog" ; 1 ;
table_1::field_3 = "labrador" ; 1 ;
0
)
// End Calculation 2 Revision
This will give the same results; however, if field_1 does equal "animal", the next two comparisons will not be made, and will save time by not retrieving the additional fields.
// Calculation 3
Case (
IsEmpty(table_1::field_1) ; 1 ;
IsEmpty(table_1::field_2) ; 1 ;
IsEmpty($var1) ; 1 ;
IsEmpty($var2) ; 1 ;
0
)
// End Calculation 3
Here we have the same result (1) given by all of these cases. You would want to place the conditions with variables first because these will calculate much faster than the fields:
// Calculation 3 Revision
Case (
IsEmpty($var1) ; 1 ;
IsEmpty($var2) ; 1 ;
IsEmpty(table_1::field_1) ; 1 ;
IsEmpty(table_1::field_2) ; 1 ;
0
)
// End Calculation 3 Revision
This way, if conditions 1 or 2 are met, the calculation does not need to retrieve any fields at all!
// Script 1
Set Variable $$concat = "";
Set Variable $x = 1;
Set Variable $count = Count(Relationship_1::Field_1);
Loop
Exit Loop If $x > $count;
Set Variable $$concat = $$concat & GetNthRecord(Relationship_1::Field_1; $x);
Set Variable $x = $x + 1;
End Loop
// End Script 1
In this script, $$concat is a concatenated string of all values in the relationship Relationship_1::Field_1. In this case, you could make a single call to the database using the List command rather than retrieving the contents from each individual record:
// Script 1 Revision
Set Variable $$concat = “”;
Set Variable $field_values = List(Relationship_1::Field_1);
Set Variable $x = 1;
Set Variable $count = ValueCount($field_values);
Loop
Exit Loop If $x > $count;
Set Variable $$concat = $$concat & GetValue($field_values; $x);
Set Variable $x = $x + 1;
End Loop
// End Script 1 Revision
Caution #1: When using the List command, if the field contents of a record is blank, the value will not be included in the returned list. In this particular case, since concatenating a blank value would result in the same string, the results will be the same either way.
Caution #2: If you know that the field could contain return characters, do not use List (unless you really know what you’re doing!). The List function actually works best with serial number fields or fields that you know contain one line of data.
318 on Facebook
Join the 318 group on LinkedIn
Join the 318 group on Plaxo Pulse
The 318 MySpace Page