AnsweredAssumed Answered

Best practice – Relationship with empty values?

Question asked by carlsson on Jan 15, 2019
Latest reply on Jan 16, 2019 by smith7180


There are two tables; ORDER and INVOICE.

Invoice have a field called INVOICE DATE. This may have a value or not.

From Order I want to create a relationship that returns all Invoices with no Invoice Date. (I don't want to use a Filter, because I need to have a "real" relationship between the two.)



AFAIK there's no way to include empty date values in a relationship, thus I have to create a workaround.

The workaround that seems to be easiest is to create a Numeric calculation field like this "cnInvoiceDate = GetAsNumber ( Invoice Date )", and then create a relation between a number field and the cnInvoiceDate field (for me, I always have a field called "One" with autodata of 1, so I can use that). Other solutions is to use the Record ID field or whatever have you.

The relationship would look like this; "One > cnInvoiceDate". Since cnInvoiceDate is a numeric field, Filemaker understands this (why it doesn't understand that no date equals zero goes beyond me – maybe it's a bug).



Is this the best solution? Do you really need to create a new field, just for this relationship to work? It bugs me, cause I always want to keep the field count down... Or are there any better solutions?