Search Project Mgmt
Search FileMaker Blogs

Thank you for visiting the FileMaker Calculations Explored blog. I recently moved this content over from my blogger account. Hope you like it! When you get a chance, check out the centralized search feature for all the FileMaker blogs found along the right side panel. It is quite handy!

Thursday
Aug142008

Sub-Expressions

Sub-Expressions

CATEGORY: Calculations
CLASS: Basics
VERSION: All Current Versions

Generally, an expression is viewed as the input of a calculation such as 1+1. Now you can have what can be called a sub-expression of an expression, if that input is within parentheses (the operator name is called precedence). So our 1+1 could be called a sub-expression when utilized as 4 * (1+1).

EXPRESSION: 4 * (1+1)
SUB-EXPRESSION: (1+1) or the overall expression 4 * (1+1)

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2010 - Dwayne Wright - dwaynewright.com

Saturday
Jul262008

Cascading Calculations

A Cascading Calculation, more often referred to as a nested calculation, is when a calculated value has one or more other calculations within it. This dependancy can be a factor when you change or delete a calculation and it is part of another calculations cascade. FileMaker doesn’t allow you to delete a calculation in this situation but it can be edited.

Creating a bunch of smaller calculations can be a bit redundant but it is more flexible (particularly in troubleshooting and copying/pasting code between tables). However, when taken to the extreme, having a large cascade of calculations that refer to other calculations ... that refer to other calculations ... (and so forth), can put a strain on your FileMaker databases performance.

Some developers are adopting the mindset of using startup variables instead of calculations in places such as this. So if they know that a calculated value is going to be used in many places, they use the variable method to toss calculated values up into memory instead of creating a separate calculation field.

There are advantages to this and there are disadvantages. Variables can be more flexible and don’t have the schema overhead of a calculation field. However, FileMaker will allow you to clear / delete a variable regardless of how you have decided to use it. So this can fall into the category of “with great power comes great responsibility”.

Tuesday
Jul152008

FILEMAKER: If you ever need a country letter code...

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

A new custom function showed up with this week from Edward Souza, Duana A. S. Designs on the Custom Function library at briandunning.com. This custom function provides a list of countries (sorted partially by their first letter) and their equivalent letter code.

Got to love the simple but extremely effective custom functions out there. This custom function will appear in my 2.5 release of InBizness, I even added the country code fields in which to use them. I also took the raw data of the country list and made them into a value list for my country fields for shipping and billing client address information. Thanks Edward!

Here is a link to the custom function...
http://www.briandunning.com/cf/879

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2010 - Dwayne Wright - dwaynewright.com

Tuesday
Jul152008

FILEMAKER: Rounding To The Half

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

Rounding To The Half HalfRound.FP7
Under the hood, there is no way to round a number to the half. However, using a calculation with a CASE and the INTEGER number function, you can.

Case(
(Number Example - Int(Number Example)) ≥ .75, Int(Number Example) + 1,
(Number Example - Int(Number Example)) ≥ .25, Int(Number Example) + .5,
Int(Number Example))

So what we are doing it taking an entered number and subtracting it’s INTEGER result. This leaves us with a fraction residue. We look at this residue, depending on it’s value, we add, subtract or do nothing to the integer result.

An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles08/HalfRound.zip

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

====================== ADVERTISEMENT ==============================
For more information on the InBizness SOHO and other quality FileMaker framework solutions, please visit http://www.dwaynewright.com/solutions.html

Friday
Jun272008

A READER ASKS: Date Warning Calculation

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

A READER ASKS
I need to create a calculation for creating a warning 6 months before a warranty expiry date is due. I have a field for the date of warranty expiry and have a merged field as the trigger for indicating the warning I just can't create the calculation to trigger the warning. Can you advise

-------
DWAYNE RESPONDS
Well, lets look at what our needed list of items before we start. We are going to need to b be able to capture the current date and compare it to the warranty date plus six months. Then we are going to need a warning field for when that date is passed. We are going to also need a way to turn the warning off or we will be in warning overload. After that, you might want to have some sort of scripted warning system.

BUILDING THE WARNING
Our warning field is going to need to deduct six months from the warranty date. For that, we are going to use the Date function and within that we will be using the month, day and year functions. The Date function allows you to take 3 parameters ( which can be a field, a calculated value, literal text or a combination thereof ) and make a proper date out of them. So we are going to subtract 6 from the month parameter and leave the others the same. Your three parameters are for the month, the day and the year.

So our calculation is going to look something like ... Date ( month ; day ; year )

will become

Date ( month(warrantydate) - 6 ; day(warrantydate) ; year(warrantydate) )

Here are some links to other posts that might be of interest in regards to this topic...
The Date Function
The Day Function
The Month Function
The Year Function

So now we can simply compare if the warning date is greater than or equal to the current date ... and ... our bypass field is not checked. Our bypass field can be used to say we have already sent the reminder, the warranty was extended, the warranty date has passed, etc... So that calculation may look something like ...

Case(
WarningBypass = "Yes", "N/A"
WarningCalculation ≥ Get(CurrentDate), "Warning",
"")

Here we test to see if the bypass is active and put a N/A in our warning field and the calculation stops. Otherwise we evaluate the next line in the Case statement to see if our warning calculation is greater than or equal to the current date. If this is the case, then our field will display the text of “warning”. If our bypass is not active and the warning date is still in the future, the field is empty.

SCRIPT TRIGGERS
Well, you can do any number of automatic, semi-automatic, manual and visual indicator processes from here. To have a script fire automatically, you will need a third party plug-in to enable FileMaker to fire off a script on its own. You can have a script that pull up the warning related records as part of the startup or shutdown process. Of course, you can have a regular button run the script as well. As far as visual indicators, conditional formatting can be use to alert the user of the impending warranty date condition.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

====================== ADVERTISEMENT ==============================
For more information on the Virtual One On One Training, please visit http://www.dwaynewright.com/training.html
===================================================================