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!!
@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?
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()?
โ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_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.
@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.
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?
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.
@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!!!
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?โ
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().
@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???
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.
User | Count |
---|---|
35 | |
35 | |
28 | |
23 | |
18 |