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?