Un Simple Query

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Un Simple Query

Post by PointsWell »

Unsimple as in it is not wildly complex but it isn't just find where.

To create a new Contact record I have to first create a CreateContact (this is a multi step process due to not being able to do conditional mandatory for attributes and queries for drop downs that are conditional on the values of other attributes).

When I am creating a CreateContact I want to be able to associate the resultant Contact with another Contact and be able to utilise their addresses. The CreateContact may be the Primary or may be the Secondary so whichever it is I need to find the addresses of the other.

The data model looks roughly like this:
Datamodel
Datamodel
Screen Shot 2020-06-18 at 13.13.38.png (37.96 KiB) Viewed 6136 times
While building up the CreateContact record I create a Relationship record and place the CreateContact into psCreateContact as a placeholder until the commit creates a Contact and inserts it into Primary or Subsidiary (the other one having already existed). When the Contact has been created then all of the psCreateContacts through the child BOs become UNDEFINED and the new Contact is put into obContact.

The challenge. While I am creating the CreateContact I can create addresses but may not if I already have an address for the Relationship contact.

When I create the CreateContact I place its ID into LIRU. I do not like placing the BO into LIRU as it creates lots of unnecessary SQL regardless of the BO status and those BOs end up having a huge spiderweb of child BOs - it's hard to then debug.

So to see the addresses that I have created for the CreateContact plus any addresses of related Contacts I have this:

Code: Select all

FIND Address WHERE Address.qCreateDeletable='No' AND ( Address.psCreateContact.ID=LoggedInRegularUser.CN0000ID OR 
(Address.obContact=Relationship.pSubsidiary
 WHERE EXISTS Relationship WHERE  (Relationship.psCreateContact.ID=LoggedInRegularUser.CN0000ID))
If I try to save this though I get an error on the second WHERE (this error occurs if I use COUNT as well).

Have I stared at this too long and missed something?
Rennur
Posts: 1191
Joined: Thu Mar 01, 2012 5:13 am
Location: Sydney, Australia

Re: Un Simple Query

Post by Rennur »

Try:

Code: Select all

FIND Address WHERE Address.qCreateDeletable='No' AND ( Address.psCreateContact.ID=LoggedInRegularUser.CN0000ID OR
(EXISTS Contact WHERE (Address.obContact=Contact AND EXISTS Relationship WHERE (Relationship.psCreateContact.ID=LoggedInRegularUser.CN0000ID)))
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Un Simple Query

Post by tford »

For situations like this I like simplicity, even at the cost of a bit of overhead. I would add a Y/N flag or Contact counter attribute that indicates if you already have an address for a Relationship. With that attribute, the query is simple!
Tom - V8.8 build 3137 - MySql / PostGres
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Un Simple Query

Post by PointsWell »

Rennur wrote:Try:

Code: Select all

FIND Address WHERE Address.qCreateDeletable='No' AND ( Address.psCreateContact.ID=LoggedInRegularUser.CN0000ID OR
(EXISTS Contact WHERE (Address.obContact=Contact AND EXISTS Relationship WHERE (Relationship.psCreateContact.ID=LoggedInRegularUser.CN0000ID)))
Thanks Marco, this doesn't work, but it has pointed out where my thinking was missing a link.
Last edited by PointsWell on Tue Jun 23, 2020 3:29 am, edited 1 time in total.
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Un Simple Query

Post by PointsWell »

tford wrote:For situations like this I like simplicity, even at the cost of a bit of overhead. I would add a Y/N flag or Contact counter attribute that indicates if you already have an address for a Relationship. With that attribute, the query is simple!
True, and that is my intention once I identify addresses, but when I create a new contact I won't know fully the extent of the addresses, so I am in a chicken and egg cycle at the moment.
Post Reply