? Searching In Other Lists

PointsWell

I have possibly worked too long today but I have managed to get myself stuck...

I have two BOs Contacts and Contracts.

A Contact can have many Contracts and a Contract must have a Contact as Buyer. There are other Contact references in the Contract (e.g. Contract.ps_SoldBy and Contract.ps_Seller)

So:
Contact.pm_Contract [edited for typo - originally said om when I meant pm]
Contract.ps_Buyer

I want to list Contacts who have Contracts.

I had expected to be able to use

FIND Contact WHERE Contact IN Contract.ps_Buyer

however this is returning a blank result set.

I have reinitialised that test database thinking that there might be an issue with old data but it didn't help.

I am stumped.

tford

So you want to show ALL Contacts that have at least one Contract referenced to it?

If so, I would add an attribute to Contacts that keeps track of the number of Contracts referenced to it.

customaware

Am I right in assuming that ps_Buyer (of type Contact) does not necessarily have to be the Contact who Owns the Contract that the Buyer has a peer reference to?

Without actually trying it out and given your requirement to List Contact who have Contracts....

Try maybe...

FIND Contact WHERE Contact.om_Contract IS DEFINED

FIND Contact WHERE (COUNT Contract WHERE(Contract IN Contact.om_Contract)) >0

If you are actually trying to find same but where the ps_Buyer is the Owning Contact

FIND Contact WHERE (COUNT Contract WHERE(Contract IN Contact.om_Contract AND Contract.ps_Buyer=Contact)) >0

PointsWell

Reasons why you shouldn't ask questions when you've been staring at the issue for 5 hours - I mis typed the relationships they should of course be pm and ps.

eagles9999 wrote
FIND Contact WHERE Contact.pm_Contract IS DEFINED

I had tried this but it generates an error at run time saying it is invalid and to use count - in my muddled state I couldn't get COUNT to work but

FIND Contact WHERE (COUNT Contract WHERE(Contract IN Contact.pm_Contract)) >0

this worked.

Thanks Mark.