UX View & Show_If Constraint Oddities

Michael2
Participant V

For the longest time I have been using SELECT statements to form my app around who is using it. I have known for quite a long time that this is not the most efficient way to go about it and recently decided to change this.

An interesting post I found by @MultiTech_Visions regarding this is here Current_User (Slice).

Generally, his method of conforming the app around WHO is using it, works wonderfully. Everywhere I was using SELECT statements, the expression builder dutifully informed me that the expression could impact performance. When I replaced those select statements with INDEX statements based on @MultiTech_Visions example, the expression builder no longer reported any performance issues.

The one exception has been with restricting views.

The expression IN(INDEX(CurrentUser[Role],1),LIST(Admin, Manager)) evaluates correctly BUT, the view is not shown.

The expression IN(ANY(SELECT(Users[Role], USEREMAIL()=[UserEmail])), LIST(Admin,Manager)) also evaluates correctly and DOES show the view.

Both expressions evaluate to the same results yet have opposite effects?

Any ideas would be greatly appreciated.

@MultiTech_Visions @Steve

Solved Solved
0 36 1,130
1 ACCEPTED SOLUTION

Thank you @WillowMobileSystems.

Turns out to have been the allow updates SWITCH statement.

Removing the SWITCH statement from the table has allowed all the expressions Iโ€™ve tried thus far to function properly.

Now, I need to figure out how to use a similar expression with the SWITCH statementโ€ฆ

View solution in original post

36 REPLIES 36

Are you by chance using the โ€œPreview Asโ€ list to switch to different users OR might you be the Co-Author in the app?

I have found that USEREMAIL() in Slices does not work correctly when the USEREMAIL() is NOT the App Creator. The wrong user info is present or none at all. I usually overcome this by temporarily hard coding the userโ€™s email into the Slice filter criteria

Thank you, but no. When evaluating whether the expression is working as I intend, I write the expression so that my [Role] will be returned in the expression.

I am aware of the shortcomings of the Preview As feature in this regard.

Understood. And you have confirmed that the CurrentUser Slice has the data row you expect?

Yes.

The expression using the CurrentUser slice works as expected everywhere except UX Show_if constraints.

Ok, I have a similar CurrentUser/Role feature in one of my apps. I took your CurrentUser expression and used it pretty much as is in my app.

My Roles are based in a table and use UniqueID so I have used that REF value rather than actual text-based role names.

I am not seeing an issue for myself. I might suggest dissecting the expression to make sure the values you are expecting to be returned are indeed returned.

Condition with expected Role REF value - View shows

Condition does not include the Users Role REF value - View is hidden

MultiTech
Participant V

My guess is the problem centers around your formula missing quotes

  • (Unless itโ€™s the community that took them away when you posed the copy of your formula?)

IN(INDEX(Current_User[User_Role], 1), list("Admin", "Manager"))

  • Without the quotes around the options, the values youโ€™re trying to compare arenโ€™t technically strings - itโ€™s whatever the system decide they are

    • So itโ€™s likely that youโ€™re encountering the problem of:
      [text_based_role_value] = *
      • Which might be true, or not - depending on what type the system assigns your list values.

I thought that tooโ€ฆbut in my examples I, for that reason, left off the quotesโ€ฆand it works!

Turns out the issue I was having was related to the expression used to filter the CurrentUser.

I was using AND(USEREMAIL()=[UserEmail],[IsActive]=TRUE). This evaluated correctly in the expression builder, the correct row was returned as true. HOWEVERโ€ฆ

When I built a table view based on CurrentUser, no results are returned.

I tried writing the expression several different ways to no availโ€ฆ Nothing I tried would work when multiple criteria was used even though the syntax was correct and the expected results were returned in the expression builder.

Writing the expression with only one criteria is the only thing that is working for me: USEREMAIL()=[UserEmail]

Did you try testing it by temporarily replacing the USEREMAIL() function with the hard coded email address?

Just to confirm I definitely have two conditions in the filter for my current user slice, so it does work

In my User Table, the column User Email is of type email and the column Active is of type yes/no. Note that because the Active field is a boolean, I donโ€™t actually have to specify =TRUE, but that should have no impact.

For what itโ€™s worth. I changed my Slice and the view โ€œShow Ifโ€ expressions to be constructed like yours. Just canโ€™t reproduce your issue.

MultiTech
Participant V

It also might be a type thingโ€ฆ

  • check and make sure that the type (Iโ€™m AppSheet) of the [Is active] column is a yes/no
  • check that the column in the data source is also marked as a yes/no
    • if youโ€™re using Google sheets, setting the type to automatic will make this happen

Sometimes even if the data type is set correctly in AppSheet but itโ€™s not correctly set in the data source, that can cause strange fringe cases - which this kind of sounds like it might be maybe.

Seems to have been the column type. The IsActive column was set as text type. Changing it to Yes/No seems to have resolved the issueโ€ฆ Not sure why I didnโ€™t think of thisโ€ฆ

Thanks for everyoneโ€™s help.
@WillowMobileSystems @Graham_Howe

Michael2
Participant V

Seems I may have Spoken too soonโ€ฆ This time regarding show_if and actions:

A user may belong to only one or several crews, i.e., Crew1, Crew2.
I need to show the action to the users that belong to Crew1 but no other crew.
The [Crew] field is set as Text type.

I have written the expression several ways, all which evaluate correctly but the action is not displayed:

  • ISNOTBLANK(INTERSECT(SPLIT(CurrentUser[Crew],","),LIST(โ€œCrew1โ€)))
  • ISNOTBLANK(INTERSECT(SPLIT(SELECT(Users[Crew],USEREMAIL()=[UserEmail]),","),LIST(โ€œCrew1โ€)))
  • CONTAINS(CurrentUser[Crew],โ€œCrew1โ€)

Similar expressions are used to determine if a view should be shown to the user and those seem to be working as expected. Thusfar, the expression only seems to be failing when used in show_if of actions.

@MultiTech_Visions @WillowMobileSystems @Grant_Stead @Graham_Howe @Steve

You state in you requirements that

I need to show the action to the users that belong to Crew1 but no other crew.

However from your example expressions it looks like you are trying to match where the user belongs to at least Crew1. Which is it that you actually want to do?

If you want to check that a user only has Crew1 in the [Crew] field, then I would probably check first that the user has only one item in that list, then that the first item is equal to โ€œCrew1โ€. Something like this:

AND(
  COUNT(CurrentUser[Crew]) = 1,
  INDEX(CurrentUser[Crew], 1) = "Crew1"
)

MultiTech
Participant V

There is a crucial step missing when calling a list

  • Concatenate()

Split(Current_User[LIST_COLUMN], " , ")
needs to be:
Split(Concatenate(Current_User[LIST_COLUMN]), " , ")


The original post was missing this, I just had Steve update the post so it had the correct formula. (It was so old, I couldnโ€™t modify it anymore! lol)

Interesting, I hadnโ€™t actually tried my suggestion, does that mean Current_User[LIST_COLUMN] is not actually presented as a list in the slice? So my COUNT and INDEX functions would fail?

3X_c_2_c23d9b7e099d098cbdc7e06d4a9c89afe1273088.png

OK specific to SPLIT() when working on LIST() then. But I just noticed that I missed a crucial part of @Michaelโ€™s post. He said the [Crew] field is type text when I was assuming a list. So my suggestion of COUNT() and INDEX() will not work as is. The correct version should be:

AND(
  COUNT(SPLIT(CurrentUser[Crew], ",")) = 1,
  INDEX(SPLIT(CurrentUser[Crew], ","), 1) = "Crew1"
)

First, allow me to thank all of you for taking your time in attempting to help me to sort this outโ€ฆ

Changing the expression to include the CONCATENATE did seem to be the solution. Initially. However, this seems to be inconsistent:

While I was trying all of these different expressions on one action in particular, there are two actions in play. Both should be shown to the same specific user, therefor both actions initially had the same show_if expression. On only one of them, was I changing the expression while I left the other as initially configured.

When I last updated the expression for only one action, both are being now being shown although I only changed the expression on one.

One actionโ€™s show_if is written withOUT CONCATENATE and is being shown.
The other action WITH CONCATENATE is also being shown.

Prior to changing the show_if for the ONE action, neither were being show.

Also of note, the initial show_if being used to determine what view is shown to specific users, which started this post, DOES NOT include the use of CONCATENATE yet, the expected view is being shownโ€ฆ

To @Steveโ€™s point about SPLIT(); the base type of the [Crew] field was and is set as text. Is it possible that AppSheet is changing it/interpreting it differently when the Users table is sliced into CurrentUser OR is AppSheet interpreting the field differently due to the fact that the data in the [Crew] field is delineated with commas?

Again, thank you all for your time.

Did you try my last example above? It should work with the text field, the need for using concatenate is when the field is a list type which yours is not.

Thank you Graham but no, I have not tried your expression yet. At the moment, I am more concerned that the results I am getting are inconsistent. I agree with your point that the fieldโ€™s base type is not a list but, is it being converted somewhere along the way by AppSheet?

Looking closer at your expression, I donโ€™t think it will work because, it requires that there only be one value returned in the SPLIT which will always be false if the user belongs to more than one crew; SPLIT(โ€œCrew1, Crew2โ€,",") will return 2 which means COUNT=1 will be false.
The second part of your expression (which will never be evaluated) would require that the INDEX value of Crew1 be predicable (It would always have to be 1 in your expression) which it may not be. It would be if there were only ever one crew but there may be 2 or more and the specific crew (in this case) may be be in different locations (INDEX values).

I thought that was what you were looking for, I even questioned it in my first answer:

If you want to find any user who has Crew1 in the [Crew] column, regardless of whether they have any other crews and regardless of order of assignment, then the following should work:

IN("Crew 1", SPLIT(CurrentUser[Crew], ","))

Updated to correct the SPLIT syntax

I apologize. Maybe I could have worded it differently but, you have misunderstood the intention.

It will be possible for a person to belong to one or more crews but, in the case, I want to show_if the user belongs to a specific crew, Crew1.

IN(โ€œCrew1โ€, SPLIT(CurrentUser[Crew],",")) should return TRUE if Crew1 is found in the list returned from the SPLIT of CurrentUser[Crew].

So does that expression not work?

Is the current user in at least Crew1?

IN("Crew1", SPLIT(("" & CurrentUser[Crew]), ","))

Is the current user in only Crew1?

("Crew1" = ANY(CurrentUser[Crew]))

Yes, the CurrentUser is in Crew1.
No. the CurrentUser is in multiple crews.

I think youโ€™re very confused.

Mmmmโ€ฆ okay? How so?

The question werenโ€™t directed at you, they were the questions answered by the expressions I provided. That should be evident by simply looking at the expressions.

Your problem isnโ€™t difficult to solve, but youโ€™ve done a bad job describing it.

I have much respect for you. Youโ€™re obviously very knowledgeable and helpful but, I canโ€™t possibly have done such a bad job describing it if you find it simple to solve.

That said, I did not pay attention to the fact that you were(?) giving me new expressions to try. I thought you were essentially asking if the CurrentUser was in in Crew1 and whether they were in only that one crew.

In any case, the inclusion of CONCATENATE (at the moment) seems to have solved the issue but, it does not address the inconsistencies mentioned.

OK @Michael, you, @Steve and I have all provided the same expression now which really should work to pick the user if they belong to at least Crew1

Iโ€™m pretty sure thatโ€™s what you want, but is this still not working? If it isnโ€™t and there is something odd going on with the split command, then you could use a far less elegant string approach like this:

CONTAINS("" & CurrentUser[Crew], "Crew1")

I say less elegant because this would also provide TRUE for a user that belonged to Crew10 for example, but it should work without any issues with the SPLIT().

This would be okay so long as the user belonged to at least Crew1.

I threw together a quick tester app using the expression above and different combinations of a list of values stored in TEXT columns. They all worked as expected. The expression above is good. Next step is to show us your column definitions and actual data being stored. There must be some anomaly you are not noticing thatโ€™s giving you unexpected results.


User Table - Test columns can be modified and are used in the tests shown below

Results of Tests - โ€œCrew Data Listโ€ shows current contents of the column being tested

Thank you @WillowMobileSystems.

Turns out to have been the allow updates SWITCH statement.

Removing the SWITCH statement from the table has allowed all the expressions Iโ€™ve tried thus far to function properly.

Now, I need to figure out how to use a similar expression with the SWITCH statementโ€ฆ

I donโ€™t relate this to anything discussed in the thread. Can you show us what you mean so others can learn from it?

Top Labels in this Space