Many to Many Filtering

customaware

I have a Business Objects A, B and C

A has a Peer Multiple Reference to B
B has a Peer Multiple Reference to A

On the Form of C there are 2 Multi Select Drop Down Controls.
C has a Peer Multiple Reference to A Represented as a Multi-Select Drop Down on the Form of C.
C has a Peer Multiple Reference to B. Represented as a Multi-Select Drop Down on the Form of C.

So, the User is Creating a New Instance of C and Selects say, 2 instances Of A from the Multi-Select Drop Down.
From this we know that the are a multitude records of B that are associated with the 2 selected A records.

I now want the User to select from all of the B BO instances but this should ONLY include as options to select the records that ARE NOT Associated with either of the 2 A BOs that have been selected in the first Multi Select Drop Down for the A BO.

The complexity here is due to all of these being M:M relationships.

I have tried using the COUNT methodology in the expression but that does not work when both ends of the reference are Multiple.

Any suggestions or solutions here would be appreciated.

pureist

To work around RA's with Multiple Instances allowed not being usable in criteria, I think you would need to use a Process with a Subprocess to compile the Instances of B which are eligible for selection into an intermediary object/'flat' attribute and then PICK from them.

Or, the inverse.. compile 'disqualified' B Instances (ie. those which exist in C.A's.B's already) [A and B = plural] and structure the PICK criteria accordingly.

Instead of using an intermediary object, you could add a dummy attribute to C and INSERT into it, and use it in the ultimate PICK.

In the form of C, you would have to switch from using a Multi-select Dropdown for selection of A Instances, as they are not automatically saved 'inline' like they are if a Table of Items, thus a 'do you want to save changes' prompt will appear when the compilation process is initiated.

As well, changes to C.A R/A will also need to be saved automatically so the compilation process can use them in its workings.

pureist

On further consideration, I reckon you can maybe do it without compiling a 'flat' Object.

PICK ONE OR MORE FROM B WHERE NOT(EXISTS A WHERE (B IN A.B_ra AND EXISTS C WHERE (A IN C.A_ra)))
AND NOT(EXISTS C WHERE (B IN C.B_ra))

Input = C

'_ra' denotes Reference Attribute.

This also prevents currently selected B Instances from appearing in the selection list.

customaware

I will test that later today Matthew. Thanx

However, if it works on a PICK then the same query "should" work on a Drop Down Combo filter, I would expect.

pureist

don't spend time yet. I don't have Aware installed, but let me try it with a SQL query today.

I'm sure the original work-around 'compilation' solution will work.

Though I will check this one and LYK.

pureist

Background:

Objects A, B and C..

A has Reference Attribute for B (Multiple/Peer)
B has Reference Attribute for A (Multiple/Peer)
C has Reference Attributes for A and B (both Multiple/Peer)

When editing Instance of C, want to generate list of instances of B to select from (to become Peer to current Instance of C).
BUT, list of instances of B to select from must exclude all which are Peer to all instances of A which are peer to current Instance of C.[/size]

Two solutions:

Note: '_ra' suffix indicates a Reference Attribute.

First:
..if using popup to select Instances of B:
PICK ONE OR MORE FROM B WHERE NOT(EXISTS A WHERE (B IN A.B_ra AND EXISTS C WHERE (A IN C.A_ra)))
AND NOT(EXISTS C WHERE (B IN C.B_ra))
..or, if using C.B Reference Attribute query filter:
FIND B WHERE NOT(EXISTS A WHERE (B IN A.B_ra AND EXISTS C WHERE (A IN C.A_ra)))

Second:
Compiles a dummy Reference Attribute of C ("B_ra_ineligible") to 'flatten' out the multi tiered and 3rd party relationship:

prcC_Select_B (Input: C)
r1
C.B_ra_ineligible=UNDEFINED
r2
FIND A WHERE A IN C.A_ra
prcSelect_B_sub1

r3
PICK ONE OR MORE FROM B WHERE NOT(EXISTS C WHERE (B IN C.B_ra_ineligible)) AND NOT(EXISTS C WHERE (B IN C.B_ra))
INSERT B IN C.B_ra

OR.. Multi-Select Query Filter might work instead of PICK and INSERT:
FIND B WHERE NOT(EXISTS C WHERE (B IN C.B_ra_ineligible))
..but C.B_ra_ineligible would need to be recompiled every time C.A_ra changes.

prcC_Select_B_sub1 (Inputs: A and C)
r1
FIND B WHERE B IN A.B_ra
r2
INSERT B IN C.B_ra_ineligible

pureist

Mark,
The post immediately prior to this has been edited following checking it out in SQL.
The Action and Criteria as they're now stated will work in Aware.

They will work because not closing the open parentheses following 'EXISTS A WHERE (B IN A.B_ra..' indicates that what follows is a subquery which is to use any unclosed (ie. no ")") object prior ('A' at that point) and should evaluate each instance of each prior unclosed object (ie. each instance of A) one by one in the following subquery.. 'EXISTS C WHERE (A IN C.A_ra)'.

Theoretically there could be multiple nested subqueries, each indicated by not closing the parentheses.

It's probably not as efficient as it could be because A has to be first so it is in context for the following subquery, thus all instances of A are evaluated, not just instances of A which are selected in C.

Not closing the parentheses is in effect just like:

Process1
FIND A
Subprocess1

Subprocess1 (Input: A)
FIND B WHERE A IN B.A_ra

..in that each instance of A will be evaluated one at a time in the subprocess.

It could be made more efficient so only instances of A which are Peer to the current instance of C are evaluated.

However, this would require a Reference Attribute (multiple/Peer) in A for C and in B for A.

If there were, then the guts of the statement would change to:

...B WHERE NOT(EXISTS A WHERE (C IN A.C_ra AND EXISTS B WHERE (A IN B.A_ra)))

..ie., instances of A to which the current Instance of C is a Peer will be passed one by one to the subquery, which will return instances of B to which A is a Peer.