Expand my Community achievements bar.

SOLVED

In/Not In Operator

Avatar

Level 3

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)

0694X00000CJRGVQA5.png

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

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Level 4

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.

View solution in original post

12 Replies

Avatar

Level 10

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);

0694X00000CJRIlQAP.jpg

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.

Avatar

Correct answer by
Level 4

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.

Avatar

Level 3

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

Avatar

Level 3

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

0694X00000DTQy1QAH.png

Avatar

Level 4

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:

0694X00000DTRKMQA5.pngHere 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.

Avatar

Level 3

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:

0694X00000DTRXfQAP.pngThis appears to work as expected and an example of the bundles passing through is here:

0694X00000DTRYJQA5.png

Array / Contains Filter:

0694X00000DTRYsQAP.png

This doesn't appear to allow anything through:

0694X00000DTRYxQAP.pngSo 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

Avatar

Level 4

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.

0694X00000DTT5oQAH.png0694X00000DTTCtQAP.png

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.

Avatar

Level 4

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

Avatar

Level 4

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

Avatar

Level 3

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"

0694X00000DVRrAQAX.png

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.

Avatar

Level 4

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