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:
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:
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:
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!
