Hi,

Has anyone found a way to relate two files based on a calculation field
in the "other" file. I keep getting the error that it won't work because
the field must be indexed. I set the calculation to index but it doesn't
"stick".

For example:

File A:
field: User name

File B:
field: Username
field: Yes or No

I want a portal in File A showing all records belonging to Username in
File B set to "Yes".

I've been using FM for years, but I've been able to fudge around (by
putting the layout in File B) it before, but this time I can't this time
because there are other portals in the layout related to other files.

Thanks for any suggestions.

Cheers,
Kevin

It's not the calc that's the problem. It's the fact that your key fields
are not identical. (I think)

So,  how to accomplish this?

Create a global field (YesOrNoGlobal) in File A. Then create a Calc
which combines "Username" & " " & "YesOrNoGlobal"  Create the
relationship from the Calc in File A to the calc in File B, which
concatenates "Username" & " " & "YesOrNoCalc" Note that the order for
both must be identical. Note that there is a space concatenated between
each value on each side.

Now, put the global on the layout. Format it with a radio button which
shows the values Yes and No. Click the yes button, filling the global
with the value "Yes." Your portal should fill.

You can also create the calc in File A without the global, just by
combining "Username" & " Yes" (note the space). This hardcoding will
always work, but is not really flexible. The global on the layout gives
you the simplest form of filtered portal. You can look at all the "no"
records just as easily as the "yes" records.

If you continue to get messages about indexing, look at the storage for
each element in File B, because this is where fields must be indexed for
the relationship to work. Set each field, Username and YesOrNo, to be
indexed. Then the calc should be indexable.

If EITHER FIELD is dependent on related values from another file, they
will not index and you cannot use them on the right side of a
relationship. A field must be native to the child file and not based on
any related values to be used in the right side of a relationship.

--
Lynn Allen              Allen & Allen Semiotics
FSA Associate           Filemaker Consulting & Training

Calc fields can be indexed (and therefore can be used as match fields)
*except* when they reference a related field or a global field or any
other kind of field that is not or cannot be indexed.  If you calculation
field does not reference any of these types, check that you have indexing
turned on the for the field itself.

The field called "Yes or No" will cause problems when referenced by a
calculation since it uses the word "or".   The calc field will interpret
it as a field called "Yes", the operator "or" and another field called
"No', and will object when it can't find a field called "yes".   When you
defined that field, you should have received an alert warning you not to
do it.  

For the scenario you describe, I would rename the field (to, say for
example, Show in A), then use the calc:

If (Show in A = "Yes", Username, "")

Make sure neither Show in A nor the calc itself are set not to be
indexed.  It should work, I use this trick myself.

Please note that even though calc fields can be indexed, you cannot use
them on the the right side of a relationship if you need to be able to use
that relationship to create new records through a portal.

--
Bridget Eley

In article