Over 46,000+ Business Solution Developers Find answers, ask questions, and connect with our community of business solutions developers, business owners and partners.
There are a number of questions you should ask yourself before building a complex calculation that involves multiple functions, relationships and fields.
What functions will you need?
If you are using a text function, you might want to read up on the other text functions. It’s possible that it may change your ideas about what you can do and make your FileMaker solutions that much better.
What fields are needed and where are they?
When you are in the middle of building a complex calculation … it is NOT the time to realize you forgot to create a needed field. So take a moment to think about the calculation you are about to create, see what fields and relationships you my need before you enter the calculation dialog box.
FYI… With the newer versions of FileMaker, you can create new relationships on the fly. When in the define calculation dialog box, look at the bottom of the pull down list for fields, you can see where you can define a new relationship on the fly!
What Is The Result Type? ( text, number, date, time or container )
This may be more of something you should think about before you close the calculation dialog box because it is definitely something that will throw your calculation awry. It’s quite frustrating to finish a calculation, wait for it to recalculate through all the records in the file … only to realize you forgot to specify the result type.
Does The Calculation Need To Be Indexed (stored)?
If you use a status function, you may want to make sure the calculation is set to “Do Not Store Calculation Results – Calculate Only With Needed.” On the other end, if it is a key field or a field that is used in Finds or Sorts … you may want to make sure the field is indexed. This is done via the “Storage Options” button in the lower right corner of the define calculation dialog box.
Did you forget a to use a conversion function?
This is another one of those “Aw Nuts” mistakes that occurs to you after you look at the result of a newly created calculation. When you are dealing with information that is different than the result, you very well may need to wrap one of the following functions around a field to get the desired result …
GetAsText – The GetAsText function looks at information that is stored in another format and converts it to text. When converted to text, then you can manipulate the text string with any of the text functions like left, right, middle, position or patterncount ( to name just a few ).
GetAsDate – The GetAsDate function looks at information that is stored in another format and converts it to an acceptable date. When converted to date, then you can manipulate the value with any of the date functions. It is possible that the data string is formatted that FileMaker cannot convert it accurately to a date. Usually this comes with someone enters in an incomplete date string without the day, month or year information.
GetAsTime – The GetAsTime function looks at a text string and ( if the text is formatted correctly ) return the text string as time related data. As time related data, you can apply any of the time related functions.
Finally, if you are using FileMaker Advanced, you might always think about custom functions. If this calculation or parts of it are used many times, you might want to make a custom function out of it. That way you can recycle the code many times and any updates to it can be done in one central place!