Ok, as per usual, having a problem with expre...

[Miranda_Lubarsk]

Ok, as per usual, having a problem with expressions.

I have a table with Columns I am trying to filter out by using the “Show_If” function.

I want to filter the Columns based on the name of the column header, not the value in the column.

That’s to start, and maybe where I am having problems.

The column headers are names of clients.

I want my equation to only show that column if the user/employee has been assigned to that client.

I have this information in another spreadsheet.

I have tried the following but it doesn’t seem to be doing anything at all.

I can still see the Columns of clients whose information I am not assigned to, so I think it isn’t working.

=“text value” = ‘Master Garden List’[Client Name].[Employee Name]

Please let me know how my terminology is wrong.

My second question is this: if an expression verifies, how is it possible that once you “Verify and Save” it brings up an error?

Thank you!!

[Miranda_Lubarsk]

@Adam_Stone_AppSheet Hey, so I did the virtual column, labelling it Employee Email, and it worked pretty well.

Now I am in-putting the equation you recommended.

It verifies and saves, but I’m not sure if it’s working or not because I am the creator.

How do I view this from a user’s perspective to figure out if the equation is working?

And which user’s perspective would I be viewing it from?

[Adam-google]

Hi @Miranda_Lubarsky, the expressions can only check cell values, so there’s no shorthand to say “this column’s name”, you’ll need to specify the name within the expression for each column.

I’m a little confused how the client is related to the employee. How is Master Garden List structured? Is the Employee Name also specified in the table with the client-named-columns (so each row is for a particular employee), or is employee based on something like UserEmail()?

[Phil]

@Miranda_Lubarsky

“Verify & Save” performs more checking than a simple “Save” does. It is a good idea to perform “Verify & Save” periodically so you will see the errors that are only detected when doing the more extensive error checking. The reason for the two options, is that simple “Save” is quicker.

[Miranda_Lubarsk]

@Miranda_Lubarsky I went to the Data Table view and clicked on “User Mode” or whatever, but all the rows that I had assigned that expression to were still showing up.

I don’t know which use I was viewing it as, but all were visible making me think it didn’t work.

[Miranda_Lubarsk]

@Adam_Stone_AppSheet Ok, so HOW do I write the expression.

If I’m referring to the value of the column header, do I put it in quotes, double quotes, or what?

The information I’m trying to get it to refer to is in another table altogether.

Each client is assigned an employee in a different table.

And in yet another table, I have the employee names and emails.

Thanks for your help.

I really need advice.

And @Philip_Garrett_Appsh , thanks and yes I know the difference between a regular save and verify&save.

What I am asking is different.

I’m asking why something would verify when testing it out in the expression helper, but then not verify in the verify&save.

[Adam-google]

Yes, to use the column header in the expression you’d have to put the text itself in quotes in the expression. Beyond that, the question of how to write the expression depends on how the client/employee relationship is defined in the other table and how you know which employee you’re checking for. For example

LOOKUP(“Particular client name”, Master Garden List, Client Name, Employee Name)

would find the Employee Name assigned to the Client (assuming each row in Master Garden List has a Client Name column with one row for each client and an Employee Name column that assigns an employee to the Client in the same row).

You said you want to show the column “if the employee has been assigned to the client,” but which employee do you mean? If you do the lookup above you can see which employee is assigned to the client, but then you would need to check if that equals the employee of interest. Is that employee based on something like the logged in UserEmail(), an option in UserSettings, another field in the row?

[Adam-google]

The expression tester checks if the expression syntax is valid, referenced columns can be found, etc. Verify and Save checks that the whole app configuration is valid. Usually they’d be in sync, but maybe some errors only appear in the full verify. Without seeing the error message it’s hard to say why.

[Miranda_Lubarsk]

@Adam_Stone_AppSheet I have another table in the app where I have a list of employees, their emails and the groups they are in (ie. maintenance, construction, sales). How do I tell app sheet that the [Employee] Column is equal to the USERNAME(), and that the [Useremail] column is equal to the USEREMAIL()???

And furthermore, how do I link the aforementioned expression with the username/email so that it knows only to show if the assigned employee’s name is the user??: How would I add on to this expression?

LOOKUP(“Particular client name”, Master Garden List, Client Name, Employee Name)

Thanks so much!!!

[Miranda_Lubarsk]

Tried this…

=LOOKUP(“Brett, Patricia”, Master Garden List, Client Name, Employee Name) = (‘Employees’[Useremail] = USEREMAIL())

It tests out fine in the expression builder in one of the column show_if’s, but not another one.

I haven’t even tried to save & verify yet.

The error message reads “Expression ’ =LOOKUP(“Brett, Patricia”, Master Garden List, Client Name, Employee Name) = (‘Employees’[Useremail] = USEREMAIL())’ could not be parsed due to exception: Object reference not set to an instance of an object…”

What are they talking about with “instance of an object?”

[Adam-google]

The error is related to incorrect syntax. Sounds like the expression tester is giving you a false positive. The pattern TableName[Column Name] returns a list of all the column values. Comparing a list a single email won’t work. Something about the use of parentheses may be confusing the parser.

If it’s the logged-in user you’re trying to filter for, you can use the UserEmail() function in any expression to get the email directly, no need to reference another table. You could use another Lookup to get the employee name corresponding to the user email, but it’s not very efficient. Instead, I would suggest relating client names to employee emails rather than employee names in the Master Garden List, and then just do

=LOOKUP(“Brett, Patricia”, Master Garden List, Client Name, Employee Email) = USEREMAIL()

We don’t recommend using USERNAME() because it’s not always known (depends on login provider and user’s profile), whereas logged in users will always have a known USEREMAIL().

[Miranda_Lubarsk]

@Adam_Stone_AppSheet Ok, my problem is that when the employee’s name is changed, it is by other people in the google spreadsheet, not me, and it will be much easier to keep it as people’s first names.

Here an idea, could I make a virtual column with the employee’s corresponding useremail and then reference that?

Or would this make app sheet even more confused???

[Adam-google]

Ok, I think I’m following now. So you’re adding the email column to Master Garden List as a virtual column which should find the email associated with each employee name, which is stored in Employees table. Is that right? And then you can use the lookup to find the virtual column and check that it equals UserEmail().

I think that should work. The expression for the virtual column would need to be something like LOOKUP([_THISROW].[Employee Name], Employees, Employee Name, Employee Email).

The first part is a little confusing. In general the pattern for Lookup is

LOOKUP(value to look for, table to look in, column to look for the matching value, column in the matching row you want to return). To reference a column in the current row in Master Garden List for “value to look for”, you need to put [_THISROW].[Column], otherwise it will look for the column in the table you’re looking in (Employees).

Once you have that virtual column you can use

=LOOKUP(“Brett, Patricia”, Master Garden List, Client Name, Employee Email) = USEREMAIL()

as the show_if expression.