Using Valid_If and Suggested_Values combined

[SKETCHwade]

Hey All,

I am trying to use a column that incorporates both Valid_If and Suggested_Values combined.
The suggested Values should provide an Enum List. The Valid If should determine if another column = X then the value must be in the suggested values list. If the other column = Y then this column is Valid as any text value.

Here Is some images of what I am doing to visualise it:

Unfortunately the above does not work the way I intended. I tried removing the ANY() from the select and wrapping the TRUE in LIST() but these sets the enum list to blank and if the position column = Subcontractor then it gets set to TRUE.

[Suvrutt_Gurjar]

Please try below

In the suggested values , please add an expression of 

IFS([Position] <>"Subcontractor",  SELECT(DATA[Employees] , TRUE),

         [Position] ="Subcontractor", LIST("")

         )

In the valid_if of the column , please try an expression of 

IFS([Position]<> "Subcontractor", AND(COUNT([_THIS])>0, COUNT([_THIS])=COUNT(INTERSECT([_THIS], SELECT(DATA[Employees] , TRUE)))),

[Position]= "subcontractor", TRUE
)

Please test well.

Edit: Made some changes to the expression.

[SKETCHwade]

Hey @Suvrutt_Gurjar Unfortunately it says the COUNT() was used incorrectly. Maybe my Employee column is not the right Type. Its Enum with a base type of Text.

[Suvrutt_Gurjar]

---

@SKETCHwade wrote:

Maybe my Employee column is not the right Type. Its Enum with a base type of Text.

---

Yes, you are correct. I gave solution for an enumlist type column by mistake. Sorry about that. Please try in valid_if of the column

IFS([Position]<> "Subcontractor",  IN([_THIS),  SELECT(DATA[Employees] , TRUE)),

[Position]= "subcontractor", TRUE
)

Please retain suggested values expression as follows.

IFS([Position] <>"Subcontractor",  SELECT(DATA[Employees] , TRUE),

         [Position] ="Subcontractor", LIST("")

         )

[Suvrutt_Gurjar]

Hello @SKETCHwade ,

Would appreciate if you let us know if the solution worked for you. This will help any future reader of the post thread with a similar requirement.

[Deryck]

if you want make it posible for "add or search", you can change column data type to "text", remove valid if, and keep Suggested values