Check for related using found set

[bcooney]

I'm drawing a blank here. I want to see if any of the records in my found set (Contacts) has a related record in another table (Dealers). The Contacts records would have a foreign key _kF_DealerID populated.

I know I can gtrr in a new window and check the found count, but there must be a calc I can do. If I do a gtrr using found set, and the first record does not have a related record, then Get (LastError) = 101. So, I can't use that.

GetNthRecord somehow?

[bcooney]

Well, I ended up using Constrain. After my find, I constrain the found set to only include records where _kF_DealerID is not empty (*). If the foundcount=0, I have not found any contacts that are dealers.

I'm still betting there's a better way.

[Fenton]

If you used a Custom Function to gather the IDs in the found set (unstored calculation), then a relationship from that field to the ID in the other table would tell you if any had a match. Or, you could use the Copy All Records copy/paste into a global field trick (old but fast; requires script).

[comment]

How do you count records in the found set that fulfill a certain condition? Usually, you'd formulate the condition as a boolean, and then use a summary field to sum/count the result. I don't see why this couldn't be done the same way.

[bcooney]

Yes, exactly. And by using constrain I don't need a summary field in the current table or a flag field (since I have the foreign key to search)or a new relationship or a custom function.

I just thought I was missing an obvious calc or use of GetNthRecord. Guess not. I often use if not Isempty(related field) to see if a single record has any relatives, but haven't needed to see if any found records have relatives.