[SOLVED-ISH] Complex Query Help Needed

PointsWell

I have a couple of complicated queries that are having my pull my hair our.

I have a number of different Contact Types instantiated as BOs.

Contact1000, Contact1001, Contact2000 and Contact2001

These BOs are grouped into ContactGroupContracting.

ContactGroupContracting has a common attribute psRole which identifies what sub-type of Contact it is (for many reasons that Type() wouldn't be sufficient for)

Contact1000 and Contact1001 has an additional psProprietor which can be UNDEFINED.

I'm trying to find all of the ContactGroupContracting where the Contact1001.psProprietor is UNDEFINED.

I've tried this:

FIND ContactGroupContracting WHERE ContactGroupContracting.obTenant=LoggedInRegularUser.obTenant AND ContactGroupContracting.psRoleSub.obRole.Code=1000 AND (ContactGroupContracting.psRoleSub.Code=1200 OR EXISTS Contact1001 WHERE (ContactGroupContracting.ID=Contact1001.ID AND Contact1001.psRelatedProprietor IS UNDEFINED))

(I've tried ContactGroupContracting.ID=Contact1001.ID and Contact1001.ID=ContactGroupContracting.ID)

But this is bringing back all of the Contact1000/1 and ignoring the psRelatedProprietor IS UNDEFINED

Any suggestions as to where I am going wrong?

=============== How I fixed this ===================
My solution was a bit of work around rather than a solution.

I have used an interim BO and set different fields dependent on the starting point (ie was it a Seller Contact or a Buyer Contact). I've then loaded up a different form depending on which fields are initially set and have 'locked' these fields using shortcuts to the Name attribute on the record. If I start from a Buyer Contact then I populate the Buyer Contact attribute and then show a form with a shortcut to Buyer.Name. This alleviates the need for a complex query.

Jaymer

for a little clarity...

FIND ContactGroupContracting WHERE

ContactGroupContracting.obTenant=LoggedInRegularUser.obTenant AND
ContactGroupContracting.psRoleSub.obRole.Code=1000 AND

(
ContactGroupContracting.psRoleSub.Code=1200 OR
EXISTS Contact1001 WHERE
(ContactGroupContracting.ID=Contact1001.ID AND
Contact1001.psRelatedProprietor IS UNDEFINED)

)

issue: bringing back all of the Contact1000/1 and ignoring the psRelatedProprietor IS UNDEFINED

JonP

Hopefully you didn't see my earlier reply which was monumentally stupid. Here is a potentially less stupid reply:

I have had no luck with EXISTS. I use COUNT instead. Try:

...OR COUNT Contact1001 WHERE (ContactGroupContracting.ID=Contact1001.ID AND Contact1001.psRelatedProprietor IS UNDEFINED)>0)

Jaymer

JonP wrote
Hopefully you didn't see my earlier reply...

... oh, I saw it !!! 😮

JonP

I pulled an Emily Litella from SNL. Never mind. :mrgreen:

Jaymer

Actually, I thought your post made sense - it confirmed to me that someone else (besides me) has no friggin clue what he's talking about.

I just didnt want to take the time to actually write about it.
I don't know if he's talking about contacts (like actual people) or groups of Types of contacts.
Even after simplifying the Query, the whole thing doesn't make sense to me.

PointsWell

I am now really intrigued as to what happened whilst I was sleeping???

The Contacts are many different types of Contact. It is the result of a legacy datamodel that was modified to make AIM more efficient. The most significant number defines a type grouping, the least significant number indicates people or companies. The BO Groups bring these together as buyers and sellers. It is more efficient as each BO and Group is as narrow as it needs to be and has minimal NULL values.

Jon, I’ll try with COUNT>0

PointsWell

No success with COUNT (in multiple ways).

Rennur

Change psRelatedProprietor in the CODE below to the proper BO Name.

TRY:

FIND ContactGroupContracting WHERE ContactGroupContracting.obTenant=LoggedInRegularUser.obTenant AND ContactGroupContracting.psRoleSub.obRole.Code=1000 AND (ContactGroupContracting.psRoleSub.Code=1200 OR EXISTS Contact1001 WHERE (ContactGroupContracting.ID=Contact1001.ID AND NOT(EXISTS psRelatedProprietor WHERE (Contact1001.psRelatedProprietor.ID=psRelatedProprietor.ID)))

FIND ContactGroupContracting WHERE

ContactGroupContracting.obTenant=LoggedInRegularUser.obTenant AND
ContactGroupContracting.psRoleSub.obRole.Code=1000 AND

(ContactGroupContracting.psRoleSub.Code=1200 OR

EXISTS Contact1001 WHERE (ContactGroupContracting.ID=Contact1001.ID AND

NOT(EXISTS psRelatedProprietor WHERE (Contact1001.psRelatedProprietor.ID=psRelatedProprietor.ID)))

OR TRY:

TRY:

FIND ContactGroupContracting WHERE ContactGroupContracting.obTenant=LoggedInRegularUser.obTenant AND ContactGroupContracting.psRoleSub.obRole.Code=1000 AND (ContactGroupContracting.psRoleSub.Code=1200 OR EXISTS Contact1001 WHERE (TYPE(ContactGroupContracting)='Contact1001' AND NOT(EXISTS psRelatedProprietor WHERE (Contact1001.psRelatedProprietor.ID=psRelatedProprietor.ID)))

PointsWell

Thanks Mario.

Interestingly (or worryingly) that locks the field and prevents it (or other reference fields being edited).

I am putting this into the too difficult bucket and will instead look at an alternative method using a temporary BO using the base contact BOs and then create the other BO (Contract) inserting the required references to the BO Group. It helps that the Contacts to a Contract become set and never need to be editable (I may come back and regret this thought).

rocketman

Jumping in late here but my understanding of groups is that in order for a query on the group to work, the attribute you are searching on must exist in ALL the BO's in the group. Therefore (If I have understood it correctly), the query will fail because PsPRORIETOR only exists in some but not all of the B.O's

.... of course, I have been known on many occasions to be wrong

Jaymer

only the fields that are in common among all the BOs, are the ones that are listed as being in the group. So I would think there’d be a syntax error if you tried to search on a field that was in 1 of the BOs but not common in all, hence it wouldn’t be in the group.

PointsWell

rocketman wrote
Jumping in late here but my understanding of groups is that in order for a query on the group to work, the attribute you are searching on must exist in ALL the BO's in the group. Therefore (If I have understood it correctly), the query will fail because PsPRORIETOR only exists in some but not all of the B.O's

.... of course, I have been known on many occasions to be wrong

Both correct and incorrect. The psProprietor section is attempting to do a sub query. Sub queries will typically be looking at other BOs not necessarily related to the BO being searched.

You cannot reach psProprietor directly but if you can relate the BO (which is in the Group) to the Group then You can access it that way.