Why not just use IF()???

1minManager · 12-02-2021 08:52 AM

I’m using the following as an Are Updates Allowed? rule:

IF(
	OR(
		IN("Manager",INDEX(CURRENT_User[Type],1)),
		IN("Admin",INDEX(CURRENT_User[Type],1))
	),"ADDS_AND_UPDATES",
IF(
	ISBLANK(UserEmail()),"ALL_CHANGES",
	"READ_ONLY"
))

but when I try to edit a record I get:

Table 'Plot' does not allow this operation. Expression result: 
'READ_ONLY'. 
Expression Trace: 
{"key": "IF(OR(IN(\"Manager\",INDEX(CURRENT_User[Type],1)), IN(\"Admin\",INDEX(CURRENT_User[Type],1))),\"ADDS_AND_UPDATES\",IF(ISBLANK(USEREMAIL()),\"ALL_CHANGES\",\"READ_ONLY\"))","result": "READ_ONLY","values": []}

CURRENT_User is simply a slice to not have to use ANY(Select(… multiple times

I’ve tested the formula in UserSettings and as a whole I get a result of “ADDS_AND_UPDATES” and INDEX(CURRENT_User[Type],1)) gives me “Admin”.

So I’m a bit stuck as to how its clearly getting “READ_ONLY” at the table level

SkrOYC

CONCATENATE() should help

View solution in original post

Marc_Dillon

I’m surprised it’s not returning an error. The second argument of IN() should be a List, INDEX returns a single value.

SkrOYC

Is [Type] EnumList?

SkrOYC

If [TYPE] is Enum

IFS(
  INDEX(CURRENT_USER[TYPE], 1) = "Manager", "ADDS_AND_UPDATES",
  INDEX(CURRENT_USER[TYPE], 1) = "Admin", "ADDS_AND_UPDATES",
  ISBLANK(USEREMAIL()), "ALL_CHANGES",
  0=0, "READ_ONLY"
)

If [TYPE] is EnumList

IFS(
  IN("Manager", SPLIT(CURRENT_USER[TYPE], " , "), "ADDS_AND_UPDATES",
  IN("Admin", SPLIT(CURRENT_USER[TYPE], " , "), "ADDS_AND_UPDATES",
  ISBLANK(USEREMAIL()), "ALL_CHANGES",
  0=0, "READ_ONLY"
)

1minManager

Yeah, sorry [Type] is a ENUMLIST

Though you maybe onto something. For my record it only returns “Admin”. I’ll see what happens if there is more than 1 item in [Type]

1minManager

Nope doesn’t work if [Type]=“Admin,Manager”

I’ll try @SkrOYC suggestion

SkrOYC

Check your EnumList config so that the Item Separator is configured as default
" , " (space - comma - space) to prevent problems in the future

1minManager

Item seperator was missing. Added comma. Made no difference.

SkrOYC

Make sure item separator is exactly Space - Comma - Space
Also the values on Type column inside the database should be this way:
Admin , Manager
Instead of
Admin, Manager or Admin,Manager
Just make sure the item separador is the string between the first and the second option

Marc_Dillon

Well, if you’re just going to use SPLIT(), maybe not.

SkrOYC

Then

IFS(
  IN("Manager", SPLIT(CONCATENATE(CURRENT_USER[TYPE]), " , "),
    "ADDS_AND_UPDATES",
  IN("Admin", SPLIT(CONCATENATE(CURRENT_USER[TYPE]), " , "),
    "ADDS_AND_UPDATES",
  ISBLANK(USEREMAIL()),
    "ALL_CHANGES",
  0=0,
    "READ_ONLY"
)

1minManager

IFS(
  IN("Manager", SPLIT(CURRENT_USER[TYPE], " , ")), "ADDS_AND_UPDATES",
  IN("Admin", SPLIT(CURRENT_USER[TYPE], " , ")), "ADDS_AND_UPDATES",
  ISBLANK(USEREMAIL()), "ALL_CHANGES",
  0=0, "READ_ONLY"
)

No Edit Button

IFS(
  IN("Manager", SPLIT(INDEX(CURRENT_USER[TYPE],1)," , ")), "ADDS_AND_UPDATES",
  IN("Admin", SPLIT(INDEX(CURRENT_USER[TYPE],1)," , ")), "ADDS_AND_UPDATES",
  ISBLANK(USEREMAIL()), "ALL_CHANGES",
  0=0, "READ_ONLY"
)

No Edit Button

Tested IN(“Admin”, SPLIT(INDEX(CURRENT_USER[TYPE],1),",")) as a UserSettings formula. Result was TRUE

SkrOYC

CONCATENATE() should help

1minManager

IFS(
  IN("Manager", SPLIT(CONCATENATE(CURRENT_USER[TYPE]), " , ")), "ADDS_AND_UPDATES",
  IN("Admin", SPLIT(CONCATENATE(CURRENT_USER[TYPE]), " , ")), "ADDS_AND_UPDATES",
  ISBLANK(USEREMAIL()), "ALL_CHANGES",
  0=0, "READ_ONLY"
)

Works. Totally not intuitive. But it works. Thanks @SkrOYC

Also love the

0=0, "READ_ONLY"

to give an IFS() a default condition. Definately be nicking that one

MultiTech

@1minManager

Why not just use IF()???

I’m weary of the `0=0` statement - which is always true.

What happens if things don’t process linearly like we’re expecting?

SkrOYC

They invented Bug Reports for that

1minManager

@MultiTech_Visions I’m thinking more of a solution where you’ve got a formula with 5-10 nested IF()'s. Using an ISF() with this last row just looks neater. Appreciate its not more functional

Steve

SkrOYC

That’s the magic of CONCATENATE()
It solves all the mess that we could have made inside Item Separator and help SPLIT() make it’s job

1minManager

IFS(
  IN("Manager", SPLIT(CURRENT_USER[TYPE], " , ")), "ADDS_AND_UPDATES",
  IN("Admin", SPLIT(CURRENT_USER[TYPE], " , ")), "ADDS_AND_UPDATES",
  ISBLANK(USEREMAIL()), "ALL_CHANGES",
  0=0, "READ_ONLY"
)

No edit button

  IN("Manager", SPLIT(INDEX(CURRENT_USER[TYPE],1),",")), "ADDS_AND_UPDATES",
  IN("Admin", SPLIT(INDEX(CURRENT_USER[TYPE],1),",")), "ADDS_AND_UPDATES",
  ISBLANK(USEREMAIL()), "ALL_CHANGES",
  0=0, "READ_ONLY"
)

Same error message

  IN("Manager", LIST(INDEX(CURRENT_USER[TYPE],1))),"ADDS_AND_UPDATES",
  IN("Admin", LIST(INDEX(CURRENT_USER[TYPE],1))),"ADDS_AND_UPDATES",
  ISBLANK(USEREMAIL()),"ALL_CHANGES",
  0=0, "READ_ONLY"
)

No edit button

1minManager

Odd thing is, why if I can see the edit button, I can’t edit a record. When the rule is supposed to apply to the entire table?

Aurelien

@1minManager
I suspect this is related to this kind of situation

1minManager

Would

IFS(
...
TRUE, "READ_ONLY"
)

not work just as well?

SkrOYC

Yep, it should.
I do logic (0=0, 1=1 | 0<>0, 1<>1) because my main language is Spanish and the True, Verdadero, Yes, Sí, is a little bit messy.
Logic stuff is better from my POV since the platform decides how the hell it wants to save the value in the database while I get the results.
For example, I use “True” as Initial Value and the platform saves that as “Verdadero”. It’s counterintuitive. I preffer logic in this case

Are Updates Allowed? rule not working

Why not just use IF()???

I’m weary of the 0=0 statement - which is always true.

I’m weary of the `0=0` statement - which is always true.