calculated fields

Tag: calculated fields

FileMaker Pro

Navigate Multi-Page PDFs in Container Fields

So if you’ve read my last post, you’ll know I had been searching for and had found the right syntax for adding a QuickTime Movie to a Container Field via a Set Field Script Step, but why was I looking to do this?

Well it’s not because I wanted to store movies in a Container Field, not at all, despite using the “macmovie:/” file path to force the import of my file as a QuickTime Movie, I was in fact importing a .pdf. But why?

Well if you import a .pdf file using this syntax it allows you to view multiple pages of a .pdf rather than just the first page you can see if you import it either as a file or an image.

Now this is really handy if you need to go through some 3,500 invoices to record data from them into a database as you can have the .pdf you’re entering from adjacent to the fields you’re entering data into.

It’s a win-win solution.

I’m a small business consultant enabling small business owners to achieve sustainable growth, whilst working part-time at Tees Valley Arts. For more about me personally see peterneal.co.uk

FileMaker Pro

Import Quicktime into Container Fields via a Set Script Step

I had some problems finding the correct syntax for this process, so now I have successfully found it I thought I’d share it:

On a Mac: moviemac:/path_to_your_file

On Windows: moviewin:/path_to_your_file

Oh and why might you want to do this? Because if you use the Insert File, Insert Picture or Insert Quicktime Script steps you have to specify a file path manually rather than doing so via a calculation, which is very inconvenient if you need to import say: 3,500 files as needed to.

If you use the Set Field Script Step and specify the appropriate Container Field, with the new contents being your Calculated File Path Field then you can effectively side step this problem. I only need to do this as a temporary measure so it’ll work just fine for my purposes.

If you’re looking for something more permanent you’d have to think about whether or not it stores the file as reference or actually inserts it, which makes a big difference and the answer to which I don’t know (if you DO know or find out please tell us in the comments).

I’m a small business consultant enabling small business owners to achieve sustainable growth, whilst working part-time at Tees Valley Arts. For more about me personally see peterneal.co.uk

FileMaker Pro

Record Count in Portal Fields

I used to use this very simple calculation to display a record count in all my solutions:

c_Record_Status

“Record ” & Get(RecordNumber) & ” of ” & Get(FoundCount) & ” ( ” &  Get(TotalRecordCount) & ” total )”

and though it still works in my main layouts in FileMaker 11 it seems to have stopped working in portals, so I had to come up with a new solution and thanks to a post by Daniele Raybaudi over at FileMaker Forums I found a much easier solution:

c_Found_Count_Portal

Get(FoundCount)

which I then compliment on the layout by using the layout shortcut for Get Record Number Symbol or Get Portal Number Symbol: @@. So it is rendered on the page as:

@@  of <<JOIN_Addresses_Companies::c_Found_Count_Portal>>

Which is in itself a much more elegant solution. Oh and remember to set the calculated field to un-stored.

I’m a small business consultant enabling small business owners to achieve sustainable growth, whilst working part-time at Tees Valley Arts. For more about me personally see peterneal.co.uk

FileMaker Pro

The Perfect Calculated Name Field in Filemaker

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

 

Let(

[

title = If( not IsEmpty (Contacts::Title); Trim(Contacts::Title) & ” “; “”);
forename =  If( not IsEmpty (Contacts::Forename); format.initials(Contacts::Forename); “”);
middlename = If( not IsEmpty (Contacts::Use_Initials); format.initials(Contacts::Middle_Name); “”);
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 & forename & middlename & surname & suffix

];

result

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

 

I’m a small business consultant enabling small business owners to achieve sustainable growth, whilst working part-time at Tees Valley Arts. For more about me personally see peterneal.co.uk