Comparing a List of Lists to a List

[CleanSheets]

Hello,

I am having troubles with querying the contents of Enum List records when I need to combine those records lists (list of lists).

I am using this for creating CRUD rules that I can config within my app.

Background

Because roles can have different Access Levels for the Crud level I allow multiple records pertaining to the modify level for Functional Role and Table (this view is grouped by table). 

A Staff member's role might have many functional roles, so I combine all the permissions to paint a picture for each role.

Here's an example of the functional roles within a role

This means for a role "IT Lead" you might have numerous CRUD entries due to matrix overlap with other roles.

As a result, I want the highest level of CRUD for your Role per Table.

To help tidy this up, so it's the least amount of backend duplication of formulas when creating a new table, I have implemented slices.

This slice tells you the permissions records that pertain to the functional roles your staff role has been assigned.

This following slice is then used to determine if you as a user, have ALL_CHANGES access. This is the first step to applying all the rest of the CRUD permissions based on a waterfall hierarchy. I won't go into the rest of the slices that complete the picture because it's this first step that is the problem.

In a slice formula I have gone through heaps and heaps of combos, to try and get a result of what should be occurring from the above. I.e. does the unique intersecting of the two lists result in all three CRUD rules that result in ALL_CHANGES as a value.

On the App Permissions table the field of the Data Modify Level is this:

Which is an enum list against this table:

Which has these values:

Just as an aside this the Update Mode expression I use on the table (table settings) to reference the config (slice on the app permissions table) from within the AppSheet entries itself:

There is a 'switch' that AppSheet applies in the background that makes a change from something list based, to a list of list records base. So if you create a VC field to test this out, it will show you the right results initially, but then when it saves and/or sync's it, it will revert back to nothing in the list.

Request:
I would love help with a workaround to get this going and I'm sure it's just a way of thinking outside of box of the formula instead of how I'd approach it from a development perspective (or understanding the inner workings of List of Lists, which is hard to reverse engineer when the VC's flip around and change) .

Cheers 🙂 

[TeeSee1 · Accepted solution]

I could not quite follow what you are trying to do but if what you want is to test if the flattened elements of a list of lists contain all the elements in another list, then here is what you can try.

1. Create a VC that converts a list (an ENUMLIST column in your case) to a text value by CONCATENATE([ENUMLIST COLUMN]): VC_TEXT_OF_ENUMLIST

2. Your target expression is something like

COUNT(
 INTERSECT( //compare the deduped list against your target list
  UNIQUE( //eliminate duplicate elements
   SPLIT( // the long text converted into a single list
    CONCATENATE( //all the elements converted to a long text
     //here you retrieve the first VC from your target rows, ie SELECT(Tbl[VC_TXT_ENL], condition) edited: get the converted [VC_TXT_ENL] instead of the orignal [ENL]
    ), " , "
   )
  ), {your list to compare the above list againt, ie all CRUD valuse}
 )
) = //the element count

[CleanSheets]

Hi @TeeSee1,

Thanks for taking the time to provide a response. You have helped me greatly.

What your response showed me was that everything was correct in my query but I was missing the CONCATENATE() of the select.

In terms of AppSheet feedback, I don't think I should need CONCATENATE() for this query, it seems unnecessary and over complicates it, but I am happy to be able to move forward.

Just a couple of asides for your suggestions, I didn't need a VC and I believe that the Unique() function is redundant in the INTERSECT() function.

But other than that, thanks heaps.

Hear is all I had to change by adding the CONCATENATE() around the SELECT()