Hi All, I’ve been reading up on different way...

(Simon Adcock) #1

Hi All, I’ve been reading up on different ways to filter user access to rows, views and actions etc. using email/domain. My app is used to collect visit data from several (4) subcontractors for my own team to review. Each subcontractor will have multiple users and ideally we will just invite them using the whitelist. My table has a column with the name of each contractor and I was hoping that somewhere within the app I could extract the domain portion of USEREMAIL() using text operators, collect the associated contractor name using a lookup table (domain/contractor) and use that to filter rows etc. I’ve tried configuring one of the USERSETTINGS to do this but it’s not reliable.

Two more things (non essential) a) I’d like to reserve the admin/user setting for other controls b) ideally I would like to be able to use a USERSETTING with access restricted to admin roles, to simulate what each contractor can see for testing.

Any ideas? Thanks

(Suvrutt Gurjar) #2

Hi @Simon_Adcock,

Here is a suggested approach for selecting rows by domain based on my understanding of your need.

A) Please explore creating a column called say [Contractor Domain] in the said table with expression such as

=IF([Contractor]=“Contractor 1”, “contractor1.com”, IF([Contractor]=“Contractor 2”, “contractor2.com”,IF([Contractor]= “Contractor 3”, “contractor3.com”, “Yet to enter domain”)))

B) In Security filter ,slices etc. you can use an expression like

[Contractor Domain]=SUBSTITUTE(USEREMAIL(), LEFT(USEREMAIL(),(FIND(@,USEREMAIL()))), “”)

So if the user belongs to a particular contractor, he sees rows of that contractor or that particular domain only.

Hope this helps.

(Simon Adcock) #3

@Suvrutt_Gurjar thanks for taking the time to reply. I have the [Contractor Domain] column in my table already. I’m calculating it using a lookup table as its more scalable and elegant than nested loops but basically we’re on the same page on this one. Ideally though I would like just to use the contractor column and have Appsheet lookup the domain.

For the row filter I’m doing what you suggest although I wasn’t aware of the SUBSTITUTE expression which is a definite improvement on the combination of LEN, LEFT, FIND, and RIGHT that I’m using. Thank you very much for that. What I would like to get away from Suvrutt is having to use that domain extraction in every filter, sometimes twice, in each one.

Is there no global value I can use to calculate it, and then just use that value in expressions? Next, but less important, I’d like to get rid of the domain column in the table, and look that up within the app. I tried these using the USERSETTINGS() but it proved unreliable. i…e - worked sometimes.

(Suvrutt Gurjar) #4

Hi @Simon_Adcock, On avoiding {Domain Name} column, as per my understanding,since you are allowing access to rows through domain name, extracted from user email,there will be domain identity needed for each row. You can rid of the column but somewhere , in expression, [Contractor Name]will need to be compared to compute corresponding domain.

Can you explore populate domain name column when you populate contractor name? This will avoid calculated domain name.

On reducing filters, I also request you to explore using Security filter.This will help in only respective contractor data reaching corresponding contractor user’s device. It may also reduce some more filters and slices etc?