Creating List of Numbers from delimited values

[_redline]

Hello!

I’m currently trying to revamp the permissions system on an app I’m working on that needs a little bit more granular control. What I eventually settled on is creating permission roles of the format ##-CCC_RRR where # represents a number, C is a category (e.g. Admin, Manager, or User), and R represents a role within that category. For example 01-ADM_GEN would represent a general administrator to distinguish from 30-PJM_GEN general project manager.

With that being said, I’m allowing for users of the app to be assigned multiple roles for better control that all get joined and delimited with a colon (:) in the spreadsheet to look something like this: 30-PJM_GEN : 40-USR_GEN (currently can append up to 4 roles). This format is nice because I theoretically should be able to bring in one column into the app (let’s call it UserRole), and filter/compare against my set permissions that correspond to these roles to limit what a given user can see.

It wasn’t long before realizing that I couldn’t easily pull all of this data in, split in appsheet, and compare though. So I relented and went to using two columns (UserRoleName and UserRoleNumber). These are of similar formats but with the “-” already split in the spreadsheet (e.g. UserRoleNumber of UserFoo = 30:40 and the UserRoleName = PJM_GEN:USR_GEN. The idea was to use something like this:

NUMBER(
  SPLIT(
   LOOKUP(USEREMAIL(), "DatabaseUsers", "UserEmail", "UserRoleNumber"), ":" 
  )
)

This obviously doesn’t propagate through the list but instead tries to write the whole thing as a single number.

But for the love of me, I can’t figure out how to bring the UserRoleNumber in to the app (which I have to specify is type “Text” due to the colon.), split into a list by the colon, and then converted into a list of numbers for comparing against other numeric values. Maybe I’m grossly overthinking this, but I’m just trying to limit the amount of columns that I have to bring in and manage for all users. Anybody have any better way to approach this?

[Steve · Accepted solution]

_redline:

But for the love of me, I can’t figure out how to bring the UserRoleNumber in to the app (which I have to specify is type “Text” due to the colon.), split into a list by the colon, and then converted into a list of numbers for comparing against other numeric values.

When generating UserRoleNumbers, use " , " (space-comma-space) as the delimiter rather than ":" (colon), then configure the app column as type EnumList with a base type of Number.

[_redline]

Steve:

When generating UserRoleNumbers , use " , " (space-comma-space) as the delimiter

Phenomenal! Exactly what I was looking for. Thank you so much, and thank you for all your help around the forums!

[_redline]

@Steve, do you happen to know why trying to call this value in another tables formula (i.e. DatabaseUsers[UserRoleNumber] then renders this EnumList of base type Number as something else (I’m assuming just a list)? I ask because I’m trying to pull the UserRoleNumber as discussed before into a formula of another table to then grab the minimum value so:

MIN(DatabaseUsers[UserRoleNumber])

Fails saying that the MIN() function of course needs type Number, but this expression

MIN([UserRoleNumber])

works just fine. Maybe I’m misunderstanding how the datatypes are constructed. I would pull in the value as a Ref, but the data doesn’t really match up to do so as I’m comparing a list of users against a list of views with preset permission levels.

[Steve]

The table-column reference DatabaseUsers[UserRoleNumber] produces a list of the UserRoleNumber column values from all rows of the DatabaseUsers table. Each row’s UserRoleNumber column value is itself a list. So you end up with a list of lists, which is an entirely different beast than the list of numbers you’re probably expecting.

To get a list of all numbers that occur in all of the UserRoleNumbers lists:

EXTRACTNUMBERS(DatabaseUsers[UserRoleNumbers])

See also:

help.appsheet.com

EXTRACTNUMBERS()

Extract list of numeric values within text value

help.appsheet.com

List from Table-Column Reference

Create a list from the values in a column of a table.

[_redline]

Huh…I strangely keep receiving the same error “EXTRACT has invalid inputs”.
SUM([UserRoleNumber]) still yields a number but EXTRACTNUMBERS(DatabaseUsers[UserRoleNumber]) flags me with the same error.

[Steve]

Ugh. The EXTRACT…() functions do have some bugs…

What about this?

EXTRACTNUMBERS("" & DatabaseUsers[UserRoleNumbers])

[_redline]

Steve:

EXTRACTNUMBERS("" & DatabaseUsers[UserRoleNumbers])

Bingo! So for the meantime, I just need to concat some void value if it throws an error like that?

[Steve]

I this case, concatenating a null text value converts the list of list of numbers to Text, which is what the EXTRACT…() functions expect as input: textual values.

[_redline]

I see, and the EXTRACTNUMBERS() just pulls out all of the numbers after expanding the DatabaseUsers[UserRoleNumber] and concatenating the void text resulting in just a one-dimensional list.

[_redline]

@Steve, sorry to bump this thread again, but along the same vein of our conversation above, I keep coming across a problem using these EnumLists (and potentially other datatypes) in “Row filter conditions” for table slices. For some reason or another, using certain expressions seems to not visibly show the same result as it does in the “test” view of the expression.

See this comment I added the other day on a similar thread here: "Row filter condition" displaying incorrectly

After further investigation, the expression works fine when applied directly to a table source itself under the “Security filter” option, but only displays the first row evaluated as boolean TRUE when using the slice row filter while ignoring all others. Do you think that this is potentially a bug, or am I missing something?

Another similar topic can be found here where I also further elaborate a similar issue, but in a different context: Slice row filter conditions work properly in the test pane, but I don't get any rows when actually p....

[_redline]

Might I also add that I am seeing similar issues from the table “Security row filter” option as well. Expression test yields different results (usually excluding results that pass as boolean TRUE) than what shows in the viewer.

[Marc_Dillon]

Provide an APPSETTINGS[] list similar to USERSETTINGS[]

I’m having a lot of issues with Slice Row Filtering right now. See the following thread; maybe you have some input:

Yikes…

My input would be that it seems like you started out with an incredibly complex system for your roles/permissions, which is causing you lots of trouble with everything that you build on top of it. I’d suggest figuring out a much simpler way to set up your roles/permissions.

[_redline]

Thanks for your input Marc, sadly, the user-role system reflects the purpose of the app. There are a lot of different user types that need to dig into different parts of the app so it needs to be rather robust (or complicated). What strikes me as strange though is that the conditional filters on the slices clearly yield a boolean true but still don’t show certain views. It’s like it allows a user a view in testing the expression, but somewhere in between the slice and the view, something blocks the view from appearing. Really just a strange situation.

[_redline]

Just wanted to bump this to say that I’ve been able to drill down into the issue and with implementing Marc’s recommendations I’m able to get the permissions to work fine as a table security filter but still yielding similar errors when using the exact same expression in a slice with no security filter.

One oddity that I found when using the slice row filter is that since I’m essentially comparing the minimum value of a number based EnumList against a column of numbers, the first number in the EnumList seems to dictate how the row filter…filters regardless of what the expression test says.