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.

0 34 1,254
34 REPLIES 34

Steve
Participant V

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.

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.

Yes the employee table has [User Email Address]

gregdiana1
Participant V

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()

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.

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
Participant V

@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?

@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.

If the Edit action Only if this condition is true is set to only:

ISBLANK([Signature])

Does that work?

Yes that works.

Let’s try this:

ISNOTBLANK(
  FILTER(
    "Employees",
    (USEREMAIL() = [User Email Address])
  )
)

This should allow you to edit any row.

Yes that allows me to edit any row

Are any User Email Address column values blank? If so, LOOKUP() might be returning the Administrator column value from the first row it finds with a blank User Email Address value. If so, we’ll have to work around this. Instead, try:

OR(
  ISBLANK([Signature]),
  ANY(
    SELECT(
      Employees[Administrator],
      (USEREMAIL() = [Email])
    )
  )
)

There are no rows with a blank [User Email Address]. There were 3 without an [Administrator] value which I have subsequently set to ‘FALSE’ anyway.

The expression above is allowing me edit all records.

Correctly, or incorrectly?

Incorrectly if it should be doing so only if the value in [Administrator] for the logged in user is TRUE. I changed the value from TRUE to FALSE and it still allows all records to be edited.

Are you testing this with the same email you use to log into the editor? Because if that’s the case, if the table is editable at all by anyone, I believe you (as author) are ALWAYS allowed to edit. Which means you should be testing all of this with a completely separate email, if you haven’t been already.

@Bahbus
I am not sure this is the case because I had the condition set initially to ISBLANK[Signature] and I couldn’t edit records where [Signature] had a value.

Could be. Actions sometimes work differently, than Editable If conditions on the column itself.

Want me to take a look? sc2758g@gmail.com

That would be great @Steve, thank you. I have granted access as co-author.

I’ve spent some time looking at it and tinkering (no changes were saved). I can’t find any explanation for the odd behavior. All the expressions I tested worked as expected–except in the app itself.

Looks like a bug to me. I recommend engaging support@appsheet.com on this one.

Sorry I couldn’t help more.

No problem @Steve. Many thanks for the support on it.

Bahbus
Participant V

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")
    )
)
Top Labels in this Space