User Roles and Filtering Dropdowns

[PapaBear9420]

I have spent close to 200 hours trying to understand and figure out one feature which should be simple and I cannot figure it out for the life of me. It doesn't matter which videos I watch/form I read/AI prompt I type, I just keep hitting a brick wall.

I am trying to build a work order app. There are 3 kinds of potential work orders: Development, Maintenance, and Tech. We have about 20 different properties and the user needs to be able to select the correct property for the work order request. However, there are properties within our organization that we do not want all users to know about/have access to (we house DV victims similar to WPS). How do I create a "New Work Order" form with a dropdown that will only display the properties the logged-in user can access. Every formula I try seems to come back with an error.

I have data tables for properties, employee info, and work orders and have created probably a dozen other tables just trying to experiment with how to make this work. Is there any way someone would be willing to walk me through this and help me understand what I am doing wrong? Our organization does not have an Enterprise Account, therefore, Domain Authentication is not an option.

[Markus_Malessa]

What is your security filter on your Properties table and what is your expression for the column in the Work Order table where you select a property?

Your security filter on properties should be similar to this:

IF(USERROLE() = "Admin", TRUE, [YourPropertyColumnForHiddenProperty] = "Unhidden")

Then your work order table property dropdown selector Suggested Values should be this if it is a related table:

YourPropertyTable[KeyColumn]

[PapaBear9420]

I have been operating under the assumption that I had to use USEREMAIL for my formulas. I did not know I could use USERROLE unless I have domain authentication set up. Is that accurate?

[Markus_Malessa]

Well it depends on how your app is setup. Appsheet only allows two user roles under the USERROLE() expression, which are "Admin" or "User". So if your app is setup to identify between those two roles only, then USERROLE() would be appropriate to use here. But, if you are using some other mechanism to determine who can see those "secured" properties then you may have to use USEREMAIL() instead. Let's say you are using an internal user table with names, emails, and internal roles. Then you could filter that table to the current user by using "[UserEmail] = USEREMAIL()". Then you could set a security filter in the Properties table "IF(IN("Special User Role", ANY(UserTable[InternalRoles])), TRUE, [ColumnToHideProperty] = "Unhidden"))".

That is why @Steve mentioned we may need to know a bit more about your app setup.

[Steve]

If @Markus_Malessa's good suggestions are insufficient, we'll need a better idea of what you've tried. What settings are you trying? What expressions have you tried?

[PapaBear9420]

I will post screenshots of my column headers in my data sheet for reference. Please feel free to offer feedback on how my data is organized. As for what I have tried, I have been using formulas that lookup the USEREMAIL, the lookup the other relevant columns in the employee info. I have done several virtual columns that will test out my ability to look up the user name, role and even the allowed properties but when it comes to making a true/false column to test if the allowed properties match the user's role, I get inaccurate trues and falses

[Markus_Malessa]

Based on this info I would say a security filter on the Employee Info table:

[Email] = USERROLE()

Then a security filter on the Properties table:

IN(ANY(Employee Info[Row ID]), [Employees with Access])

Any column in any other tables where you may need to select a property should have the following in the Suggested Values expression:

Properties[Row ID]

Under these circumstances if you filter the properties table via a security filter like above then the dropdowns for selection should show accurately based on your custom restrictions.

[PapaBear9420]

I really, really appreciate your time/help.

When I typed in those security filters and suggested values, none of the formulas came back at me with an error message which is progress! However, when I test out the app as one of the users, it has now removed all of the properties from the dropdown menu. For what it is worth, there are currently 8 defined roles in my data: "Admin, Admin User, CC Admin, Development, Maintenance, ODSH, PSH, and Tech.

[Steve]

Please explain the relationships between departments, roles, and properties. Which determines who can do what?

[PapaBear9420]

We have 4 departments (Admin, CC, PSH, and ODSH). As far as the app is concerned, these distinctions don't mean much. Those are there more for the users sake as that is how the organization refers to its employees/properties. It doesn't allow the needed flexibility the roles distinction does. For example, our CEO needs to be able to access all properties. Our CFO just needs access to the Admin Office, yet they both work in the Admin department. The roles are where I have been trying to do most of the leg-work. Each employee has a role. However a few employees have multiple roles. For example, we have a therapist that works within two of our departments. She would need the ability to have access to properties that both the ODSH and CC Admin have access to. I can always create a separate role if needed though so that employees only have a single role. I have moved the data around a million ways but currently the properties have their own table with a "Employees with Access" column that is an EnumList ref to the Employee Info table with names, email (username), role, etc. I would prefer to have the properties table have a "Allowed Roles" EnumList ref column referencing the Roles table. I am willing to organize my data however is needed but I need to make sure that the properties can remain somewhat fluid. Admins need to be able to add, edit, and delete properties. When they do so, it should be user friendly for them to also add permissions to those properties. For example, if we get a new PSH property, an admin should be able to add that property and give access to the PSH, and Admin role or at least select the employees one by one that should have access to that property.

[Markus_Malessa]

I'm having a hard time distinguishing between departments and roles here, unless they are actually the same. Can you list the different property types also? Is the PSH type property the one that needs to be filtered to only certain roles or departments? There is at least a slight inclination that you are making this more complicated than it needs to be.

[PapaBear9420]

I totally could be making it more complicated than I need. When I first started to building my data, I was using departments as a main way to organize the relationships with properties and employees. However, I quickly realized that using roles to organize that relationship would work much better. Therefore, I still have the department table and labels for employees and properties, but I am not using that in any way as far as the relationship between employees and property access.

Although the names of the departments and roles share a name, they are not the same. For example, our CEO and CFO both work in the Admin department but the role of the CEO is Admin. The role of the CFO is Admin User. The Admin role gives access to all properties which only applies to that particular role. The Admin User role only gives access to the Admin Office property. I went ahead and just made the roles singular for simplicity's sake. Therefore, each employee only has one role. The "Role" column in the "Employee Info" table is a reference column to the "Roles" table. The "Roles" table has an "Associated Properties" column that is an EnumList ref column. That column references the "Properties" table that contains the addresses. Hopefully, that helps!

[Markus_Malessa]

Are you using the default column [Row ID] as the key column for each table or are you using the columns you created [xxxx_ID] as the key columns for each table?

[Markus_Malessa]

I noticed a mistake on my prior response for the security filter that should be used for the Employee Info table. It should be:

[Email] = USEREMAIL()

[PapaBear9420]

For what it is worth, I have taken a lot of screenshots and even created sample data that uses the same columns/structures/relationships as my actual data, just with fake names/addresses/etc. Hopefully this helps. I did restructured my data a tad to hopefully simplify things from the screenshots I provided earlier.This would be my "parent" table as all the data is funneled to my work orders table."Role" table"Employee Info" table"Properties" table"Departments" tableWhat I am seeing on the app editor side of things. This is where I want the list of properties to be limited to the user's role.In case this view is helpful...

[Markus_Malessa]

The following should work:

Security filter on Employee Info table:

[Email] = USEREMAIL()

Security filter on the Properties table:

IN(ANY(Employee Info[Role]), [Associated Roles])

 Suggested Values in the Work Order table for the [Property] column:

Properties[Row ID]

[PapaBear9420]

I can tell we are SUPER close! Thank you again for your help! That got us most of the way there. The only issue now is that it is showing the Row ID instead of the actual address. It also has a yellow warning sign beside it? Would that just be the Delta Sync thing because of the security filter?

[Markus_Malessa]

Typically the exclamation triangle means that there are returned values that are not part of the records the application has access to. So in this case that might actually be unwanted.

In your database table you have the property address as the label column, but how about in the application itself?

[Markus_Malessa · Accepted solution]

Quick follow up on this. This could also be an error because your [Row ID] column is not set as the key column in the application itself. If you are using the other ID columns that you created in the table then you would need to set the suggested values to "Properties[Property ID]"

[PapaBear9420]

You are awesome! That worked! Thank you so so much! That ended a month long battle for me!