[SOLVED] Complex Filter Using a m-n Table

PointsWell

I have a many to many relationship of

Client has many Support Team which results in
Support Staff have many Clients

Client.pm_Support
Support.pm_Clients

A separate table Relationships holds these

Relationship.ob_Client and
Relationship.ps_TeamMember

I am trying to build a filter to add new Support to a Client

Obviously I don’t want to allow duplication of Support but I’m a bit stuck.

I think I need to use Count =0 (as I’m looking for Support where Support not in Reltionships.ps_TeamMember where Reltionships.ob_Client = ThisClient) but I’m stuck on the syntax. Any suggestions?

BenHayat

Count() is an expensive operation, especially in loops.
I'd create two "count" properties in the middle table/object that holds the two tables, which basically holds the counts on either side. You will maintain the values anytime you add/delete objects from the two sides.

This way you have instant access to counts at all time without fetching the other two tables.
In fact, I generally keep several other properties in this middle table that represents totals and other stuff about each side.

tford

I'd create two "count" properties in the middle table/object that holds the two tables, which basically holds the counts on either side. You will maintain the values anytime you add/delete objects from the two sides.

I did this in a BSV at some point also.

PointsWell

Sorry I didn’t explain clearly enough

I want to filter Support not in Relationships.

So on my Clients BO the pm_Support when I create a new Client Support Relationship I want to exclude anyone already on the Relationship table.

Ie Find Support where Support not in Relationship where Client equals this Client.

I could use Count Support where Support In Client.pm_Support if it we a 1-m relationship but how do I do it in a m-n relationship (ie on an intermediary table)

Effectively I am looking for:

FIND Support WHERE (Support<>Relationships.ps_TeamMember WHERE (Relationships.ob_Client = Client))

For this (simplified) data model:

Rennur

Maybe try using EXISTS in the query:

FIND Support WHERE NOT(EXISTS Relationship WHERE (Client IN Relationship.Clients AND EXISTS Support WHERE (Support IN Relationship.TeamMember)))

PointsWell

Rennur wrote

Maybe try using EXISTS in the query:

FIND Support WHERE NOT(EXISTS Relationship WHERE (Client IN Relationship.Clients AND EXISTS Support WHERE (Support IN Relationship.TeamMember)))

Thanks Mario - it's not the correct answer but it has unblocked my mind.

Totally forgot about EXISTS.

If I go to first principles

FIND Support WHERE EXISTS Relationships WHERE (Client=Relationship.ob_Client  AND Support=Relationship.ps_TeamMember)

Gives me a list of Support in Relationship for this client.

So working out the negative of this you get.

FIND Support WHERE NOT(EXISTS Relationships WHERE (Client IN Relationships.ob_Client AND Support=Relationships.ps_TeamMember))

Thank you for clearing my fog!

tford

Not your scenario, but does this help at all?

https://forum.awareim.com/d/5274

customaware

I am not 100% sure this is what you are after but I suspect it is...

You can see the filter queries on the Client and Support Form of Relationship.

Let me know if that helps

ComplexFilter.zip

38.87kB

PointsWell

eagles9999 wrote
I am not 100% sure this is what you are after but I suspect it is...

You can see the filter queries on the Client and Support Form of Relationship.

Let me know if that helps

[attachment=0]ComplexFilter.zip[/attachment]

This was what I was trying to do all weekend but the fug was upon me.

Mario's suggestion got me out of the funk and got me where I needed to be using NOT(EXISTS).

I wonder what the relative costs of the two routes is (I will look at that another day when I am not fighting other OB relationships)

PointsWell

tford wrote
Not your scenario, but does this help at all?

https://forum.awareim.com/d/5274>

Thanks Tom - as I was saying to Mark - this was what I was trying to remember / work out over the weekend!

PointsWell

This is my working (at the moment) solution

FIND Support WHERE NOT(EXISTS Relationships WHERE (Client IN Relationships.ob_Client AND Support=Relationships.ps_TeamMember))

So not as complicated as I seemed to be making it for myself over the weekend.

Jaymer

this thread helped me find a solution to something. thx. was simple...

I have Purchase Orders.
I want to present a list of DISTINCT VENDORS for a given set of POs.
I can't use "IN" because there is no ownership reference from a Vendor "down" to his POs.
Since the custom SQL option was removed, it was easily done this way:

FIND Vendor WHERE EXISTS PO WHERE ( Vendor = PO.ps_Vendor and PO.Status='Open' )

keywords: SELECT DISTINCT , SELECT UNIQUE