Not sure I completely understand this question. FileMaker will not return results when one of the match fields is empty.
Then Filemaker can't do this select statement in relation and I have to use SQL:
SELECT * FROM "A copy" WHERE field IS NULL [ AND ...other conditions ...]
There is no relationship in your SQL, only a WHERE clause--functionally the same as performing a find.
Finds can be performed to find records with empty fields. The WHERE clause should also work. But I wouldn't count on a JOIN clause working if the match field is empty.
Rick and Phil are (of course) both right.
You might use stored calcs to concatenate the two fields in one, and then, imply only these stored calcs to establish the relationship.
Let us know...
Edit : modified my text because you must of course create two calcs, one for each table.
Thank all for your answers. I use stored calc with text "<empty>", if the value is null.
Is this the relationship that doesn't work?
Invoices::InvoiceNumber = INvoices 2::Number and
Invoices::suffix = Invoices 2::suffix
and the records don't match when the suffix is null?
Then Fred is on the right track.
Define a calculation field, cDupKey as
InvoiceNumber & "|" & Suffix // "|" may not be needed. It depends on the type data in Suffix
and your relationship can be:
Invoices::cDupKey = Invoices 2::cDupKey
As long as InvoiceNumber is not null, this will work even if suffix is null.
Oh yes and simply using ! as find criteria in cDupKey can find all records with duplicate values in that field--which may remove the need for the relationship in some cases.