IFNOTBLANK Help required.

[Domearian]

I'm having a problem with the following code: IFNOTBLANK (User[Location]) CONTAINS([Local], User[Location])

If the user location is blank, it shows nothing, which is what I want. But, if it is not blank I want it to run CONTAINS([Local], User[Location])

Help is appreciated.

[WillowMobileSys · Accepted solution]

First, just make sure it's understood, the expression User[Location] returns a LIST of values

There is a common misunderstanding of the usage of CONTAINS().  This function is meant for checking if a text string has some sub-string included within it.   While it can work with a LIST of values in many cases,  there are situations when it simply will not.

To be safe, whenever you want to check if a LIST contains a value. you should use the IN() function.

Try using this expression below instead.  You do not need the check for ISNOTBLANK().  If it is blank, the below expression will return FALSE:

IN([Local], User[Location])

IN()

CONTAINS()

[Steve]

There's no such thing as "IFNOTBLANK".

[Domearian]

It shows in the Row Filter Conditions. As my question is as Yes/No question, ie: Is it Blank? If its is, be blank, if it isn't, do this.

[Steve]

Oh, ISNOTBLANK, not IFNOTBLANK. Got it.

Your expression:

ISNOTBLANK (User[Location]) CONTAINS([Local], User[Location])

is invalid. You probably want an AND() or OR() in there.

AND() - AppSheet Help
OR() - AppSheet Help

[Domearian]

I've tried AND and just couldnt get it to work.

[WillowMobileSys · Accepted solution]

First, just make sure it's understood, the expression User[Location] returns a LIST of values

There is a common misunderstanding of the usage of CONTAINS().  This function is meant for checking if a text string has some sub-string included within it.   While it can work with a LIST of values in many cases,  there are situations when it simply will not.

To be safe, whenever you want to check if a LIST contains a value. you should use the IN() function.

Try using this expression below instead.  You do not need the check for ISNOTBLANK().  If it is blank, the below expression will return FALSE:

IN([Local], User[Location])

IN()

CONTAINS()

[Domearian]

And that Willow, is just the ticket. Performs everything I want it to do. Thank you.

[Domearian]

Have found an issue.  What I would like to do is the following: Test User 1, when they login, I want the User Location to only display the Local (Menu Item) that equals their location. So HPC will Show HPC Menu Item only. If there is no location set against the user, I want it to show nothing. 

[WillowMobileSys]

Ok, so now we are getting deeper into your need which requires a better understanding of your data.  

I do understand you want to get a list of valid Menu Items for a user and that users' assigned Location.  What you will to need to do is apply filtering, using a FILTER() or SELECT() function, to return a subset of rows.  

There are a couple of unknowns:

• How do you identify the logged in user and match that to the User table?
• How does app know the logged in user's location

For the logged in user, if you are using AppSheet's login capability you will have available the USEREMAIL() function that returns the logged in users email.  You would need to have a column in the User table with this login email to match with the USEREMAIL().

With all of the above, you would change the IN() function as follows (note I have assumed the "Login Email" column is included in the User table):

IN([Local], FILTER("User",  AND([Login Email] = USEREMAIL(), [Location] = <<users location>>))

[Domearian]

Hi Willow, I would like to resolve this "IN" with your help. Your solution has resulted in an error. So let's clarify my data (Google Sheet x 2. 1st is Menu, 2nd is Users. I have attached some screen grabs).

So I want the user location to set what is filtered in the "SetLoc" only part of the Menu. The idea behind it is if a User does not have HPC set in the location, it shows nothing. If it does show HPC then it shows that part of the menu. A User may have HPH set as a location, and if there is part of the "SetLoc" menu that has HPH, it only shows the HPH.

The User is matched via it's logged in address (USEREMAIL), and this then helps identify by the Users data, what location he is at.

Let me know your thoughts.

[WillowMobileSys · Accepted solution]

This helps.  But it seems maybe we still don't have a complete picture of things.  The IN() function is being used in the Menu table for some decision but we don't know what. And we don't know how or if the other Menu Items should play a part in this expression as well

I suggested IN() function above based on the attempt to use CONTAINS(), but with what I understand of the data now, I wonder if that was incorrect.  There still are some gaps in the understanding but for now, it seems the better expression would be: 

[Local] = ANY(SELECT(User[Location], [Email] = USEREMAIL()))

You can use LOOKUP() instead of the ANY(SELECT()), which you did in a prior post.

If Local is blank then it will only match the user if the User location is also blank, which seems maybe to fit your needs?  I am just not sure if there needs to be additional criteria inserted by Menu Type as you did in the LOOKUP() you provided earlier.

In the off-chance, that you have multiple rows per user in the User table, then the expression would require IN() and would be:

IN([Local], SELECT(User[Location], [Email] = USEREMAIL()))

A few other things:

1) The "<<users location>>" i had in my sample expression was just a place holder.  It's not a valid expression construct ... but I should have said so.

2)  When you use a construct such as "User[Location]" , where "User" is a table and "Location" is a column, it returns a LIST of values.  Thus its not valid to use it in an expression like this "User[Location] = something".

3)  Now seeing your data, I can say the FILTER expression cannot work in this case and a SELECT must be used.  FILTER() only returns row references - i.e. it returns a list of key columns...always.  In your case, for the IN() function, you want a list of values returned similar to the [Local] column - i.e. a list of Location values.  FILTER does not allow you to choose which column values are returned but SELECT does.

[Domearian]

Hi Willow, Thank you for explaining that for me. Made perfect sense and helped me understand where I was going wrong.

I used the second option:

IN([Local], SELECT(User[Location], [Email] = USEREMAIL()))

 It is very simple and has worked a treat. I have taken note of your explanation so hopefully I wont get caught up like that next time.

Thank you again! 😀😁🙏

[Domearian]

Hi Willow, Thanks for getting the solution. I'd like to take it a bit further. If they haven't got assigned a site, i'd like it to do something like assign a default site.

IN([Local], SELECT(Users[Location], [Email] = USEREMAIL())) ELSE Users[Location]="None"

Not returning anything. Any ideas?

[WillowMobileSys]

You are mixing types of expressions which won't work.  The IN() expression is a TRUE/FALSE test which means you are likely using it in a context that requires a Yes or No answer.  You can't then just assign some default Text value within the same context.

There are several ways to tackle your goal and depends on how you have implemented your columns.  Can you show us where, in your app, you use the IN() function and where you want to assign the default of "None"?

[Domearian]

Hi Willow, See the attached screenshot. The aim is as follows:

If a user is not recognised, I want it to default the [SetLoc] to "None". As before, Users have a setting called [location]. The Menu has a setting of [Local]. If the user [Location] matches a menu item [Local], it will show that item. If the User Location is blank, or does not match anything in the [Local], then I would like a [Local] "None" item to be "filtered", and thus visible. The None is a [local] item within the menu and will only be shown if nothing is matched or is blank.

[Steve]

Perhaps you didn't use it properly? Have you looked at the docs I provided?

[Domearian]

I've managed to find a solution.

IF(LOOKUP(USEREMAIL(), Users, Email, Location) = "HPC", [Type] = “SetLoc”, AND ([Type] = “SetLoc”, [Local] = None))

This does everything I want it to do. Should have used the Lookup to start with.