I have an app displaying two entity types: "audits" and "actions". Both entities have dates on them and can be grouped into quarters, for example. A user is allowed to see a subset of all audits, and a subset of all actions. (Subsets vary across users. Some users are allowed to see everything).
I implement this using SECTION ACCESS. An "Identity Role" table maps users to USER or ADMIN, while "Identity Audit" and "Identity Action" tables capture users' entitlements. Finally, there are the data tables "Audit" and "Action".
So far so good. However, I then discover that I cannot have a filter on "Quarter" - both "Audit" and "Action" have "Quarter" field, which alone is left unqualified in the load script - as this creates a circular reference. In fact, I cannot connect "Audit" and "Action" on any field: any connection will close the loop. I can, of course, qualify "Quarter", and have two disconnected "Quarter" filters - operating on, effectively, two different fields - but that does not look good to my users.
It sounds like I just can't have SECTION ACCESS permissions for both audits and actions AND a common field between "audit" and "action" islands. The only workaround I can think of is "compress two tables into one":
a. have a data table created with
select AuditID as EntityID, *
from Audit
union all
select ActionID as EntityID, *
from Action
b. have a permissions table created with
select AuditID as EntityID, *
from IdentityAudit
union all
select ActionID as EntityID, *
from identityAction
Is there a better way?
