PatternCount ( Related ValueListItems vs. Related Field)

[Steven Cappiello]

I have a table that has a self-join based on 4 criteria.

The relationship will typically result in 0 related records, but could return up to 5 related records.

What I'd like to do is check a field on each of the related records to see if the value "none" exists in a certain field for any of these records.

Two ways I thought I could check for this were:

Case ((PatternCount (RelatedField_thru_self_join_T.O.; "none") >= 1); "records found!")

or

by creating a related value list and checking that way:

Case ((PatternCount( Valuelistitems(Get(CurrentFile); "Value List")); "none") >= 1); "records found!")

But neither of these seems to work. Could anyone tell me why ??

Some of the relationship criteria & the field which I'm checking on are unstored calcs.

Thanks.

[-Queue-]

The first one only checks the first related field. On the second one, you are closing the PatternCount before specifying the text to be tested. Try using

If( PatternCount( ValueListItems( Get(FileName); "Value List" ); "none" ); "records found!"

)

Note that if any of the unstored calcs are on the 'child' side of the relationship, then your relationship will naturally fail.

[Steven Cappiello]

THANKS... unfortunately the patterncount of the value list is GOD AWEFUL SLOW :-( it takes a few seconds for filemaker to think about things and return the proper color of the light.

I would love to look at the unstored calc through the relationship somehow... is there some way to do this ?

[Fenton]

How about if you create another relationship the same as the one you have, but add the "none" test. Create an unstored (or global) calculation, = "none", in your parent table. Use that as the "left-hand" key to add.

Then count that relationship. Not fast, but much faster than PatternCount().

[Steven Cappiello]

wouldn't this not work because the calculation that "none" would have to relate to in the child table would be unstored.

[Fenton]

It would work. The unstored "none" is the "left" or parent side of the relationship. Since there really no longer is a left and right (and since this a self-relationship in this case), it's the side you're counting FROM, the place where you want to see the results (which are of course unstored). The unstored "none" targets the stored field with "none" in it.

Edited September 26, 2005 by Guest

[-Queue-]

Since there are only up to five related records, I did not think PatternCount would be that slow; otherwise, I would have suggested using Position or a "none" field, as Fenton has.

[Steven Cappiello]

thanks for the help guys.. it isn't working unfortunately..

the field that I am relating to on the child side is unstored.

when I tried it it shows me a when I try to display the field I'm checking against on the layout.

[-Queue-]

If the field is unstored, then how are you making a value list out of it? Neither should work for an unstored child field.

Does the field's definition reference globals or related fields? If not, change the Storage Options so that it is indexed (stored).