Good day. I am trying to create a multi-field lookup that will match all records to a blank field. Specifically:

File A File B

Field A1 = Field B1

Field A2 = Field B2

Field A3 = Field B3

All three of these are one (File A) to many (File relationships. The data enters File A in a portal. What I want to do is set this up so that, if one field in A is empty, the relationship will match all records for the corresponding field in B, and that the match would only be limited by the other fields. Ultimately, if all fields in A are empty, all records from B should come into the portal. What Filemaker seems to want to do is not evaluate the relationship at all if any of the fields in A are empty.

My attempt at a workaround was to use, for File B, calculation fields which used, for example, "" & P & Field B1, however, this configuration works exactly the same.

I have toyed with using an intermediate field in File A as the match field, which would not be displayed, but would calculate to some character string that I could use in File B if the field in File A was empty, but that seems like an awfully complicated solution to this.

Is there some way to match all values to an empty field in Filemaker 8.0v3?

Thanks,

Jay Allen

You will need two calculation fields for this (or more, if you are using multi-predicate join). For simplicity, let's take a simple relationship:

Parent::Value = Child::Value

In the Child (many) table, the calculation field is =

Value & "¶ALL"

In the Parent (one) table, the calculation field is =

Case ( IsEmpty ( Value ) ; "ALL" ; Value )

Yeah, that's what I was talking about as a possible work around. Just wondered if there was an easier way. However, I have implemented that for now, and it seems to be working.

Thanks much!

Don't know about easier, but you might find this thread interesting:

http://fmforums.com/forum/showtopic.php?tid/172518/

This is incredibly helpful.