Help with IN() expression

Hi.

I am trying to compile a conditional expression for the system generated ‘Edit’ action. What I want to achieve is to allow all users to edit records where [Signature] is blank but if the user is an administrator they can edit records with a [Signature] value. The Administrator flag for each user is held in Employees Table.

What I have so far is
OR(
ISBLANK([Signature]),
AND(ISNOTBLANK([Signature]),
IN(“Yes”, Employees[Administrator])
))

Doesn’t seem to be working. I know I am missing something but not quite sure how to update expression accordingly.

Any help would be great, thank you.

Try:

OR(
  ISBLANK([Signature]),
  LOOKUP(
    USEREMAIL(),
    "Employees",
    "Email",
    "Administrator"
  )
)

replacing Email with the name of the column in the Employees table that contains the user’s email address.

Would this work? The lookup doesn’t return a Boolean.

If the Administrator column is a Yes/No value, my expression should work; otherwise you’re correct, it won’t without modification.

Hi @Steve

I have set the [Administrator] column is Yes/No type with all values in that column being TRUE or FALSE but the expression doesn’t appear to be working.

I still seem to be able to ‘Edit’ as a user with a ‘FALSE’ value in the [Administrator] Column.

In the app editor, go to the column’s configuration, to the Editable? property, into Expression Assistant. Replace the existing expression with just the LOOKUP() subexpression:

LOOKUP(
  USEREMAIL(),
  "Employees",
  "Email",
  "Administrator"
)

then click the Test button. Do you get the results you’d expect (they should all be the same)?

All the results are the same. Condition result is ‘N’. I changed the [Administrator] value for me to True before I tested the expression so I pressume all the results would be showing Y if the expression was working.

Would that be correct?

Yes.

Does the Employees table have a column named Email that contains the user’s login email address?

Please post a screenshot of the Edit action configuration screen.

Yes the employee table has [User Email Address]

You used

OR(
ISBLANK([Signature]),
AND(ISNOTBLANK([Signature]),
IN(“Yes”, Employees[Administrator])
))

After doing all this below I found that all that may be required is to use. Try if not read the rest as it also works.

OR(
ISBLANK([Signature]),
AND(ISNOTBLANK([Signature]),
IN(LIST(“Yes”), LIST([Administrator]))
))

The problem is that the Employees[Adminstrator] contains either YES or NO so you might have

{“YES”,“NO”,"YES,“NO”…}

So the issue is that there will be multiple YES’s and multiple NO’s so how does the IN() function determine when there are repeated values. So the IN function only determines whether a single YES exists and returns YES but not Y/N for each row as shown below.

What you are looking for is to find every value in Employees[Administrator] which has “YES” as they are administrators. This may be done with INTERSECT(LIST(“YES”), Employees[Administrator]) but which is a LIST and not a boolean. So now you need to produce a boolean from this list.

If you use IN (LIST(“YES”),LIST([Flagged])) you get

Which is what you require.

You already know that INTERSECT will return a list all YES values so now you need a function which confirms this and converts this to boolean

OR(
ISBLANK([Signature]),
AND(ISNOTBLANK([Signature]),
CONTAINS(INTERSECT(LIST(“YES”),Employees[Administrator]),"YES"
))

So the INTERSECT filters all values which have YES and the CONTAIN confirms that each and every list value returned is indeed “YES” and you get a single boolean return value. So it confirms that each and every value in the array / list is “YES”.

Adding the CONTAIN is a redundant step but it serves to return a BOOLEAN from a LIST of identical values.

CONTAIN() is case sensitive so YES <>= Yes for example.

I quickly tested this and this is what I got

This is just to illustrate why seemingly simple expressions and logic do not function as one would expect.

It appears to work but note that in the CONTAIN I had to make each variable of type LIST.

Thus the above would then become

OR(
ISBLANK([Signature]),
AND(ISNOTBLANK([Signature]),
CONTAINS(LIST(INTERSECT(LIST(“YES”)),LIST(Employees[Administrator]),“YES”)
))

Note that APPSHEET requires incremental debugging of expressions as return values may change so one has to save an incorrect expression to reflect the correct type and then it works.

INTERSECT(LIST(“YES”),LIST([Flagged]))

Returns

Which is correct as it only identifies rows in column Flagged that are YES and all other are blank.

But it is of type LIST and you require BOOLEAN

Now if you add CONTAIN it gives error as it requires type YES/NO


Now save and change to Type Y/N and you get

and the result is

Which is what you wanted and which return a BOOLEAN for each row with YES.

As Appsheet returns different types one has to take care of how one develops the expression.

Now if I add a SELECT it returns just the rows with YES

SELECT (park[Primary Key],CONTAINS(INTERSECT(LIST(“YES”),LIST([Flagged])),“YES”))

You get the same result using SELECT(park[Primary Key],IN (LIST(“YES”),LIST([Flagged])))

Note that using Employees[Administrator] and [Administrator] gives different results when using IN()

1 Like

Thanks for the screenshots.

At this point, I’d have to think the email address you have in the Employees able is not the one you’re using with the app editor.

1 Like

Hi @Gregory_Diana

Many thanks for such a detailed response. Unfortunately you first suggestion hasn’t worked and using the alternative expression
OR(
ISBLANK([Signature]),
AND(ISNOTBLANK([Signature]),
CONTAINS(INTERSECT(LIST(“YES”),LIST(Employees[Administrator])),“YES”)
))

is returning the following error. Just to note my [Administrator Column] is type Yes/No.

Could you advise what I am doing incorrectly?

Many thanks.

@MauriceWhelan
If your [Administrator] column is Yes/No type, the INTERSECT expression cannot evaluate it because you cannot compare a LIST type with a Y/N type.

I might advise changing that INTERSECT expression a bit and substitute that LIST(Employees[Administrator]) with this:

SPLIT(CONCATENATE(Employees[Administrator]),",")

Hi @LeventK.

Thank you for that. Can I confirm the expression should now be

OR(
ISBLANK([Signature]),
AND(ISNOTBLANK([Signature]),
CONTAINS(INTERSECT(LIST(“YES”),SPLIT(CONCATENATE(Employees[Administrator]))),",")
))

If so I still don’t appear to be able to edit records where [Signature] is not BLANK.

@MauriceWhelan, let’s take a step back. When you say it doesn’t work, what behavior are you seeing? Can a screenshot illustrate the undesired behavior?

1 Like

The real problem here, is that I’m pretty sure these CONTAINS and INTERSECTs aren’t going to be helpful. Nowhere in this expression are we comparing the USER who is logged in attempting to make the edit, to their defined “Administrator” value. So, @Steve has been going down the right road, and I agree with him that it seems one way or another, somehow the email value of the logged in person isn’t matching whatever email is listed in your table. So once that is solved, this should be full expression that does the job.

OR(
    ISBLANK([Signature]),
    AND(
        ISNOTBLANK([Signature]),
        LOOKUP(USEREMAIL(), "Employees", "User Email Address", "Administrator")
    )
)
1 Like

Hi Maurice,

Yes it appears the Employees[Administrator] type being BOOL is the problem. I used text in the Google Sheet.

May I suggest that you define Employees[Adminstrator] as text in the Google Sheet rather than BOOL and see if it helps. One may use dfferent types in the GS column and APPSHEET column.

Alternatively, if you send me a reasonable number of rows from your entire spreadsheet I can see what I can do. APPSHEET is very sensitive to TYPES.

Try just getting the intersect to work just by itself as it is the source of the error

INTERSECT(LIST(“YES”),LIST(Employees[Administrator]))

Cheers

Greg

Also you can create a VIRTUAL column say AdminText and use the expression

Employees[Administrator] and it will convert all YES/NO to TEXT. The use

INTERSECT(LIST(“YES”),LIST([AdminText]))

@Steve

The behaviour I am hoping to see is the edit icon available for records even where [Signature] is not blank.

The first screenshot is a record that contains a value for [Signature] and the second is one where [Signature] is blank.


The USEREMAIL and [User Email Address] comparison is really confusing me because they definitely have the same value.

Are you sure you’re working with the Edit action for the right table?

Definitely.