Hello everyone,
I am having issues with querying process report which has multiple (different) values in one column. The column is filled with data from a list of CDTs, for example:
I've tried setting it as a list of integers and strings, but either way, when trying to query all rows which have a value "12345" in that column (by using a!queryProcessAnalytics), I get no results.
a!queryFilter( field: "c4", operator: "includes", value: tostring("12345") )
I also tried all different combinatios (in, includes, strings, integers....).
Thanks in advance!
Discussion posts and replies are publicly visible
The way I've handled this sort of thing before is to force the column's data into a flattened string (though usually i'll create a brand new column for this, even if it seems redundant), because once it's a flattened string (instead of list), the query should work on it.
edit: see the example I posted in another comment of mine in this post, here.
The solution to this might be to join on a delimiter - that way, you can just do your "includes" operator on the value concatenated with that delimiter, and it should work. I'll need to double-check whether you can easily do the text flattening in the report configuration to join on a delimiter, but I think this is one of the few functionalities that it actually does support.
e.g. for this array-type CDT PV, i just join them in such a way that every value ends up included in [square] brackets - meaning an "includes" would merely need to concat square brackets to your value as well to safely find the whole value you want.
With tostring(), you do the text flattening with semicolon as a delimiter. anak3061 wants the indexes where the search text 12345 exists as a value and is not part of it, like 123456.
I already handled this in the reply I just posted a minute or so before yours - but now I'm waiting for it to be un-spam-blocked by the admins
unfortunately the process report's handling of list data (particularly when querying with operators that would usually work with a list) is very brittle. i've found it to be basically non-functional unless you use workarounds like i discuss elsewhere in this thread.
I get the point what you want to achieve but again you will have to do an additional filtering for getting the exact matching values of the requested search text.
The "includes" filter option is similar to a "like" in a query. But he needs the equal value here.
I fear you're not quite understanding the full capability of the solution I've detailed here. Sorry if my description thus far has been misleadingly vague or something.
The array is concatenated such that the value ends up like "[123][1234][234][456]" (etc). See my previous configuration screenshot for the expression code used to generate this string.
When querying, you use "includes", and the value you pass into the filter is done thus:
operator: "includes",value: "[" & ri!myValue & "]"
This will find exact matches, as I previously said.
Yes, you are right. Will work also