In/Not In Operator

[JonathanTh]

Hi All

So i find myself a bit stuck with using the "In" operator.

Fusion 2.0 suggests i should do this as i want to find all Projects that are either "AWA"" or "PLN" (I am aware my screenshot only shows "PLN" at present)

What i cant seem to figure is how to do this in the above module? There will be several other "AND Rules" statements to follow this.

I have written countless API queries and always end up going down the road of something like:

proj/search?status=AWA&status=PLN&status_Mod=in

So how can i replicate this using the "In" operator as above?

Thanks

[CiaranCa]

Hi Jonathan, that's a great question and something I ignored possibly to my detriment, I do use "OR" to split out duplicate fields in a search criteria (or more often the custom API card if it's a large criteria);

This would produce the below query to WF api, which does look like an efficient search;

/PROJ/search

{

....

"actualCost": "1",

"actualCost_Mod": "eq",

"status": "PLN",

"status_Mod": "eq",

"OR:1:actualCost": "1",

"OR:1:actualCost_Mod": "eq",

"OR:1:status": "AWA",

"OR:1:status_Mod": "eq"

}

There is probably good reason they have placed that message in the module, maybe @Darin Patterson - inactive‚ could weigh in.

[darin_patterson · Accepted solution]

Hi everyone,

This is a great question and you are so close to the right answer.

When you use the "in" operator in the Workfront Search module, Fusion is expecting to compare the field to an array of possible values.

In other words, to find a list of projects with the status of "AWA" or "PLN" you wold compare Status with "IN" and then use the following formula:

{{add(emptyarray; "AWA"; "PLN")}}

(you can, of course, include additional values in the array as necessary as well).

I've included a screenshot as well.

The advantage of this approach over using "OR" is you can combine multiple complex criteria more effectively (and not have to repeat common criteria).

Cheers.

[JonathanTh]

Hi Darin

Thanks for this, just what i needed! This will make things a whole lot easier as you say for not having to repeat common criteria!

Thanks again

[JonathanTh]

‚ I have one question off the back of this.....

This is great for the search module but can something similar be used on a filter between modules? The reason being i also face the same issue with repeating common criteria.

An example to support this ask would be:

Category ID = [Category ID 1]

-and-

Last Updated By != [User 1]

-OR-

Category ID = [Category ID 2]

-and-

Last Updated By != [User 1]

It would obviously be much easier (especially if there a numerous Category ID's) to have a filter that says:

Category ID IN [{Category ID 1};{Category ID 2};........{Category ID X}]

-and-

Last Updated By != [User 1]

Thanks as always

UPDATE: I may have been overthinking this....... if i take it back to the example of PLN or AWA i am assuming this would be correct:

[darin_patterson]

Hi Jonathan,

The same general principle can be applied here with an array of items to compare to. In some unfortunate labeling / terminlogy though...you flip the concept. Rather than saying something "IN" some array. You would say some array "contains" some value.

Here's the example:

Here you can see the array is formed similarly to the WF Search card: {{add(emptyarray; "categoryID1"; "categoryID2"; "categoryID3")}}

and then the comparison is a "contains" comparison (the array comparisons are actually quite powerful in filters).

I don't *think* that your second thought of doing "AWA" "or" "PLN" would actually work. That "or" is designed to separate expressions and returns true or false.

[JonathanTh]

Hi Darin

This is interesting as the reason I did this is because i had initially tried with the array but with no success. I've detailed out the two methods and an example of the results returned:

OR Filter:

This appears to work as expected and an example of the bundles passing through is here:

Array / Contains Filter:

This doesn't appear to allow anything through:

So this is why i initially thought i was over complicating things.......

Appreciate your time on this one, and hopefully you will be able clear up what the correct method is for the filter module (and point out what i am doing wrong in terms of using the array!)

Thanks

[darin_patterson]

Hi Jonathan ,

That is strange -- my testing does not show that and I can't fully see your testing because I can't see what values actually existed for "status".

For testing purposes only -- I would create a simple scenario with two nonsense modules, then experiment with the filter between them. In this case, you'd use directly written values rather than mapping them to a previous module's values.

When I do that, I can see the "contains" array approach work. But the "or" approach only "deceptively works" when the value happens to match the first value before the "or" (it doesn't match subsequent ones).

Right now, I'm guessing that you may have selected the "text operator contains" comparison rather than the "array operator contains" comparison. See screenshot of both.

If that doesn't bring clarity, then send me an email with a link to your scenario execution and I'll take a closer look.

[stuartcairns]

Hi Darin, I'm trying the same thing in a filter between two modules. However, there is no IN operator available. Any advice on which operator can take the add(emptyarray;pln;cur;) etc statement ?

Thanks v much

[stuartcairns]

I just used an OR from the builder and it worked ok. :-)

[stuartcairns]

ok, it didn't work. Bad day :-)

[JonathanTh]

Hi Stuart

For the filter module you use "Contains", however what I was doing wrong was using it the wrong way around.

I had my filter set as "Status" Contains "[ARRAY]" where it should have been "[ARRAY]" contains "Status"

I also had the OR function working, however it turns out it wasn't working and this was Darin's explanation:

The execution was using the “Boolean operator: Equal To”

A Boolean comparison will used against a non-boolean field type like this will basically revert to standard javascript that determine if any value exists at all. Essentially, this filter was always passing regardless of the value in status.

If the filer was a “text operator: Equal to” we would see the behavior where it only looks at the first one.

In summary: the or function should only be used to compare two different full expressions and shouldn’t’ be used for filtering like this.

[stuartcairns]

Hi Jonathan. Thanks v much for that. I had tried the CONTAINS but I think I fell in the same trap as you. I'll follow your lead. All the best, Stuart