FINDing BO in Reference of Same Object

customaware

There have been many various permutations and combinations of this and for all the previous scenarios I have found and answer.
Except this one....

I have a BO called Employee

Each Employee can have Visible Employees which is a Multiple Reference to Employee.
So, we have two Multiple References in Employee and each is one end of the M:M reference.

Employee
pm_Employees_Visible_To_Me
pm_Employees_I_Am_Visible_To

So obviously, pm_Employees_Visible_To_Me is the matching Reference to pm_Employees_I_Am_Visible_To and Visa Vers.

Ok.... So for a Particular Employee, I want to Add Reference to Employees to populate pm_Employees_Visible_To_Me. I use
a seperate Query so as to limit the displayed Employees to only those that are NOT already a Member of pm_Employees_Visible_To_Me.

As we already know and expected, the following does not work.

FIND Employee WHERE (COUNT Employee WHERE (Employee IN ThisEmployee.pm_Employees_Visible_To_Me) = 0 )

FIND Employee WHERE (COUNT Employee WHERE (OtherEmployee IN ThisEmployee.pm_Employees_Visible_To_Me) = 0 ) also does not work.

One of the other variants of this is FIND B WHERE COUNT A WHERE (B IN A.pm_B AND A.pm_B=ThisA.pm_B)=0 which works fine except
A and B are different BOs.

Does anyone have a solution for my scenario above....

TIA.

Jaymer

I think its a bad scenario to begin with.
From a traditional database perspective (like 40 yrs ago), the "way" to handle a M-M relationship was to create the middle table.
Sure, Aware allows you to bypass this, but at what expense?

You now cannot add any piece of info that would normally be in the middle table:
ie:
effective date,
ending date,
added/updated by,
etc etc

OK, there may be some scenarios where you really don't need any extra information... or you code around it. But generally I make the middle table and end up with more functionality.
Perhaps managing it yourself would give you more options.
J

customaware

Thanx Jaymer,

Yeah. I know that and generally try and avoid the M:M functionality in Aware and do implement an intermediate table.

Just tried to implement this quickly but as your rightly say..... not necessarily quicker.

PointsWell

To add to Jaymer’s comment, AIM is already creating the third intermediate table so you might as well go the extra mile and make the additional table functionally available. Your queries become a lot simpler too.