Innovative Data Solutions

FileMaker can not take NULL for an answer

While NULL is a concept familiar to SQL users FileMaker developers tend to have little exposure to the idea. Effectively NULL is the value given to a field who has no known value. It is distinct from an empty field or a field that contains the text NULL. NULL has some interesting characteristics most based around the fact that NULL ≠ NULL so SELECT * where field = NULL will never return any values; NULL never equals anything even itself. To test for NULL within MySQL the IS NULL function is used.

So what has this got to do with FileMaker?

A field defined as NOT NULL within MySQL has NOT EMPTY as a validation in FileMaker. Notice the subtle difference there? Because FileMaker has no effective NULL its the best FileMaker can do without some hacking in the MySQL. The best solution is to have MySQL to auto enter NULL but have the field set to NOT NULL. Within FileMaker have allow blank values. This does move the validation from FileMaker to MySQL and you need to insert a blank value into MySQL but it has the advantage of closer following the rules with NULL.

Unfortunately pushing a blank from FileMaker through ESS changes blank to NULL so you have to use a space or some dirty hack with a trigger at the MySQL end. A better alternative is to use the  EXECUTE SQL script function from FileMaker in MySQL when amending, deleting and updating. Using the EXECUTE SQL script step has a number of advantages but principally here allows the use of stored procedures. Typically data amending functions within SQL databases are handled through stored procedures rather than directly amending the data. Stored procedures are preferred because they provide a single point of maintenance for a given function.

The advantages of using stored procedures from the FileMaker end may seem to outweigh the costs; the easy ESS is swopped for SQL. The advantages come with where the stored procedures are maintained. Assuming the MySQL database is managed by a DBA familiar with MySQL they will already prefer using stored procedures over trusting some alien system with carte blanche rights over their precious data. The key to creating advantage in FileMaker is planning the way FileMaker uses the stored procedure. The natural approach is to either create a function within a script, a custom function or a combination of the two. Effectively we have a single point of contact between the MySQL and the FileMaker.

Damian Kelly

Damian Kelly

Damian has been a database developer with Linear Blue since 2007. During this time he has developed complex FileMaker solutions in a broad range of industries from Finance and Investment to Manufacturing to New Media Promotion. Damian graduated in Production Engineering from the University of Hertfordshire in 1996, attained his Masters of Engineering from Kingston University in 1997 and MBA from Heriot Watt in 2006. He holds certification in FileMaker versions 7 through 12 and in Oracle MySQL 5.

More Posts - Website

4 Responses to FileMaker can not take NULL for an answer
  1. Lyndsay Howarth
    September 19, 2011 | 3:43 pm

    So Damian,

    What would be the effect of having FM auto-enter “NULL” as a literal.
    Could the SQL interpret or auto-enter-calculate NULL?

    From a long-term FileMaker developer I like the fact that when a field is empty … it is empty. I have, however, played in this fm>sql>fm loop a number of times so I love to understand more.

    cheers
    Lyndsay

  2. Damian Kelly
    September 25, 2011 | 10:34 am
    Damian Kelly

    Hi Lyndsay,

    Unfortunately just putting the string NULL is doesn’t help as the SQL just sees the string.

    If you put “” into a FileMaker shadow table field then ESS will change that to a MySQL NULL but that stops you putting an empty value into a MySQL field through ESS. So if you have a MySQL field defined as NOT NULL and you want to put empty into the field through ESS you are stuck.

  3. Russell Watson
    November 23, 2011 | 3:26 pm

    @Damian Kelly

    Unfortunately pushing a blank from FileMaker through ESS changes blank to NULL so you have to use a space or some dirty hack…

    Oh dear, this is going to make my life really difficult… :-(

    Is it REALLY not possible to put an empty string into a MySQL field at all via ESS?

    It sounds to me that the most elegant solution would be to make triggers on the MySQL side so that everything works from FileMaker just as you would expect it to, without any unnecessary

    In the simplest form you could just replace NULL with an empty string, in the trigger, couldn’t you?

    If necessary, FileMaker could also send an encoded string (e.g.: "<"">") so that the trigger really knows it is a prompt from FileMaker to enter an empty string.

    I think it is a HUGE omission from the techbrief_ess_fm11_en.pdf that they do not mention in the section “Miscellaneous Limitations of ESS” that you can’t write an empty string to a (My)SQL Database field using ESS!

  4. Damian Kelly
    December 1, 2011 | 12:06 pm
    Damian Kelly

    It seems to be impossible. It is a pretty straight forward test; create a MySQL db and table, create the DSN and create a TO in FileMaker pointing to that DSN. Then in a layout add the relevant fields and see what happens when you try to enter blank values and see what happens.

    Damian

Leave a Reply

Wanting to leave an <em>phasis on your comment?

Trackback URL http://www.linearchat.co.uk/2011/09/filemaker-can-not-take-null-for-an-answer/trackback/
Email
Print