Create Slice based on USEREMAIL()

John_Henry_Jose · 11-21-2020 04:58 AM

I need to create a Slice based on the USEREMAIL() to verify either he has a right to view particular country data.
This is the table, I need to search.

AND
(
[IsPosted]=TRUE,
IN(USEREMAIL(), SELECT(Tbl_Businessunits[RepresentativeEmailID], [BusinessUnit]=[_THISROW].[xBusinessUnit])))

The above condition does not work as expected.

Any idea please.

Thanks in advance.
John[quote=“John_Henry_Joseph, post:1, topic:34885, full:true”]
I need to create a Slice based on the USEREMAIL() to verify either he has a right to view particular country data.
This is the table, I need to search.

AND
(
[IsPosted]=TRUE,
IN(USEREMAIL(), SELECT(Tbl_Businessunits[RepresentativeEmailID], [BusinessUnit]=[_THISROW].[xBusinessUnit])))

The above condition does not work as expected.

Any idea please.

Thanks in advance.
John
[/quote]

MultiTech

Hey @John_Henry_Joseph, you’re thinking about things in the right way!

In just about every app I create, one of the first systems I create is exactly what you’re talking about.

Here’s my suggestion;

Split out the user slice into it’s own thing. I typically call this “Current_User” with a simple formula
USEREMAIL() = [User_Login_Email]

This slice then holds that record, and that record alone (provided that there’s only one record that matches the email in the User table), and from this slice you can easily pull data from the record for whoever is currently logged in and using the app.

Here are some examples of how you can pull data from that slice:

Any(Current_User[UserID]) -> pulls the UserID value from the user record
- INDEX(Current_User[UserID], 1) -> does the same thing, but in better way
split(Current_User[BusinessUnit], " , ") -> pulls the list of countries (from your table screenshot ) and formats that into a usable list.

With the Current_User slice method you can really get some customization going.

Once you have your slice, if you use that SPLIT() formula I showed above, you can easily see if the [xBusinessUnit] is inside the authorized list of countries for your user.

IN([xBusinessUnit], SPLIT(Current_User[BusinessUnit], " , "))

LeventK

@John_Henry_Joseph

AND(
	[IsPosted]=TRUE,
	IN(
		[_THISROW].[xBusinessUnit],
		SPLIT(
			LOOKUP(
				USEREMAIL(),
				"Tbl_Businessunits",
				"RepresentativeEmailID",
				"BusinessUnit"
			)," , "
		)
	)
)