Year: 2010

All database solutions have a basic challenge, which is to present data in the easiest possible format and account for all possible variables.

Once you move over to a database system for contact management as opposed to just the Apple Address Book or Outlook you can use some of this power to better deal with the complexity that contacts can present and thereby provide yourself or your customers with a more complete solution.

Properly formatting names is one of the first things that I dealt with whilst implementing my first Filemaker solution and so when it came to re-building my customer’s database I wanted to make sure that I had taken care of every eventuality.

So what can a name consist of?

1. Forename

2. Middle_Names

3. Surname (or Family Name for those countries that don’t follow the English naming conventions)

This is normally the basic for dealing with the visual display of names within a solution, however, if you want your solution to also take care of names for say printing envelopes (yes some people still send letters) you’ll have to consider:

4. Title

5. Suffix

Now this takes care of the basic fields that you’d need to create in your solution for the average Joe or Jane Bloggs, but there are also some other things to consider:

What if your contact is female and has been married but still uses her maiden name for business correspondence and that’s the context you’ll be contacting her in? You’ll need a field for that:

6. Maiden_Name

So for this contact you’d end up entering data for both her surname and her maiden name, but you’ll want the flexibility to decide whether or not you want that data, so you’ll need a field to capture that choice:

7. Use_Maiden_Name

And what if you need to know your contacts full legal name but in reality you actually refer to them by their nickname as it’s so much easier? You’ll want a field for that:

8. AKA_Name

And again you’ll want maximum flexibility so that you can choose how to display this information, so you’ll need a field to indicate if you want to use that nickname for display in your system:

9. Use_AKA

And that I think covers all the main variables you might have to deal with when considering names (though I’m conscious I’ve ignored titles of nobility, mainly because I haven’t decided how to deal with them yet – future blog post?) So what field types do you choose?

Field Types

All the fields except Use_Maiden_Name and Use_AKA_Name should be text fields. For these two exceptions I simply use a number field, which I use a checkbox in the interface to fill with a 1 if they are to be used, if they are not to be used (the default position) then they remain empty. This leaves the user with the maximum flexibility.

The Calculation

So for the calculation we are obviously going to use a calculated field, I prefer to use an un-stored calculation that minimises the amount of data stored in the database as it is recalculated each time it is needed based on the context of the record being displayed in the database.

The main problem you are going to have to deal with is spacing. A calculation in Filemaker is relatively simple. You take two fields: Forename and Surname and combine them using the following syntax:

Forename & Surname

But this is going to give you the following result: JoeBloggs

So you have to add the space to the calculation:

Forename & ” ” & Surname

Giving you: Joe Bloggs

Naturally you can see once you are dealing with all of these variables your calculation is going to get pretty complicated.

The Old Name Display Calculation

In the system I am replacing the calculation looked like this:

 

 

 

 

If(IsEmpty(Forename); “”; Trim(Forename) & ” “) &

If(IsEmpty(Surname); “”; Trim(Surname))

If(IsEmpty(Forename); “”; Trim(Forename) & ” “) &If(IsEmpty(Surname); “”; Trim(Surname))

And produced a correctly formatted Mr Joe Bloggs if all the fields were filled.

It uses the simple Filemaker If Statement that allows you say:

if something is true do this, otherwise do that.

It also uses the simple Filemaker IsEmpty Function that allows you to check if the field has any content.

Initial Replacement

Initially I just tried to replicate this function and came up with the following calculation:

Case (

not IsEmpty(Contacts::Use_AKA) and not IsEmpty(Contacts::Use_Maiden); Trim(Contacts::Title & ” ” & Contacts::AKA_Name & ” ” & Contacts::Maiden_Name);

IsEmpty(Contacts::Use_AKA) and not IsEmpty(Contacts::Use_Maiden); Trim(Contacts::Title & ” ” & Contacts::Forename & ” ” & Contacts::Maiden_Name);

not IsEmpty(Contacts::Use_AKA) and IsEmpty(Contacts::Use_Maiden); Trim(Contacts::Title & ” ” & Contacts::AKA_Name & ” ” & Contacts::Surname);

Trim(Contacts::Title & ” ” & Contacts::Forename & ” ” & Contacts::Surname)

)

The FileMaker Case Statement allows you to nest multiple Filemaker If Statements and thereby achieve something more complex and as you can see it’s pretty complicated and difficult to read.

You’ll also notice I used the FileMaker IsEmpty Function in a different way by adding the not in front of the function you’re able to reverse the function to assess when a field is filled rather than empty.

And although it worked in some instances I hadn’t taken account of all of the variables and I came to the conclusion that by using the FileMaker Case Statement I have to repeat each test to get the right result in every context and where to add or remove spaces was also becoming pretty complicated.

So after a little time googling, I came across this blog posting from Skelton Key which explained the power of the FileMaker Let Function. After a little fiddling around I came up with the following calculation:

The Improved Name Display Calculation

Let(

[

title = If( not IsEmpty (Contacts::Title); Trim(Contacts::Title) & ” “; “”);

forename =  If( not IsEmpty (Contacts::Forename); Trim(Contacts::Forename) & ” “; “”);

middlename = If( not IsEmpty (Contacts::Middle_Name); Trim(Contacts::Middle_Name) & ” “; “”);

aka = If( not IsEmpty (Contacts::AKA_Name); Trim(Contacts::AKA_Name) & ” “; “”);

whichname = If( not IsEmpty (Contacts::Use_AKA); aka; forename & middlename);

surname = If( not IsEmpty (Contacts::Use_Maiden); Trim(Contacts::Maiden_Name); Trim(Contacts::Surname));

suffix = If( not IsEmpty (Contacts::Suffix); ” ” & Trim(Contacts::Suffix) ; “”);

result = title & whichname & surname & suffix

];

result

)

As you can see by using the FileMaker Let Function I have been able to break down the calculation into it’s segment components and then put these components together in a more readable and efficient manner.

You’ll also notice that I’m using the FileMaker Trim Function which allows you to remove any extra spaces from a field before you use it to make sure you’re just getting the text you want.

So there it is. My perfect calculated name field for display:

c_Name_Display

Please feel free to copy and use it and if you have improvements you’d like to suggest please do so in the comments.

Oh and before I forget. My perfect calculated name field for post:

c_Name_Postal

The Improved Postal Name Calculation