[SOLVED] How To? Filter BO via a Many to Many Linked BO

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

[SOLVED] How To? Filter BO via a Many to Many Linked BO

Post by PointsWell »

Looking for some advice.

I have four BO
  • Contract
    WorkPart
    Work
    Contact
Works own Contracts

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 
I thought (incorrectly) that I should be translating that to AIM by:

Code: Select all

FIND WorkPart WHERE(WorkPart.ob_Contact = Contract.ps_Contact)
FIND Work WHERE (Work IN WorkPart.ob_Work)
However, this gives me the error
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
as there is a one to many step involved and I get the error:

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?
Last edited by PointsWell on Sat Feb 25, 2017 4:19 am, edited 1 time in total.
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: How To? Filter BO via a Many to Many Linked BO

Post by PointsWell »

Code: Select all

FIND Work WHERE COUNT WorkPart WHERE (WorkPart.ps_Contact=Contact AND WorkPart.ob_Work=Work)>0
Thanks eagles9999
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: [SOLVED] How To? Filter BO via a Many to Many Linked BO

Post by tford »

Nice "work" Mark!
Tom - V8.8 build 3137 - MySql / PostGres
Post Reply