I have four BO
- Contract
WorkPart
Work
Contact
A Contact has many WorkPart and a Work has many WorkPart
So the relationships are:
Contact < WorkPart > Work < Contract
where 1 < m
I am trying to create a filtered list on the Contract BO whereby the user Select the Contact to then filter the Work list
If I were doing this in SQL it would look something like:
Code: Select all
SELECT Work.Title FROM Work WHERE Work.ID=(SELECT WorkPart.WorkID WHERE WorkPart.ContactID
Code: Select all
FIND WorkPart WHERE(WorkPart.ob_Contact = Contract.ps_Contact)
FIND Work WHERE (Work IN WorkPart.ob_Work)
Invalid query string FIND WorkPart WHERE(WorkPart.ob_Contact = Contract.ps_Contact) FIND Work WHERE (Work IN WorkPart.ob_Work)
I can't just traverse the relationships
Code: Select all
FIND WorkFull WHERE WorkFull.om_WorkPart.ob_Contact=Contract.ps_Contact
Work.om_WorkPart.ob_Contact is not valid because attribute ob_Contact has 'multiple allowed' flag on
(It doesn't the allows multiple flag is set on om_WorkPart not ob_Contact)
If I go down the SQL rabbit hole then I am going to have to put a whole bunch of test v operations table data into the query and besides that it feels like this should be achievable using the ootb functionality as it isn't that complex.
Clearly I am approaching this from the wrong direction - what direction should I be coming at it from through?