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?