Hello all, I am having trouble with expressio...

[Miranda_Lubarsk]

Hello all, I am having trouble with expressions - again.

I am in the security filter field for a table entitle “Design Numbers by Client”.

I have the client names in the first column, and then the plant names in the remaining columns going across.

I would like app sheet only to show the names of the clients that the user is assigned to.

I have this information located in another table called “Employees”.

The table’s columns are “Employee Name”, “Useremail” and “Crew”. I also put the expression USEREMAIL() in the app formula field in the “Useremail” column to try to make sure app sheet understood this link between the “Employee Name” and the “Useremail”.

The assignments of each employee to their client are in yet another table where I have the “Client Name” as the key and first column, and then the “Employee Name” is another Column in the table which is a REF to the aforementioned “Employees” table (the second table) where the Employee Name is the key of the table. I also put =UNIQUEID() in the Initial Value field of the Employee Name column because I read somewhere to do that.

So I am using the following equation in the security filter but it is not working, nothing actually appears in the view for that table when it should show me at least my own assigned clients.

(It shows them all when I have no filter so I know the info is there).

[Client Name].[Employee Name] = USEREMAIL()

I have also tried this but it doesn’t work: [Client Name].[Employee Name] = USERNAME()

What am I missing?

Am I doing this wrong?

I have tried to make sure that I have keys and refs and all that in the right place, but to no avail!

[Phil]

@Miranda_Lubarsky

You are using a two dimensional model for your data where the vertical dimension represents Customers and the horizontal dimension represents Plants.

Using a two dimensional model works in a spreadsheet, but it is not suited to a database. The first step in database design is to “normalize” your data by eliminating the repeating horizontal data. In your case that is the Plant data.

To do this, you would need two worksheets in your workbook. One for Customers and the other for Plants. The Customers worksheet would contain one row per customer. The Plants worksheet would contain one row per customer per plant.

I get the sense that you are keen on keeping the two dimensional model, but I am worried that it will cause you no end of grief.

I am encouraged that you are following the right approach for modeling Employees. It is wise to create a table with one record per employee. However, I am confused about the key for the Employee table.

You should do one of two things.

1. If every employee has a unique name, you can simply make Name the key. You should not specify an INITIALVALUE for the Name field. Because the key is required, you will be forced to enter a value in the Name field. 2. If there is ever any chance that two employees might have the same name, then your Employee table should have both an EmployeeId and a Name column. EmployeeId should be the key and you should give it the INITIALVALUE of

UNIQUEID(). That ensures that the key for every Employee record is unique. The guidance you saw in the documentation about keys and UNIQUEID() in the INITIALVALUE was describing this case. (All large companies use this second approach and assign employees a unique employee id of some sort. This is the safer approach.)

[Phil]

@Miranda_Lubarsky

Has anyone suggested that you data model might not be optimal?

AppSheet is based on modelling data in the way a database does. Everything normally gets easier if you model your data in that way.

You wrote “I have the client names in the first column, and then the plant names in the remaining columns going across.”

That is not how you would normally model this in a database. Instead you would create two tables to hold this data. You would create one table for Customers that only holds Customer specific data like the customer’s name, address, phone number, etc. You would then create another table to hold the Plants a Customer wants. That table would contain one row per customer per plant. So if Bob has three plant, he would have one Customer record and 3 Plant records. The Plant records would have a Ref to the Customer record which keeps track of the relationship between these records.

[Miranda_Lubarsk]

@Philip_Garrett_Appsh Where I am coming from is I have one master spreadsheet with Plants as the rows and client names as the columns.

We have hundreds of clients.

But I need to be able to narrow down what I am seeing in many ways. I want to filter the plants that I am viewing by the week that they are planted, and variety and I want to filter the clients I am seeing by the employees that are assigned to them, etc.

Are you saying I need to create an individual spreadsheet for each individual client in order to record their number of plants?

That feels like a ton of work!

Can you please address my question regarding the equation???

Please pretty please.

[Miranda_Lubarsk]

I also need to filter the clients by the day of the week that we visit them.

Each employee visits 3 to 5 clients per day.

[Miranda_Lubarsk]

@Philip_Garrett_Appsh Thank you.

I am really having a VERY difficult time envisioning what you are describing to pull this information out from the spreadsheet to the tables.

Is there ANY way that one of you fabulous guys could take a look inside my app, or take a look at the spreadsheet and give me some pointers?

I am really very desperate and although Aleksi has made a sample for me (not having looked at my data), I don’t think you can fully understand what I am trying to do without looking at my spreadsheet and at the app.

[Miranda_Lubarsk]

@Philip_Garrett_Appsh Let me also say that I do have another spreadsheet with all the client information, and the clients are the row with the contact information about them as the column headers.

The spreadsheet (whose picture I included above) I have transposed so I have two copies in the app, on a reverse axis, so to speak.

I have been playing with them for weeks.

Can’t envision how to pull the information out into the tables.

[Phil]

@Miranda_Lubarsky

I took at look at the spreadsheet image your provided above. Can you explain what you are recording in that worksheet? For example, what does the value 12 under “Arnold” in column “P” mean in the “Leek” row.

[Miranda_Lubarsk]

@Philip_Garrett_Appsh I am recording the number of each plant variety that each client needs.

For example, 5 Ripbor Kale, 10 Sungold Tomato.

So it’s three pieces of information: #1: number of plants, #2, variety, #3. plant.

[Miranda_Lubarsk]

And just as you might be able to see on the spreadsheet, each plant has a few varieties that belong to it, and the number of each plant is different for each client.

[Miranda_Lubarsk]

@Philip_Garrett_Appsh I also have this spreadsheet, which is the other spreadsheet but with some extraneous information taken out and the clients/plants transposed.

I think I could organize the client names by their assigned gardener by creating a virtual column with a ref once I have it in app sheet?

Is that correct?

But I also need to be able to filter the columns so that I can view the plants by the week that they are assigned, and right now I am being told that there isn’t really any way to filter columns?

I am trying to use the “show_if” function, but having trouble with expressions.

It’s not my strong suit.

And regardless, I actually like being able to see all the information for all the weeks any time that I’d like, I don’t want to hide it when it’s not that week, I just want to be able to organize it by that week and not have a million different views.