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!

Tuesday
Jun242008

FILEMAKER: Inserting Commas In Numbers Within Text Calculations

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

Added the custom function of insert_commas to my InBizness product and thought I’d mention it here (just in case someone else is looking for something like this).


This nifty function inserts commas before every third digit before a decimal point. Uses recursion to handle any number, including negative values and decimals. This allows me to have a calculated number in a text calculation field and still show the commas. So a calculated value of 2,000 will look that way instead of 2000.

The name of the function is insert_commas and I got it from the briandunning.com web site. The credits for uploading this function to that site are noted as Matthew Stetson, Antidote Solutions

The direct link to this custom function is ...

http://www.briandunning.com/cf/483
=
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 InBizness Money including screen shots and tutorial movies, please visit http://www.dwaynewright.com/solutions.html
====================================

Tuesday
Jun172008

FILEMAKER: A READER ASKS: struggling with "Case" and "if" calculations

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

A READER ASKS

I have been struggling with "Case" and "if" calculations.

I am at a business where we schedule 200 people. We want our schedule to
show the breaks that the people will need to take.

I am trying to do something like the following (where "Shift Hours" is
already a successful calculation itself)...

If Shift Hours is less than 4 return 0.

If Shift Hours is greater than or equal to 4 AND less than or equal to 6
return 15.

If Shift Hours is greater than 6 AND less than or equal to 8.5 return 15,30.

If Shift Hours is none of the above, return ?

-------
DWAYNE RESPONDS
One thing that might help is that a CASE function will evaluate each case step from the top going down and the first condition it comes across that has a TRUE result, will return the associated matching value. In your description, you are going from the bottom to the top (meaning you have to look for a minimum and maximum range). If you flip it, going from top to bottom, you only need to have greater than comparisons.

In in top down method, you simply have to say ...
are you at least this ,
or are you at least this,
or are you at least this,
ok then you are that obviously this.

So going from top down, the calculation could look something like ...

Case(
shift hours > 8.5, "greatest value", (which is greater than 8.5)
shift hours > 6, "next greatest value", (which is between 6 and 8.5)
shift hours > 4, "next greatest value", (which is between 4 and 6)
shift hours > 0, "next greatest value", (which is between 0 and 4)
"")

By using this method, your upper limit is automatically handled because your case statement would never get to that line, if that is TRUE.

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

© 2010 - Dwayne Wright - dwaynewright.com

Tuesday
Jun172008

FILEMAKER: Break Long And Complex Calculations Into Smaller Pieces

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

If possible, it is not a bad idea to break up a long calculation field into a collection of smaller calculation fields with one large master calculation field that binds them all together. One big advantage of this technique is that you can test and, if needed, troubleshoot your calculation much easier.

On occasion, I’ve even gone so far as to create fields or a quick Data Viewer watch item for each function, just to make sure it was not the problem. This is a very productive troubleshooting technique and I recommend it highly.

When everything works fine, you can always go back later and combine all the calculations into your master field.

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

© 2010 - Dwayne Wright - dwaynewright.com

Monday
Jun092008

FILEMAKER: A READER ASKS: Incrementing Letters Via Auto Enter

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

A READER ASKS
I am currently learning on FileMaker Pro 9 and I encounter this problem:

I would like to have text results for my auto-enter fields. For example: AA, AB, AC (increment by the alphabetical order). Pls advice on how to do the calculation in order to get this result.

Hope to hear from you soon. Thank you

-------
DWAYNE RESPONDS
Well, you obviously are going to need to craft something that reads the last character and intelligently increments it to the next letter of the alphabet until it reaches the letter Z. Then you will need to add a new digit with A starting at the end.

It would take me a lot of time to try and cook something up using traditional FileMaker. I normally don’t do something like this, if I don’t have to. So if you were looking for an excuse to upgrade to FileMaker Advanced, I might have some ammunition for you.

My knee jerk reaction was to pop over to briandunning.com and see if there is a custom function for something like this in his sites library. In about 2 minutes, I found one by doing a search for the word increment. The custom function I found was ...

NextLetter ( ExtractedLetter )
Steve Gerow, Abrazos Data Consulting, Inc.
http://www.abrazosdata.com
Increments letter sequence A - Z.

The link to this custom function is at http://www.briandunning.com/cf/718

Custom functions is just one of the very small ways FileMaker Advanced makes your development life better and I cannot recommend it (or the custom function library at www.briandunning.com) enough.

Cheers!
Dwayne

Thursday
Jun052008

FILEMAKER: Save Troubleshooting Time By Duplicating, Copying And Pasting

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

You do not have to create each calculation you need from scratch. One thing I do quite often is duplicate an existing calculation field (if the new calculation is going to be in the same table) or copy/paste the calculation field using FileMaker Advanced. Then I rename the field and modify just the parts that I need to get my new result.

Say that you have successfully built a calculation field. You now realize you need a second calculation field that is almost the same, except for a small change. You can save a lot of time by recycling the first field, changing the fields name, editing the calculation to fit your needs ... and then it is off to the ball park to enjoy an afternoon game.

Perhaps that isn’t a success that merits taking the rest of the day off. You can also highlight a calculation expression in the calculation dialog box to copy the command to the computers clipboard. This means you can paste the calculation into other calculation dialog boxes used in field validations, auto entry, script steps or other calculation fields. You can do this in the same FileMaker file you copied the calculation from or to a different FileMaker file

I've heard that some developers even keep a library of their most sophisticated calculations handy for that rainy day project.

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

© 2010 - Dwayne Wright - dwaynewright.com