Hello to all of the great people and minds!
I’ve built a document, with a somewhat complex set of access rules, based on the acl, which also checks data from a table that stores user rights and roles.
The tables in my document reach up to a 5 level depth, which actually means that there is a master table Events, under that a table Expenses with a reference to an Event, and under that a table ExpenseDetails with a reference to an Expense and lastly a table ExpenseDetailAttachments that points to an ExpenseDetail.
Also, the Event, has reference to a “lookup” table named Sports, which has records with sport names.
Sports ← Events ← Expenses ← ExpenseDetails ← ExpenseDetailAttachments
Now, in the UserRights table, the 2 important columns are Email and AllowedSports.
AllowedSports is a reference list to Sports table.
Sports ← UserRights
With this system, a user may have 0 to N access rights to Events that belong to his AllowedSports.
For example, UserA has 2 references in AllowedSports: [Basketball, Football], or if you prefer: [Sport[1], Sport[2]. UserB has 1 reference, for example Basketball, or Sport[1], UserC just 1: Diving or Sport[3], etc.
Finally, I’ve setup a User Attribute: UserRow, to store the row from table UserRoles, by looking up the user.Email on the Email column. And I use this to check UserRow.AllowedSports
Now, the problem I’m facing, and it’s actually inconsistent, as it works in some cases, and not working in others, is the following:
If I want to show Only the events for the sports that the user has access to, it works great:
A. On table Events:
rec.Sport in user.UserRow.AllowedSports << WORKS FINE
B. If I want to filter ExpenseDetails following the relationships shown above, Again it works:
rec.Event.Sport in user.UserRow.AllowedSports
C. Let’s say, we want to show records from ExpenseDetails, same thing, 1 level deeper:
rec.ExpenseDetail.Event.Sport in user.UserRow.AllowedSports
ISSUE #1 - as shown in C
I can’t use it and I’m getting: No value for ‘rec.ExpenseDetail.Event‘
ISSUE #2 - as shown in B,
While the expression there is allowed, the actual data filtering is not working. Practically, while UserA has the right to view the records of 2 sports, he sees nothing.
ISSUE #3: Inconsistency - Works in another case
The formula that doesn’t filter correctly in ISSUE #2, WORKS LIIKE A CHARM in the set of tables and relationships that have to do with revenue:
Sports ← Events ← Revenues ← RevenueDetails ← RevenueDetailAttachments
I know it’s a lot, and I may also fail to explain most of it, so I’m sorry for the lengthy message.
Any ideas/suggestions are more than welcome. Thank you for your time!
George