Role dependent select item, view selected item by all roles.

[Peter_Bernsen]

Dear all,

I have a parent table with a ref to a child table.

In the Ref-table there are in for example 10 values.

Only one user is admin and is allowed to select on of the 10 values.

Other Non-admin users only may choose from 6 specific values.

I have a user table with admin and non-admin role

The ref-table has two column

Column1: ID the item to select

Column2: Role wiht Admin or non-admin

I could make a Valid_IF with the Role for the Ref column.

The problem is:

The non-admin users must see the value in a record when the person with role "admin" choose one of the 4 items the non-admin can't select. 

I also thought of a editable_if constraint.

Who can help.

Subscriptionplan: Appsheet Core, Plan Class: Secure

[Suvrutt_Gurjar]

Your description of the tables is detailed but could you make it clearer with proper table names and in which table non admin and admin users should have restricted and non restricted access etc.?

[Peter_Bernsen]

Table 1: Products
Column 1. Keycolumn = uniqueID()
Column 2. Product = text
Column 3. Route = Ref to table 2 Route

Table 2 Route
Column 1: Value 1-10 (key column)
Column 2: Role with values "admin" or "office"

In example: record 1-6 role: "office", record 7-10 role: "admin"

Table 3: User
Column 1: Email (key column)
Column 2: Role with the values:  "admin", "Non-admin "or "office"
Column 3: Name

Example:

User admin: no restriction on table 1 Products, column 3 Route and can choose record 7 from table 2.

User office: restiction on table1  products, column 3 Route, only can choose  values table 2 Route from column 2 Role with value "office" example record 2, AND can see ALL choosen values of column 3 Route, so also record 7 (when is choosen by user role admin).

So admin can select a value route[role]=admin but users with role "office" can not edit that field when a route[role]= "admin" is choosen.

[Suvrutt_Gurjar]

Hi @Peter_Bernsen ,

Thank you for all the details. Your requirement is clear now. However it is somewhat specific. While I can share the approximate approach that I have in mind, just wanted to test it out before responding.  Hence I have not so far responded. I have been unable to test it so far. Will do so as soon as I can test it out. 

You may also get a response from someone else in the community in the meantime.

[Suvrutt_Gurjar]

Please try below

1) Create a slice on the Users table called say "ThisUser" with a filter expression of [Email] = USEREMAIL()

2) The Valid_if of [Route] column in Products table

IFS(ANY(ThisUser[Role])="Admin", Route[ID],

ANY(ThisUser[Role])="Office", SELECT(Route[ID], [Role]="Office")

)

Here [ID] is key column of "Route" table

3)The Editable_If of [Route] column in Products table

 IFS(
NOT(IN([Product ID], Products[Product ID])), TRUE,
ANY(ThisUser[Role])="Admin", TRUE,
AND(ANY(ThisUser[Role])="Office",IN([Route], SELECT(Route[ID], [Role]="Office"))),TRUE,
AND(ANY(ThisUser[Role])="Office",IN([Route], SELECT(Route[ID], [Role]<>"Office"))),FALSE
)

Here [Product ID] is the key column of Products table