Hey folks, This has to do with an expression...

expressions
(Miranda Lubarsky) #1

Hey folks,

This has to do with an expression to render a drop-down list.

I have a column, [Employee Name] in a form my employees are filling out.

This has an initial value and a ref to a list of employee names.

Then there is a second column called [Co-gardener] for the option of listing another employee’s name.

However, I would like to make sure that the list of employees that comes up in the drop down list will not allow people to select the same person as was selected in the [Employee Name] column.

I have tried this expression:

AND(SELECT(Employees[Employee Name], [Crew] = “Maintenance”), NOT([Employee Name] = [_THIS]))

But it does not validate.

Funny thing is, the SELECT expression on its own validates, and the NOT expression on its own validates, but not the two together.

Am I using the AND expression wrong to bind them together?

Any ideas on what I’m doing wrong?

Many thanks! Miranda

#2

I think you can do this in this way:

SELECT(Employees[Employee Name], AND([Crew] = “Maintenance” , NOT([Employee Name]=[_THISROW].[Employee Name])))

You’ll create a list of all the employee names that are not equal to name in the first column.

(Steven Coile) #3

SELECT(Employees[Employee Name], [Crew] = “Maintenance”) does not produce a Y/N value, but AND() wants a Y/N value, hence the validation failure. @Stan’s suggestion is the right one.

(Miranda Lubarsky) #4

@Stan @Steven_Coile thank you!! Success!!