I've not managed to write a useful post this week as I've been (am still am) busy re-writing BusinessTracker.

For those of you who are not aware, BusinessTracker is the system I wrote to run my business on and which I have sold over the years to a handful of customers, primarily because a good friend said he really liked it and I should sell it.

Anyway, this week I've re-done the navigation so it is dynamic to system preferences (allowing me to turn modules and functions on and off at the click of a button). I have also optimised the layouts for iPad and am working on iPhone layouts (bit more of a challenge due to screen size ;-)

For those of you who are interested in having a look, you can find details here

More next week…

Finding your records in FileMaker Pro Part 2

Building a find can be as simple as entering a text string into a field and clicking the find button (such as all companies in the UK), but quite often you want to go further than this.

Lets take the example above and build on it to also find all companies in the UK we have also invoiced. You can either do a find for companies with UK in the Address, then do another find and enter * in the invoice number in the invoice portal (assuming you have one), then select Constrain Found Set from the Requests menu. This will constrain the found set (ie find only those companies who are in the UK and have been invoiced).

To extend a found set you can do a similar thing, for example find all UK companies and then perform another find with Germany in the address field and select Extend Found Set from the Requests menu. This will give you companies in the UK and those in Germany.

You can build on a current found set by using either of these methods.

An alternative method (and a better/quicker one if you know from the start what you want to do) is to enter the first find criteria, then select Add New Request from the Requests menu and enter the second find criteria.

You need to be careful with your logic to make sure you have the correct criteria and find being built up.

One other useful tip is to be as vague as you can without being too vague. This can be a great help in finding a record where your data is not necessarily always 100% accurate and consistent. For example if you are looking for somebody called John K Smith, you are probably better off not entering that exact string as it may have been entered without the K for example, so enter Smith and you may be given a list of a few possibilities, but it 's a lot easier to then ascertain the exact record you are after than if you are presented with several hundred.

Finding your records in FileMaker Pro Part 1

So, you've entered all your customer addresses and contact numbers, now you need to find the specific Bob you needed to call today but you can't for the life of you remember which company he works for, or what his hone number is.

You need to do a find (or search) in your address book to get to the record you require.

Generally, when I write a solution I give users a find button and/or a quick find facility, but using FileMaker native functionality you would select Find from the View menu in a layout that contains the fields you wish to search on (such as a company record layout). You then enter the criteria for the search, this can include some special characters (Operators) and there are also a few things you need to bear in mind when constructing the find (see the next blog entry for these).

Find Operators
=    match whole word (or match empty)
==  match entire field
!     find duplicate values
<    less than
≤    less than or equal
>    greater than
≥    great than or equal
…  range
//    today's date
?    invalid date or time
@  any one character
#    any one digit
*    zero or more characters
\     escape next character
""   match phrase (from word start)
*"" match phrase (from anywhere)

The Although all of these operators have their use, the ones that I find particularly useful are:

… particularly useful if finding all records in a given date range (such as invoices for a month) where you might want to enter something like 1/4/2013…30/4/2013 to find all records in April 2013.

* prefixing a complicated serial number (such as an invoice number) where you don't want to enter the whole string, but concentrate on the bit that changes, for example *110 where an invoice number might be something like inv00110


// to find records with today's date without having to key the whole date.




In the next blog entry I will give some hints  and tips on how to build your finds

Relationship Diagrams - How to avoid the spider web of doom

When the switch was made from FileMaker v6 to v7 I remember a lot of discussion about how on earth you build your relationships now everything is in a single file. I also say a lot of solutions where the relationship diagram is an unmanageable mess (even made a few in the early days :-). It must be said that I still see those today and it is a nightmare to get into somebody else's database and try and work with it when this is the case.

To help me avoid the "spider web of doom" I basically use 1 simple rule.

1. Each table occurrence must have single a master instance. Name this after the table itself.

It then follows that …

1. This is the table occurrence that you use for evaluation context within calculations.
2. And very importantly, this is also the only table occurrence you ever base a layout on.

Put this together with the simple naming convention I use and it is clear which table occurrence to use and what it shows.

Master instance = table name
Child table occurrence = parent table.child table

So for example, an address book which has a Company table and a People table would have the following relationships…

Master instances
    Company
    People

Relationship showing all people related to a company
    Company.People

The table occurrence "Company" would be the one you base the main company layout on, and would probably contain a portal showing Company.People

You can also then build up and easily understand more complex relationships, for example
    Company.Invoice.Payment

This would show all payments relating to invoices for a given company.

I also sometimes add a lowercase explanation to the end if there are multiple similar relationships or one has a specific purpose, for example
    Company.People add

… might be used for a portal that allows you to add related people to a company via the relationship.

My favourite feature of FileMaker v12

There is normally one or two features in a new version of FileMaker that jump out at you as being really cool, these sometimes end up being a long term favourite, but quite often it's the little one that slips past you to start with that proves to be the one that gives the best benefits in the long run.

The When I first saw v12 I thought the new way of handling containers and file management would be a great addition, and so it has proven to be, although easily my favourite feature are the new themes. The simple reason for this is that I am not very good at design :-) I can happily put a system together and it will do things others may struggle with, it will have all the fields it needs and will look pretty good, but I lack that design flare that allows me to make it look as good as it performs. Now however, with themes, a lot of that pain is taken away and it is very easy to get a great interface. Of course there is still a lot of work to be done in getting the right information in the right place, but I've not really had a problem with that.

One thing I would advise though is to work within the theme, don't try and work against it. You can do so much with the new interface designer, formats depending on what the user is doing, curved edges to fields, etc but if you make too many bespoke changes it then becomes a real problem if you want to apply a new theme. Admittedly this is not something you will probably be doing after the client is happy with the way it looks, but if you want to re-purpose the solution you may want to apply a different theme.

Have some fun and explore the different themes, they make great looking interfaces a lot easier to put together.

FileMaker field naming conventions

It makes sense to use a convention when naming your fields, this can help in a number of ways, not least by allowing you to easily identify how a field should be used. Treat a field like you would a variable, give it a meaningful name.

What do I do? Well, I only have a couple of conventions I follow, these include…
1. Prefix a global field with g, eg gDate.
2. Prefix a summary field with a s, eg sInvoice Total. This is useful when building a 'system layout' in that it will easily allow you to identify the fields to leave off. Summary fields can have a big performance hit so should really only be displayed when needed.
3. Name the field with the same name you\d like to see as it's label when it appears on a layout. This is useful as it allows you to easily change all labels for a particular field in one hit if appropriate, such as a Customer Ref becomes Client Ref.
4. Keys. There are 2 type of key fields that I use when building a database, Primary and Foreign. A Primary Key is the unique identifier of a particular record and I generally call this PK_Unique ID, a Foreign Key is the value used to link a table to another table's Primary Key, I name these FK_Company ID. Note that if you use the same field name for all table Primary Keys it allows you to be cunning with dynamically naming fields in scripts and in CWP solutions.

Welcome

Welcome to my FileMaker blog. I thought it was about time I wrote one as I have been developing database solutions for mnmnmnm years now, since the days of Claris and FileMaker v2.

Anyway, the intention of this blog is to give hints and tips about how to build and use FileMaker solutions, I'm sure other stuff will creep in though ;-)

Feel free to comment and ask questions, I'll try and help where I can.

Thanks for your interest.