1 Reply Latest reply on Sep 20, 2010 8:52 AM by TSuki

    Multipart key, matching on blank value

    RussHibbing

      Summary

      Multipart key, matching on blank value

      Product

      FileMaker Pro

      Version

      11

      Operating system version

      Win 7 Professional

      Description of the issue

      I have a relationship betweeen two tables that is a three part key.  MagCode, Issues, Special Requirements.  All records have a MagCode and Issues, but only some have Special Requirements. For those not having a Specil Requirment the field is blank.  When trying to display values from the joined tables, the only records that get pupulated are those with a Sprecial Requirement.  It seems that FileMaker does not match blank or empty values between the two tables as matching.  Is there a way to make the blank or empty values a match?

      Steps to reproduce the problem

      Created a relationship between Products and MagOptions with the three part key, MagCode, Issues, Special Requirements.

      Expected result

      Expected that fields having no value would also match between the two tables, and pull related information.

      Actual result

      Only records with all three key values having an actual value pull related information. When Special Requirements is blank in both tables, those records do not pull realted information.

        • 1. Re: Multipart key, matching on blank value

          Russ Hibbing:

          Thank you for posting.

          FileMaker Pro won't match on empty fields. This is in part to be consistent with SQL join behavior.

          It should be easy to work around this behavior by simply defining a value to signify that there is no Special Requirement such as "none" or " " (space). To set this up, you could do a find with "=" in Special Requirements to get all records with an empty value and then use Records > Replace Field Contents to add a value. You can then specify an Auto-Enter value for new records in the field options for Special Requirements.

          TSuki
          FileMaker, Inc.