[IM] logical operations with relationships in queries

[JensN.]

Hi @all,

we have in IM an Item (Module-Request (MR)), which has subitems (Tasks) linked via relationship. Both item-types have workflows. The tasks can have different task-types:

MR --+-- task (task-type A) (obligatory)

+-- task (task-type B) (optional if task-type C exists)

+-- task (task-type C) (optional if task-type B exists)

+-- task (task-type D) (obligatory)

+-- task (task-type E) (obligatory)

All tasks are linked with the same relationship-field in the MR.The MR has a workflow with many states, and in every state it will append one task.

Now we want to do a query about this: If a MR is in a special state ("InTest") and one or more of the tasks type B or C are linked, and all of the linked tasks (type B or C) are in the same special state ("Finished" or "Invalid"), the the query should find this MR. Oll other tasks (type A or D or E) doesnt matter. With the rule-wizard in the query-builder i didnt find a way to have the logical link between the linked tasks and their state. Here is the "nearest" rule i could find:

Query Definition: ((field["State"] = "InTest") and (relationship["Task_Type"] backwards forwards using ["ModulParent_to_TaskChild"] = "task-type A","task-type B") and (relationship["State"] backwards forwards using ["ModulParent_to_TaskChild"] = "Finished","Invalid") and (field["Type"] = "Module_Request"))

As you can see there are two separated definitions for the relationship "ModulParent_to_TaskChild" in this query, and i didnt find a way to link them together. The result is, that the first relationship-rule (red) gets me all MR with tasks with type B or C, but it doesnt matter in which state they are, and the second (green) gets me all tasks (from a to E) which are in state "Finished" or "Invalid".

How can i bring them together? And i dont need a report, i need the result in a query. Thanks in advance for any hint, and i hope my writing isnt too much confusing

kind regards, Jens

[matt_giltaji]

Hi Jens,

We've run into this exact issue in the past as well. I believe we were added to an RFC, but I don't recall the number.

The workarounds we use are:

1) Query for the Tasks themselves, pulling the MR ID as the value in the TaskChild_to_ModulParent column.

a) This means that you will get duplicate MR IDs if there are multiple tasks meeting the query criteria, so you need to filter those out somehow in whatever you do with the query results

b) This also means that any fields located on the MR will not be available in the query result unless they are FVA'd or copied down to the TaskChild level somehow.

2) Write custom SQL against the DB backend so you can get the INNER JOINs exactly how you need them

a) This works well but does not meet your requirement of getting it in an IM query.

If neither of those works, you could go with a trigger approach. This is probably not very efficient or scalable, but it should get the job done.

1) Create a scheduled trigger which runs against an admin query of either the red or the green queries. As you mentioned, this will pull extra items that you don't want in the end result.

2) In that trigger script, loop over each of the item beans and check the other criteria. (So if you used the red query for the trigger, then check the state of each TaskChild, if you used the Green, then check the Type of each TaskChild)

3) If the item matches the criteria, then save the MR ID into an ArrayList or some other variable.

4) After processing all of the original query item beans, loop over the variable holding the MR IDs to generate a query definition of the form

"((field["ID"] = "X") OR (field["ID"] = "Y") OR (field["ID"] = "Z") OR ...)"

5) Use an im createquery/editquery API call to create/update a query with the new definition

If you needed to do this for multiple sets of queries, it might be better to not hardcode the logic and instead pass in some query names as parameters, execute those queries with im issues API calls and merge the resulting set of IDs. (Google has an open source java library called Guava which is great at this sort of thing. http://docs.guava-libraries.googlecode.com/git/javadoc/com/google/common/collect/Sets.html#intersection(java.util.Set, java.util.Set))

Then create the new query as a long chain of "OR (field["ID"] = "X")".from the results.

Either trigger will be much slower than if we could do this directly against the database, either within the IM Query functionality or by using raw SQL. The query generated by the trigger will also grow stale over time, depending on how much the results of the original queries change, so you will want to rerun it as often as is practical.

Hope that helps,

Matt

[JensN.]

Hi Matt,

many thanks for your really detailed, helpful answer. At the moment we do such things in a similar way, we get one part of the query (green or red) and export the results to excel, where we do the filtering. Some users are doing some very extensive VBA-codings, to start complete queries out of excel. I didnt like this much, because i didnt like the idea of database-engineering with excel, but it seems to be easier for the users than working with integrity-queries and -reports (this is a strong hint to the integrity-devs!!! ).

So we will ask the support to add our problem to this rfc you wrote about.

kind regards, Jens

[KaelLizak]

Hello Jens, Matt,

With regard to multiple IDs, you should have yourself attached to this RFC if you find yourself needing to do this: CS85873: Ability to query on multiple non-consecutive item IDs.

You might also find RFC 108414 useful (no public article). It requests the ability to search within the context of a result set of a previous Integrity query. Was this the one you were thinking of, Matt?

Regards,

Kael

[JensN.]

Hi Kael,

many thanks for your answer. CS85873 seems to not fit really to my problem, nevertheless its a use case we do also have. For this we wrote a little tool, which takes a list of IDs and is building a query-rule around these numbers and passes this rule to integrity.

RfC 108414 sounds also really interesting, but doesnt fit exactly.

kind regards, Jens