Complex Query Conundrum

customaware

Always seem to struggle with some of these types of queries...

We have 3 BOs... Customer, Agency, Store

A Customer can be related to many Agency .... So, Customer has a Peer Many reference to Agency
A Store can be related to many Agency .... So, Store has a Peer Many reference to Agency

I know the Customer and I want a Query to display all of the Stores that are in an Agency that is related to that Customer.

I expected one of these to work... but no cigar...

FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency)>0
also tried...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN ThisStore.pm_Agency)>0
also tried...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN ThatStore.pm_Agency)>0

Any ideas or suggestions would be appreciated.

PointsWell

Agency IN Agency IN

Is this a typo in your post or in your quey?

Agency cannot be in Agency

customaware

It’s a typo in my post.

I’ll edit it.

PointsWell

I tend to avoid IN and do the reverse relationship but it's not clear what the Agency.p_Store relationship.

If it's a ps then I would do

FIND Store WHERE COUNT Agency WHERE ( Agency.psCustomer = Customer AND Agency.psStore = Store)>0

If they are many to many then I avoid using these in AIM and instead create an intermediary BO that handles the 1-m-1 relationship.

So CustomerAgent BO and StoreAgent BO

In part because that is what is happening anyway and in part because with lists you have little auditability - you can't track the date that a relationship became active and when it becomes inactive (without doing an audit log table)

customaware

I don’t have a matching Agency.ps_Customer as each Agency could be associated with multiple Customers

PointsWell

eagles9999 wrote
I don’t have a matching Agency.ps_Customer as each Agency could be associated with multiple Customers

I'm outta ideas then beyond the intermediary BO CustomerAgent.

Agent.pmCustomerAgent
CustomerAgent.psAgent
CustomerAgent.psCustomer
Customer.pmCustomerAgent

BLOMASKY

I don't suppose you want to use a Stored Procedure to retrieve the records?

Bruce

himanshu

:idea:
FIND Store WHERE (EXISTS Agency WHERE (Store IN Agency.pm_Stores AND Agency IN Customer.pm_Agency))

customaware

Thankyou all for your suggestions.....

This has turned out to be one of those situations where the actual data can trip you up.

In my original try....
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency)>0

It seemed incorrect because I was getting back ALL Stores which is not the answer I was expecting.

After frigging around with this for 2 days, I have now realised that one of the Agencies actually does have ALL Stores assigned to it. So the result was
in fact correct.....

When I limited the query to a particular Agency like this...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency AND Agency.ID=17)>0
Then it returns the correct number of Stores.

So, problem sorted.

Apologies for wasting everybody's time :-/