Check if a particular value is present in a field's list of values

[Anonymous]

Hi Experts,

I have a "User" field having list of users. I want  to compare the OSUser() with the "User" field if the OSUser is present in that list of users or not.

e.g.

if OSUser()= domain\xyz and xyz is in user list then flag it as 'Y'

if OSUser()= domain\def and def is not in user list then flag it as 'N'

Help on this is very much appreciated.

Thanks!

[neelamsaroha157]

Use this

if(Match(User, OSUser()),'Y','N')

[Anonymous]

Thanks neelamsaroha1575 for your reply.

I do not want to have flag for each user, instead I just want to see that if the OSuser is available in the user list or not in overall. It should simply show me 'Y', if the OSuser is in the list else nothing.

Sorry for the confusion.

[vishsaggi]

Extending neelimas expression, you can take 'N' from the IF so it should be

if(Match(User, OSUser()),'Y')

and if there are any nulls suppress them accordingly.

[johnw]

Here are a few ways. I can't help thinking I'm overcomplicating.

if(index(concat(User,','),osuser()),'Y','N')

maxstring(aggr(if(User=osuser(),'Y','N'),User))

if(min(aggr(User=osuser(),User)),'Y','N')

[Anonymous]

Thanks all for your reply and efforts. I have found some similar scenario in below post and will try to do as per that.

Re: Find In Field

Thank you!!

[johnw · Accepted solution]

I think I was overcomplicating. I think this should work.

if(min(User=osuser()),'Y','N')

[Anonymous]

Perfect!!

This is what I wanted. But unable to understand how min() helping in comparing the users, could you please make me understand on this?

Thank you!!

[johnw]

Unless only a single User is selected, then there are multiple possible values of User, and so if you reference User, you'll get null. So this:

if(User=osuser(),'Y','N')

Would be evaluated like this:

if(null=osuser(),'Y','N')

The condition is always false, so you'd always return 'N'.

There are a couple key things to understand to get around this problem. First, that since User can have multiple values, we can use an aggregation expression, even without using an aggr() function. Second, a true condition evaluates to -1, while a false condition evaluates to 0.

And so:

min(User=osuser())

Is saying:

If ANY of the possible users match the osuser(), return -1 (true), else return 0 (false).

And we can then check that value in the if() as we could any other boolean value to assign a 'Y' or 'N'.

The expression only checks possible Users, though, in the sense that if the full list is 100 users, but only 5 are currently selected or otherwise possible based on other selections, it would only check the 5. If you want to check the entire list regardless of selections, I believe you'd want this.

if(min({1} User=osuser()),'Y','N')

[johnw]

Perhaps a clearer way to write it would be this:

maxstring({1} if(User=osuser(),'Y','N'))

Then we're not taking advantage of knowing that true is -1 and false is 0.

Edit: on the down side, it's somewhat more code to plug in to other places we might need a Boolean value, as it would then be this:

maxstring({1} if(User=osuser(),'Y','N'))='Y'

vs. this:

min({1} User=osuser())