Relate on calculation fields 
Author Message
 Relate on calculation fields

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




Tue, 19 Aug 2003 08:05:17 GMT
 Relate on calculation fields

Quote:

> 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.

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



Tue, 19 Aug 2003 08:41:55 GMT
 Relate on calculation fields
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

Quote:

> 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





Tue, 19 Aug 2003 09:01:29 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. calculation on related field ??

2. Calculation Field from Related File with list

3. finds in calculations and related fields

4. how to get field in Nth related record in a script or calculation

5. Updating related match field of type calculation

6. Updating related match field of type calculation

7. How to stop index of related calculation fields

8. FMP: Error message with related field calculations

9. Calculation fields versus Insert Calculation

10. field type Calculations versus autoentry via calculation

11. Loop calculation and related files

12. summary calculation in a related file


 
Powered by phpBB® Forum Software