Hi I am trying to build a Sales Application. ...

[Kartik_Borikar]

Hi I am trying to build a Sales Application. I have following tables :

1. “outletdetails”

-> Stores a list of Retail Shops with City_id column 2. Sheet1 -> Table storing a list of Cities with their corresponding

“District_id” 3. Usersloc -> Table containing user and the district_id in which the user functions

Right now in the

List view of “outletdetails”, all the records in “outletdetails” are visible. I want only those records to be visible to the user where city_id is in the allowed district_id of the user. If one user is handling more than 1 districts, another row is added in Usersloc table.

I wrote the following expression for the security filter for “outletdetails”. It should compare the district_id of the outlet, with the district_id allowed for the user.

LOOKUP([city_id],

“Sheet1”,

“city_id”, “district_id”) = LOOKUP(USEREMAIL(),

“Usersloc”, “user_email1”, “district_id”)

In spite of this, it shows all the records.

Can somebody guide where I am going wrong?

[David_Ackling-J]

You need an City table, a District table, an Outlet table, a User table and a UserOutlet table. Is it true that a City has many Disticts, a District has many outlets, an Outlet has many Users, and a User has many

Outlets? If so the Column Structure needs to be City: ID, Name,

District: ID, CityID, Name User: ID, Email Outlet: ID, DistrictID UserOutlet: OutletID, UserID

IF it is true that a District has many Cities, then the Column Structure needs to be City: ID, Name, DistrictID District: ID, Name User: ID, Email Outlet: ID, CityID UserOutlet: OutletID, UserID

If you get your table and column structure set up in a normalised way you lookups will work.

[Kartik_Borikar]

@David_Ackling-Jones Thanks for taking time to reply.

A district will have multiple cities and users shall be given access to outlets in multiple districts. “UserOutlet” won’t be feasible as there would be hundreds of outlets. Each time we need to add an employee(user) ,

it would take a lot of time.

We have the same scheme in our database, but for Appsheet we are using Google Sheets tables.

[Sdfaheemuddin]

Hi @Kartik_Borikar

I hope city_id is the key in sheet1 table. You can also make city_name as label.

In Outlet details table, make city_id as ref to table sheet1 Create a virtual column named district_id with formula [city_id].[district_id]

Now in security filters place this formula for Outlet details. [district_id]=Lookup(USEREMAIL(), “userloc”, “user_email1”,“district_id”)

[Syed_Faheem_Udd]

@Kartik_Borikar

ignore the above comment

You can use your approach but instead of [city_id] use [_thisRow].[city_id]

LOOKUP([_thisRow].[city_id],

“Sheet1”,

“city_id”, “district_id”) = LOOKUP(USEREMAIL(),

“Usersloc”, “user_email1”, “district_id”)

[Kartik_Borikar]

Hi!

I changed the column names to avoid confusion.

While testing I found that the second lookup is not able to fetch district_id.

Please see the image attached.

[David_Ackling-J]

@Kartik_Borikar If a User can have more than one District, and a District can have more than one User, you have no choice but to create a UserDistrict table. Many to many relationships require a link table of this kind or your queries will never work.