Relationships with an empty field

[Jay Allen]

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

[comment]

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 )

[Jay Allen]

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!

[comment]

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

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

[Chris Robot]

This is incredibly helpful.