Thank you for visiting the FileMaker Thoughts 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
Apr082014

Some cool text formatting custom FileMaker functions

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Recently, I wanted to have a listing of the cool text formatting functions for a project I was working on. The super custom function site at briandunning.com doesn’t have much in the way of organization options and its search capabilities are pretty basic. So I made a listing myself and then thought I might as well share the list to you via a blog. So here you go!

Dwayne

-----

ActualLineCount ( Text ; CharsPerLine )
Jonathan Stark, Jonathan Stark Consulting - http://jonathanstark.com/
Determine if text is overflowing field bounds on layout.

http://www.briandunning.com/cf/802

---------------------

CaseStatus ( theText )
Michael Pease, KPM
Determines if text is all upper case, all lower case, or mixed case (ignoring non a-Z chars)

http://www.briandunning.com/cf/848

================

GetFontFamily( text )
Joe Gold, Angel City Data - http://www.angelcitydata.com
Returns the font of a text

http://www.briandunning.com/cf/633

==================

GetFontStyle( text )
Joe Gold, Angel City Data - http://www.angelcitydata.com
returns style of a text

http://www.briandunning.com/cf/636

====================

GetStyledWords ( text ; style )
Daniele Raybaudi, ACI - www.aci.it
Returns all the styled words ( in list view ) within the given text

http://www.briandunning.com/cf/664

======================

RemoveDuplicates ( Text )
Andrew Persons, Excelisys - http://www.excelisys.com
Removes duplicate values from a value list.

http://www.briandunning.com/cf/492

=========================
Supertrim ( text )
Debi Fuchs, Aptworks Consulting - http://www.aptworks.com
Speedily (and with no recursion) remove leading and trailing white space (including spacing, tabs and returns) from a text string.

http://www.briandunning.com/cf/904

===========================
Trim4 ( text )
Ray Cologon, NightWing Enterprises - http://www.nightwing.com.au/FileMaker
Removes *all* leading and trailing white space from the supplied text, including tabs, carriage returns, spaces and non-breaking spaces.

http://www.briandunning.com/cf/166

===========================
=
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.

Tuesday
Apr082014

FileMaker Example File: Find Month Via Calculation

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

There are times you may want to see a found set of records for the past month, a month in the future or the current month. This isn’t something you will find straight out of the FileMaker box but you can script it easily enough. In this example, we have two buttons that will find all the records for the previous month and for one month in the future. Really, we have 4 buttons but they do the same thing using different methods. We will cover that a little later.


A big part of this example uses the family of date functions. So if you are not familiar with them, you might need to do a little research first. Also, we do run a script when the example file starts up that creates records with dates that will match our searches. So don’t let this script throw you off.

The next thing we did was create a calculation field that will build what you would type in for a range of dates. You know, the string of a start date and then three periods ( ... ) and following up with the end date. So a text string of 12/1/2003...12/31/2003 would be used to find all the record dates in the month of December of 2003. The calculations are as follows ...

Past month
GetAsText(Date(Month(Get(CurrentDate))-1; 1; Year(Get(CurrentDate))))
& "..." &
GetAsText(Date(Month(Get(CurrentDate)); 1; Year(Get(CurrentDate)))-1)

Future month
GetAsText(Date(Month(Get(CurrentDate))+1; 1; Year(Get(CurrentDate))))
& "..." &
GetAsText(Date(Month(Get(CurrentDate))+2; 1; Year(Get(CurrentDate)))-1)

Both calculations use the date function of Date(month, day, year ) and some simple math. To get the first day of last month, we simply subtract one from the month of the current date and make the day the 1st. We do basically the same thing to get the last date. We calculate the first day of the current month and subtract 1. That will give us the last day of last month. The future dates use the same setup except we are careful to make sure our calculated month has a 1 added to it. So we take those two calculated dates and concatenate them together with a text string of three periods in the middle.

So we all we need to do is make the script go into find mode, paste in the string we want to search with and then perform the find. We wrote two different methods for doing this.

The first set of scripts use those calculation fields. They copy the results of the calculation while in browse mode, switches to find mode, pastes the data into the date field and performs the find.

The second set of scripts does not use a copy and paste action. This may be a superior setup because copy requires the field you are copying from to be on the current layout. If we were to delete those calculation fields from the layout, the first set of scripts would break. In the second set, we don’t need the calculation fields at all. We are using the Insert Calculated Result script step. So the script is doing the calculation and separate calculation fields are not necessary.

An example file can be downloaded by clicking (here).
=
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.

Monday
Apr072014

The PERFORM FIND FileMaker script step

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

On the most fundamental level, this step goes into find mode, enters in find criteria (new user entered criteria or restored find criteria) and performs the find. This step can be used in conjunction with the Enter Find Mode script step or independently. When it is used with the Enter Find Mode step, it executes the find parameters after the Enter Find Mode Pause. On it's own, it can be used to perform an entire saved FIND command.

The one option for this script step is to specify the find parameters. Here you can manually select the find settings you want to perform. Many users know about how the recent find is automatically shown here when you add a new script. If not, let me tell you that you can perform a find you want to say manually. Add the Perform Find script step to the script you want to have it, click the specify find request button and the setting for the find you just did will automatically be there. You can save the setting as is or tweak it as necessary.

HOW PERFORM FIND IS USED
Usually the script will look something like...

Go to the layout that has the key fields you want to search with
Enter Find Mode
Place Find Specifics in the Key Fields
Perform Find

Optionally (some would argue this is not an option) the scripter may have the script do some error trapping for the possibility of no records found .

An example of how this type of script would look like would be ...
Set Error Capture On
Go to Layout (that has the key searching fields)
Enter Find Mode
Paste in the Find Specifics in the Key Fields
Perform Find
If(StatusCurrentError = 401)
Show Message "No matching records found!"
Perform an error reaction subscript
Else
Go To Layout (the one you want to display)
Print

While we are here, lets elaborate about ScriptMakers Error Capture.

FileMaker, like almost any application, will give you an error message when an error occurs. Usually, this is a good thing and you can then go on about your business. Sometimes though, you would rather have the database react in a customized manner when a common error occurs. Usually the Set Error Capture step is used in conjunction with an IF Statement. If a particular error comes up, immediately perform a desired task. There are a number of reasons you want to do this but they usually fall into the two main categories of the user experience and automating processes.

The user experience misfire is when you don't want to confuse a typical user with an error message they probably won't understand. The set error capture and braching If script steps allow the developer the opportunity to present the user with an alternative error message and perhaps branch the script in a designed direction.

The FileMaker error message of "No Records Found" can toss a lot of beginning FileMaker users for a loop. If you trap for this error, you can control what the user can do after a search comes up fruitless. Generally, a developer should try to put the Set Error Capture script step as close as possible to the top of the script. However, it is possible to have Set Error Capture On and Set Error Capture Off sprinkled through out a complicated script in order to selectively add error capture throughout the script.

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

© 2007 - 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.

Monday
Apr072014

Nesting FileMaker Expressions

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

You don’t hear the term expression used that much in FileMaker discussions but its generally accepted meaning is calculation content that returns a single value. To make a complex easier to manage, you might include one expression within another expression (considered nesting the expressions).

Here you can see some Russian nested dolls, also known as matryoshka. Each doll is a single unit inside of a larger unit.

BASIC EXPRESSION
1 + 3 * 2 = 8

NESTED EXPRESSION

(1 + 3) * 2 = 8, the (1 + 3) expression is nested within the main expression

1 + (3 * 2) = 7, the (3 * 2) expression is nested within the main expression

The calculation content between the parenthesis returns a single value and then used to determine another single value outside. The setup of the nested expression can alter the final result of the calculation.

An expression can contain full array of options such as numbers, literal text, fields and functions. A CASE function is a single expression, although it can have many possible returned values. An IF function inside of a case function could be considered a nested expression because it would return a single value, that in turn, is returned to support a calculated single value above it.

Monday
Apr072014

A READER ASKS: New Field That Combines Two Existing Fields

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A READER ASKS: (actually it was a twitter post)
Trying to figure out how to create new field that combines two existing fields in FileMaker Pro 9. Anyone know how?

-------
DWAYNE RESPONDS
Actually, it is quite easy, you will want to make a calculation field and use the concatenate operator between the field. This connects or concatenates two text strings or fields. together.

Field A & Field B

So if field A contains the word “dog” and field B contains the word “cats”. This would give you “dogcats”. Now if you want to have a space in between, put in some literal text with a space such between the quote marks such as ...

Field A & “ “ & Field B

and that would give you “dog cats”.

Here you can see that I’ve highlighted the concatenate operator. The calculation is used to build slap together a company name field, add a hyphen and and then the name of the contact.

I added some IsEmpty branches just in case either the company name or contact fields are empty and I don’t have a dangling hyphen.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.