Dynamic RLS Question

[leilei787]

Keeping the question simple.....I have a dashboard that allow territory manager to see the whole region. I want to use territory table in our SFDC to do the dynamic RLS. However, in SFDC, each territory can only see their own territory and account. What can i do? 

More detail below: 

Happy Friday Everyone....i have questions about creating dynamic RLS using our SFDC report

I have a powerbi dashboard that allows Territory manager and Regional manager to see their own region. (Not specific Account or own territory. For example, territory managers in 1101, 1102, 1103 can all see Appalachian region. They can see each other but can not see other regions ). This report is currently using manual method to add new hire or make changes. it works fine. 

i want to leverage our Salesforce to create Dynamic RLS...but i am having trouble to setup the logic. Specifically the territory table in SFDC filters individual territory numbers (see below)......for example, if the territory manager in 1101 log into PBI, the territory table will filter 1101 which then point to Appalachian (connected through territory manager ID) in the Geo Mapping Table ( which connected to the main shipping table).......But is shipping report going to allow territory manager in 1101 to see the whole Appalachian? (My feeling is that it will not, only show 1101 individual territory) if not....should i connect Region field in territory table to the Region field in the Geo map table? that will become many to many relationship....will it work?

Here are the sample tables ( And sample data at the end)

Table one: Shipping Detail ----- this report has all shipping data such as order date, Customer name, Territory number, Item and $$

Table two: Geo Map Transition ---- this report has Geo mapping such as territory number, Region number, names, etc

Table Three: Territory SFDC----- this report is from our SFDC which has territory info such as ID, name, TM ID, Region name, TM username, AD username, etc

this is how each table is linked:

Using userprincipalname()

Shipping table data

OrderDate	Cust #	Cust Name	Cust Zip	Territory ID	Terriotory Name	Item	Total
1/6/2021	4224	Intersection	4662	1202	Quincy Jones	Pencil	189.05
1/23/2021	524455	Nevsky Cop	98022	1203	Johan Ramsay	Binder	999.5
2/9/2021	2445	Perspective Korp	90943	1201	William Jardine	Pencil	179.64
2/26/2021	2434	In touch	45345	1102	Avtar Gill	Pen	539.73
3/15/2021	5324	Driveway	24252	1401	Paul Sorvino	Pencil	167.44
4/1/2021	4553	Magnet	24553	1202	Quincy Jones	Binder	299.4
4/18/2021	5335	Nori	14566	1101	Andrews Mcmeel	Pencil	149.25
5/5/2021	5634	Nardis	74356	1201	William Jardine	Pencil	449.1
5/22/2021	6345	LG	32222	1402	Emma Thompson	Pencil	63.68
6/8/2021	6433	IBM	78554	1202	Quincy Jones	Binder	539.4
6/25/2021	3566	Infosys	35678	1301	Morgan Freeman	Pencil	449.1
7/12/2021	8964	Blue Star	34567	1103	Howard Hewett	Binder	57.71
7/29/2021	8558	Philips	63463	1302	Tyron Waller	Binder	1619.19
8/15/2021	4478	walmart	35785	1202	Quincy Jones	Pencil	174.65
9/1/2021	47643	Amazon	75567	1303	Joseph Smith	Desk	250
9/18/2021	23567	Cvs Health	84573	1202	Quincy Jones	Pen Set	255.84
10/5/2021	27684	Mckesson	68765	1301	Morgan Freeman	Binder	251.72
10/22/2021	45453	Microsoft	46785	1202	Quincy Jones	Pen	575.36
11/8/2021	24568	ATT	75644	1302	Tyron Waller	Pen	299.85
11/25/2021	45435	Tmobile	45674	1203	Johan Ramsay	Pen Set	479.04
12/12/2021	456456	Sprint	85674	1303	Joseph Smith	Pencil	86.43
12/29/2021	5565	Comcast	85457	1302	Tyron Waller	Pen Set	1183.26
1/15/2022	34343	Kroger	45456	1102	Avtar Gill	Binder	413.54
2/1/2022	22435	Cigna	46786	1303	Joseph Smith	Binder	1305
2/18/2022	4546	Costco	95654	1202	Quincy Jones	Binder	19.96
3/7/2022	5655	UPS	56326	1401	Paul Sorvino	Binder	139.93
3/24/2022	43356	Fedex	33676	1201	William Jardine	Pen Set	249.5
4/10/2022	34356	USPS	86566	1101	Andrews Mcmeel	Pencil	131.34
4/27/2022	3567	HD	45785	1103	Howard Hewett	Pen	479.04
5/14/2022	788355	Lowes	96755	1102	Avtar Gill	Pencil	68.37
5/31/2022	744907	Ford	57855	1102	Avtar Gill	Binder	719.2
6/17/2022	457980	Tesla	85558	1203	Johan Ramsay	Desk	625
7/4/2022	57809	Toyota	46768	1202	Quincy Jones	Pen Set	309.38
7/21/2022	34354	Honda	87656	1301	Morgan Freeman	Pen Set	686.95
8/7/2022	446576	Verizon	75654	1203	Johan Ramsay	Pen Set	1005.9
8/24/2022	346789	GM	76443	1401	Paul Sorvino	Desk	825
9/10/2022	453368	IBM	74578	1102	Avtar Gill	Pencil	9.03
9/27/2022	677899	HP	85644	1401	Paul Sorvino	Pen	151.24
10/14/2022	45779	Boeing	54355	1402	Emma Thompson	Binder	1139.43
10/31/2022	7899	Intel	23453	1101	Andrews Mcmeel	Pencil	18.06
11/17/2022	56799	Pfizer	32325	1201	William Jardine	Binder	54.89
12/4/2022	566899	State Farm	24453	1201	William Jardine	Binder	1879.06
12/21/2022	6447878	Humana	78564	1101	Andrews Mcmeel	Binder	139.72

Geo Map Translation Data

Territory ID	Territory Name	Region ID	Region Name	Region Manager	Area
1101	Andrews Mcmeel	1100	Appalachian	Ziggy Vu	East
1102	Avtar Gill	1100	Appalachian	Ziggy Vu	East
1103	Howard Hewett	1100	Appalachian	Ziggy Vu	East
1201	William Jardine	1200	California	Yahya Walsh	West
1202	Quincy Jones	1200	California	Yahya Walsh	West
1203	Johan Ramsay	1200	California	Yahya Walsh	West
1301	Morgan Freeman	1300	Southeast	Gage Foster	East
1302	Tyron Waller	1300	Southeast	Gage Foster	East
1303	Joseph Smith	1300	Southeast	Gage Foster	East
1401	Paul Sorvino	1400	Southwest	Beth Couch	West
1402	Emma Thompson	1400	Southwest	Beth Couch	West

Territory SFDC data

Territory ID	Region__c	Territory_Manager__c	Region_Manager__c	Region Name	TM Name	TM Username	RM Username
1101	a0N6100000uMkXPEA0	00561000001pY2hAAE	00561000001gIDfAAM	Appalachian	Andrews Mcmeel	Andrews.Mcmeel@msn.com	Ziggy.Vu@msn.com
1102	a0N6100000uMkXPEA0	00541000002KUYXAA4	00561000001gIDfAAM	Appalachian	Avtar Gill	Avtar.Gill@msn.com	Ziggy.Vu@msn.com
1103	a0N6100000uMkXPEA0	0054N000005Y2Q1QAK	00561000001gIDfAAM	Appalachian	Howard Hewett	Howard.Hewett@msn.com	Ziggy.Vu@msn.com
1201	a0N6100000uMkXPEA0	00561000001KUYXAA4	00561000001gIDfAAM	California	William Jardine	William.Jardine@msn.com	Yahya.Walsh@msn.com
1202	a0N6100000uMkXPEA0	005610000032fqPAAQ	00561000001gIDfAAM	California	Quincy Jones	Quincy.Jones@msn.com	Yahya.Walsh@msn.com
1203	a0N6100000uMkXPEA0	00563000002KUYXAA4	00561000001gIDfAAM	California	Johan Ramsay	Johan.Ramsay@msn.com	Yahya.Walsh@msn.com
1301	a0N6100000uMkXPEA0	00561000003oYUDAA2	00561000001gIDfAAM	Southeast	Morgan Freeman	Morgan.Freeman@msn.com	Gage.Foster@msn.com
1302	a0N6100000uMkXPEA0	00761000001pY2hAAE	00561000001gIDfAAM	Southeast	Tyron Waller	Tyron.Waller@msn.com	Gage.Foster@msn.com
1303	a0N6100000uMkXPEA0	0054N000004Y2Q1QAK	00561000001gIDfAAM	Southeast	Joseph Smith	Joseph.Smith@msn.com	Gage.Foster@msn.com
1401	a0N6100000uMkXPEA0	0054o000004Y2Q1QAK	00561000001gIDfAAM	Southwest	Paul Sorvino	Paul.Sorvino@msn.com	Beth.Couch@msn.com
1402	a0N6100000uMkXPEA0	00561000002KUYXAA4	00561000001gIDfAAM	Southwest	Emma Thompson	Emma.Thompson@msn.com	Beth.Couch@msn.com

[lbendlin]

Your RLS is ineffective as it can be overwritten by the Geo Map Translations filters.

Territory assignments should be fully spelled out in SFDC, and you should be able to base your RLS exclusively on the assignments object.

[leilei787]

thanks Ibendin.....i can get ride of geo mapping table and solely use Territory table in SFDC ( i can incorporate all Geo map info into SFDC).....but in SFDC, each TM can only see their own territory and account.....i am not sure how to leverage it for my PBI dashboard which allow territory manager to view the whole region. 

i feel i still need a middle table to do it though

[lbendlin]

I have no right to criticize your SFDC setup but it sure sounds weird.

Yes, if you can combine the tables you will be better off.  This true in 99% of the cases where you have a bidirectional 1:1 relationship betweeen tables.

[leilei787]

thanks Ibendlin. i am not SFDC admin in the company. so i have to use what they can provide.

so if i understand correctly....if region field lives in shipping detail table and Territory SFDC table, i can link the two tables by region.....it is gonna be many to many relationship.....will it work? 

i guess the logic is that if 1101 territory manager log into PBI, Territory table will point it to Appalachian region....which in turn filter the data in the shipping detail for Appalachian only, right?

thanks again

[lbendlin]

This will work even with M:M relationships BUT the search directiion must be one way from the RLS table to the fact table.

[leilei787]

@tamerj1 hi Tamerj1, do you know any solution on this issue? appreciate your help!