Selecting Non-Redundant Parent/Children for Hierarchical BO

Contains tips for configurators working with Aware IM
Post Reply
JonP
Posts: 287
Joined: Thu Feb 16, 2017 9:49 pm
Location: United States

Selecting Non-Redundant Parent/Children for Hierarchical BO

Post by JonP »

This is probably going to be a big yawn for you old-hats, but for us newbies, this may salvage your sanity (it's too late for me).

So I have a form from a BO called Consideration. Consideration can have a parent (RootConsideration) and children (pm_BranchConsiderations). I want to be able to select a parent or children that is not:

A) The same consideration as the instance related to my form.
B) Already a child of the consideration.
C) Already a parent of the consideration.

It would be nice if it also looked for grandparents and above as well as grandchildren and below. But that would require nested recursive queries, which I don't believe AIM supports. If you have a solution for that, please share.

For the query, I first tried using the COUNT=0 and NOT (EXISTS) suggestions and got nowhere. Here's one such example that doesn't work reliably:

Code: Select all

FIND Consideration WHERE NOT (EXISTS Consideration WHERE (Consideration IN ThisConsideration.pm_BranchConsiderations)) AND Consideration<>ThisConsideration AND (Consideration<>ThisConsideration.RootConsideration OR ThisConsideration.RootConsideration IS UNDEFINED)
This is what reliably works for both selecting the parent in the combo-box filter and the children in the pick list:

Code: Select all

FIND Consideration WHERE (Consideration.RootConsideration<>ThisConsideration OR Consideration.RootConsideration IS UNDEFINED) AND Consideration<>ThisConsideration AND (Consideration<>ThisConsideration.RootConsideration OR ThisConsideration.RootConsideration IS UNDEFINED)
Note that for the parent and children you have to separately check for undefined because AIM's SQL cannot seem to compare something to nothing. That took me forever to figure out. Is that a bug?

Another thing that wasn't obvious is the process for picking children, which is this:

Code: Select all

PICK ONE OR MORE FROM PickBranchConsiderations
 INSERT OtherConsideration IN ThisConsideration.pm_BranchConsiderations
The reason for "Other" is because Other leaves out "This", which is not a result of the query, but the consideration that the process received into context when it was called (because Consideration was set as the input). It's the consideration you want to relate to your selections. Otherwise the consideration will get related to itself.

Hope that was useful!
v8.1 on Windows 10 / MySQL 5.6 (local), v8.1 on Windows Server 2016 / MySQL 5.6 (server)
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by customaware »

Have not been able to try this out but...

Maybe this would work?

FIND Consideration WHERE Consideration.ID<>ThisConsideration.ID AND Consideration<>ThisConsideration.ob_ParentConsideration AND NOT(Consideration IN ThisConsideration.pm_BranchConsiderations)
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
JonP
Posts: 287
Joined: Thu Feb 16, 2017 9:49 pm
Location: United States

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by JonP »

Tried it. Forgot I tried it, then tried it again. Internal server error. NOT (IN) is a known issue with a workaround of Count=0 or NOT (EXISTS IN).

You might have a good solution for avoiding the IS UNDEFINED condition by using ID. I'll test it tomorrow.
v8.1 on Windows 10 / MySQL 5.6 (local), v8.1 on Windows Server 2016 / MySQL 5.6 (server)
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by tford »

Tom - V8.8 build 3137 - MySql / PostGres
JonP
Posts: 287
Joined: Thu Feb 16, 2017 9:49 pm
Location: United States

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by JonP »

Hi Mark,

No dice on ID eliminating the need for the IS UNDEFINED condition. This really seems like a bug to me. A reference value should be able to be compared to a null value. But at least there's a workaround...
v8.1 on Windows 10 / MySQL 5.6 (local), v8.1 on Windows Server 2016 / MySQL 5.6 (server)
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by customaware »

Jon,

What is the structure of your BO? Is it....?

BO Consideration
ConsiderationName TEXT
ps_RootConsideration Consideration (no Matching Attribute)
pm_BranchConsideration Consideration (no Matching Attribute)
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
JonP
Posts: 287
Joined: Thu Feb 16, 2017 9:49 pm
Location: United States

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by JonP »

RootConsideration and pm_BranchConsideration are matched (which is not easy to do when they are in the same BO). I do have a text attribute, Name, in Consideration.
v8.1 on Windows 10 / MySQL 5.6 (local), v8.1 on Windows Server 2016 / MySQL 5.6 (server)
JonP
Posts: 287
Joined: Thu Feb 16, 2017 9:49 pm
Location: United States

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by JonP »

Thanks for the link, Tom! I'm looking forward to digging around in that demo BSV to get ideas.

That last post about LIST_LINE is gold. I've been meaning to learn more about the list functions.

And I saw the combo-box filters above the column headers in the grid and went, "You can do that?!" AIM has a lot of little gems that I'm still discovering.
v8.1 on Windows 10 / MySQL 5.6 (local), v8.1 on Windows Server 2016 / MySQL 5.6 (server)
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by customaware »

Jon,

How did you manage to get ps_RootConsideration and pm_BranchConsiderations to be matching in the same BO.
I have tried that and could not get it to work.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
JonP
Posts: 287
Joined: Thu Feb 16, 2017 9:49 pm
Location: United States

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by JonP »

For a Hierarchical BO:

1. Add a Parent attribute with Type Hierarchical_BO.
2. Add a Children attribute with type Hierarchical_BO.
3. Save.
4. Go into the Children attribute, set "Matching data" to existing Parent and tick "Multiple allowed".
5. Go into the Parent and set "Matching data" to existing Children.

In both cases the relationship is Peer.

There may be other paths to victory, but I know this one works.
v8.1 on Windows 10 / MySQL 5.6 (local), v8.1 on Windows Server 2016 / MySQL 5.6 (server)
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Selecting Non-Redundant Parent/Children for Hierarchical

Post by customaware »

Ah.... that's right. I had forgotten. You need to create both before saving otherwise it prevents you from doing so.

Thanx
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
Post Reply