Security Filter for hierarchy data

[Fusion_Incense]

Question
I’m building a meeting logging APP,
There are different kind of the meeting held, ex., board meeting, manager meeting, sales meeting, logistic meeting and so on,
What I’m trying to accomplish is,

1. board can see everyone’s meeting log
2. manager can only see their team of manager, sales, logistic, and warehousing meeting
3. sales can only see their team of sales, logistic, and warehousing meeting
4. logistic, can only see their team of logistic and warehousing meeting
5. warehouse group can only see their only meeting log
   What is my best approach on data setup, and what is the easier way I can set different security filter for different group

for now I’m going type out the whole security filter manually, meaning line by line

[EIG]

Hi,
I think you can setup a second table where you assign people to different groups then you can use a security filter on the original table to see which referenced group that person belongs to.

[Fusion_Incense]

Hi @EIG,
what you mean is using an IN() statement? for example, IN([Position Title], Assign[Position Title]), something like this?
What about let’s say I have 2 board members, they have their own meeting log which can only view by them, and they also need to see every other meeting logs, including, all group of managers, all groups of sales,… and so on
and same as manager level, they can’t see the board meeting, but they can see everything downward, and so on

[EIG]

I think you might be needing SELECT() but I am not quite sure how it will work with people being able to see meetings outside of their group (“everything downward”).

[Fusion_Incense]

@EIG Yes, Thank you Joe, Let is what I’m stuck on too , I can get each group to see their own group with IN() statement, but outside downward still no luck

[Bellave_Jayaram]

Haven’t tested this but one way you can set it up is to have 2 tables, a Group table and an Include table and filter what someone can see based on them. In the Group table, you will need Email and Allowed2SeeGroups (Enumlist of Groups) and in the Include table, Group, Allowed2SeeMeeting (Enumlist of MeetingType).

IN([Meeting], 
     SPLIT(SELECT(Include[Allowed2SeeMeeting], 
                     IN([Group], 
                           SPLIT(SELECT(Group[Allowed2SeeGroups],[Email]=USEREMAIL()),","))),","))

[Fusion_Incense]

Hi @Bellave_Jayaram OK, I’ll process the concept first thank you

[Steve]

AppSheet’s “no-code” model makes this a difficult problem to solve: expressions lack needed programmatic features.

There are two big problems: (1) describing the hierarchy (called a “tree” in programming parlance); and identifying whether something is “in” a given branch of the tree.

A tree can easily be constructed as rows in a table, each with a Ref back to the row to which it belongs (e.g., each employee row points to the employee’s manager. How do you browse the tree? How do you add and remove entries? What happens to subordinate entries if a manager is removed? How you move entries to reflect reorganizations? How do you simply display the tree?

The simple tree above, with a single Ref from subordinate to manager, can describe the hierarchy, but there’s no programmatic way to identify everyone below a given starting point. Well, you could do it with actions, but you can’t use actions in security filters, and actions have to be prompted by some user action (either directly or indirectly).

Trees are also delicate and not well suited for the distributed model AppSheet uses where multiple users may be using different data sets until everyone syncs. It would be very easy break a tree if multiple users are each manipulating it and sync at different times. You would have to design a report that could discover such breakage. Fixing the breakage may not be easy.

[Fusion_Incense]

Hi @Steve Thank you for the great inside, everyday always learning something new

[Aleksi]

What I have used with one app…

• five different group levels
• Board is 1
• Managers are 11,12,13 etc.
• Sales are 111,112,113,121,122,131,132,133 etc.
• Logistics are 1111,1112,1121,1122,1123,etc.
• Warehouses are 11111,11112,11121,11122,11123 etc.

What this modelling does… when different levels have different amount of numbers, it’s easy to make a hierarcy search. You can do that with a formula…
LEFT([GROUP],LEN(TEXT(LOOKUP(USERMAIL(),Users,Email,Group)))=LOOKUP(USEREMAIL(),Users,Email,Group). If you use Usersetting where the user will identify with his/her group, you would not need to read it from users table. Then you could use… LEFT([GROUP],LEN(USERSETTINGS(“Group”)))=USERSETTINGS(“Group”)

For example… if you are a sales and your group is 112, with that security formula you are seeing all groups where the group number starts with 112. This is a dynamic hierarcy and it works if you don’t have more than 10 groups in the same level. I mean Manager 11 can have 10 different groups like 110,111,112,113,114,115,116,117,118,119. You could use letters as well. Then you could have 26 but it’s little bit difficult to see the “three”… for example AACHD would not tell you directly as much as 11384.

[Fusion_Incense]

@Aleksi nice, very clever, Thank you again Aleksi, I’ll see what I can do with my APP Thanks

[Aleksi]

You’re welcome