Relationships to SQL Data including Empty/Null

Discussion created by JeffreyBloch on Jan 3, 2019
Latest reply on Jan 3, 2019 by JeffreyBloch

My FM application accesses SQL/ODBC data across a network and I have no control over the data end (ie change properties, etc.), therefore I can't add create a stored calc field which includes the word "ALL" to each record, thus allowing for easy access to all records.


I have a relationship from FM to one of the tables:

  • Hair Color Search field (FM) -> hairColor (sql)


I use a multi-line key to gather records matching multiple criteria, ie:

  • Black
  • Brown


I allow the user to select as many or as few hair colors as need be, and do the same for Gender, Eye Color, etc.. I use CALC fields to form the basis of each relationship, as above. There is one 'Master' relationship which connects all CALC fields (ie Gender, Eye Color) with the sql side such as:

  • Race CALC = Race
  • Gender CALC = Eye Color
  • Hair Color CALC = Hair Color
  • etc


This works very well in all cases except when no search criteria is selected for a particular item (ie Gender). My CALC fields auto-populate with all possible criteria (ie male, female, unknown), which relates to all records except those which are empty. The crux of the problem is an instance when a user searches for WHITE MALES, with no other criteria. I initially populated my CALC fields (when no criteria is selected thereby should show include ALL records) with a SPACE and Carriage Return in addition to all possible values. This worked sporadically. I discovered a reliable means to relate to records in which fields are empty, and that is:

  • CALC field containing only the #1 > Hair Color


How do I set up a relationship that will remain flexible enough for the following example:

  • Race = White
  • Gender = Male
  • Hair Color = Brown
  • Eye Color = None specified therefore don't exclude any records


The other means I see to accomplish this is to perform a find, but with the flexibility I prefer for the user, it seems pretty complex on the development side. For instance, if a user is searching for persons matching the following:

  • Age = Range 23-30
  • Hair color = Black, Brown, Bald
  • etc


I think this will require multiple/many search Requests. Any thoughts on the pros/cons of these methods or ideas for others?


Thank you!