By using multi-criteria relationships in order to find identical records, lookup doesn't find them if one field is empty
I need to spot if additions to a database contain records already entered. I define the records "identical" (i.e. to a certain degree) if the data in a certain number of fields are identical. I thought I could accomplish this by:
1) labelling each record with a serial number (field "serial_number")
2) making a multi-criteria relationship table, connecting all the relevant fields
3) defining a calculation field with the formula
If the lookup delivers a number which is lower than the one in the current record, it means that it was already there before.
This works fine as long as all the related fields contain data. The problem is that if one or more fields are empty (the records still being identical) the lookup will not deliver anything. This makes the whole approach totally failing. And the database is fairly large so it's important to have this working smoothly.
I'm using FM Pro 11 for mac (lion) in case it matters.
Does anyone have a solution? Sincere thanks if so ..