I recently ran into a problem with a script that was exiting a loop prematurely. The script was using an [Exit Loop If] step that compared two variables; one ( $$Count ) that was being incremented by 1 on each loop iteration and a second ( $$Max ) that was set previously in the script that represented the maximum value which, upon being reached by the incrementing value, should cause the script to exit the loop. Something like this: Loop … Exit Loop If [ $$Count > $$Max ] Set Variable [ $$Count; $$Count + 1 ] End Loop In this case $$Max was set to something like 750. I found when $$Count reached a value of 8 the script would exit the loop. In other words, 8 > 750 = TRUE ??? Of course, you, being the keen FileMaker developer that you are, immediately realize that this is because that expression is being evaluated as text where 8 IS considered higher than 750 even though both values appear to be numbers. As it turned out the reason behind this was the fact that earlier in the script the $$Max variable had been set as the result of a calculation that returned a text data type. It had been parsed from text using something like: Left ( “750values”; 3 ) The result was “750”, but it was stored as a text value. The take-away from all this is fairly simple. Know your variable data types. Pay attention to how variables are being created so you know how they will behave down the line. FileMaker documentation addresses this minimally with the following note regarding the use of variables: “The data type of a variable is determined dynamically based on the assigned data. The default data type is text.” https://www.filemaker.com/help/15/fmp/en/index.html#page/FMP_Help/using-variables.html To get a little more clarity I started experimenting to see exactly how variable data types behave. Here are a couple things I found. ================================================= If a variable is set by a calculation function its data type is determined by the output of the function being used. ================================================= Example 1 using [Set Variable] and a number function Set Variable [ $$Variable; Div ( 50; 2 ) ] Result: $$Variable = 25 $$Variable is a number Example 2 within a Let statement using a text function Let ([ $$Variable = Left ( “25hh08y”; 2 ) ]; … ) Result: $$Variable = 25 $$Variable is text This behavior remains consistent when the calculation is being used as a field calculation regardless of the selected “Result Type” from within the field options. There is an exception to this however. Example 3 using a calculation field Say you have a calculation field using the following calculation, but with the result type set to “Text”: Let ([ $$Variable = Div ( 50; 2 ) ]; $$Variable ) In this case $$Variable would be stored as a number, BUT if you were to invoke the value of the field itself the result, while still being “25”, would be treated as text. ================================================= Even if a variable value containing only numbers is being stored as text you can still use it in most arithmetic functions. ================================================= In example 2 above, all of the following would be TRUE:
- $$Variable + 50 = 75
- Factorial ( $$Variable; 2 ) = 600
- Log ( $$Variable ) = 1.3979400086720376
- $$Variable > 100
You can see from the last example in this list that FileMaker is storing the value as text. Despite this you can still do accurate math with it using virtually all arithmetic functions. I say “virtually” because I did not test all of them. I did test all ‘Number’ functions and this was the case with all of them. Keep in mind that there may be some other functions that behave differently. In all my experimenting the only time calculations produced unexpected results was when a function used some form of greater than ( > ) or less than ( < ). As was the case in my looping script. Hopefully this saves you a couple grey hairs. Jeremy Upton is a FileMaker 15 Certified Developer at Skeleton Key. About Skeleton Key Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.