cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BillKern
Helper III
Helper III

Filtering a data source by user email domain and Office 365 Group

Hello,

 

I am creating a simple order entry app, the users are all office 365 users, and the customer list is an SQL data source called vCustomers. 

I am trying to filter this customer list based on the users email address, specifically the domain of the email, and in one case an Office 365 group that the email belongs to. 

 

My customer list has a column called "CompanyFlag"

There are 4 values that may appear in that column: Co1, Co2, Co3, Co4

 

The User email domains are one of these three

@company1.com, @company2.com, @company3.com

 

Company1, 2, and 3 all match up to Co1, Co2, and Co3 respectively, however Co4 also shares the Company1 email domain but they are in a unique Office 365 Group. When a user logs in I only want them to be able to see customers with the CompanyFlag that matches their email. 

 

My first thought was to filter this list when the app is started up, so any time I try to use the vCustomers source later on in the app the list would already be filtered. I put this in the OnStart property of the app:

 

Set(varUserEmail, User().Email);
If(EndsWith(varUserEmail, "@company1.com"), Filter('[dbo].[vCustomers]', CompanyFlag = "Co1"));
If(EndsWith(varUserEmail, "@company2.com"), Filter('[dbo].[vCustomers]', CompanyFlag = "Co2"));
If(EndsWith(varUserEmail, "@company3.com"), Filter('[dbo].[vCustomers]', CompanyFlag = "Co3"))

 

At this point I hadn't tried tackling the Office 365 Group yet for Co4 yet. Anyway this does not work. I do not get any errors with the code, but I am able to see customers from all companies. 

 

I have a search box (Text Input called CustomerNo) where you search by CustomerNumber (in vCustomers), and a gallery displays the resulting customer name. Here is the code I have for that:

 

Filter('[dbo].[vCustomers]',Value(CustomerNo.Text) = CustomerNumber)

 

This is where I need the data filtered, because if I type in a customer number with any CompanyFlag the gallery will populate, instead of filtering out results based on user email. I think this formula is where I need to add the additional filters but everything I try is filled with errors. 

 

Any help would be appreciated!

 

 

 

10 REPLIES 10

Use split and last to get the domain

Last(Split(column, ”@”))

This will return the domain in the email address and can be used in your filter

I was able to modify my search filter which seems to work, however I appear to be running into a delegation issue.. but I do not get any delegation warnings. Here is my code:

Filter(
    If(
        EndsWith(varUserEmail, "company1.com"),
           Filter('[dbo].[vCustomers]', CompanyFlag = "Co1"),
        EndsWith(varUserEmail, "company2.com"),
           Filter('[dbo].[vCustomers]', CompanyFlag = "Co2"),
        EndsWith(varUserEmail, "company3.com"),
           Filter('[dbo].[vCustomers]', CompanyFlag = "Co3")
    ),
    Value(CustomerNo.Text) = CustomerNumber
)

 Without these If statements I am able to search the whole list and get all results, but with these If statements it only loads the customer numbers with the lowest value. I'm not sure how many records are being loaded, but I assume it is the first 500 if it is indeed a delegation issue. 

 

I also still have no idea how to check if a user has the @company1.com domain AND belong to a specific Office 365 Group so I can filter the results to only display CompanyFlag Co4. 

 

Any thoughts on these two points would be appreciated!

Another update. I created a collection of the Office365Group that is only supposed to see customers with the Co4 CompanyFlag, the collection is called COMPANY4. Then I updated the search code to check if the user is in or not in that collection depending on the flag they need to see. Here's the code:

 

 

Filter(
    If(
    	EndsWith(varUserEmail,"company1.com") && !(User().Email in COMPANY4.mail),
        	Filter('[dbo].[vCustomers]',CompanyFlag = "Co1") ,
	    EndsWith(varUserEmail,"company2.com"),
        	Filter('[dbo].[vCustomers]',CompanyFlag = "Co2"),
        EndsWith(varUserEmail,"company3.com"),
        	Filter('[dbo].[vCustomers]',CompanyFlag = "Co3"),
        EndsWith(varUserEmail,"company1.com") && User().Email in COMPANY4.mail,        
            Filter('[dbo].[vCustomers]',CompanyFlag = "Co4")
    ),
    Value(CustomerNo.Text) = CustomerNumber
)

 

 

This does the filtering that I want based on the user email.

 

Now my only problem is the record limit. I do not have any delegation errors or a yellow triangle but I am only able to search for items at the beginning of the list. I created a label and put this code in to see how many rows were being called with these filters in place.

 

CountRows(CustSearch.AllItems)

 

 Only 100 rows are being called at a time, if I scroll down in the gallery to the bottom another 100 will load. But when I am trying to search for something outside that 100 rows it does not load. 

 

If I take out the If statement and use this code

 

Filter('[dbo].[vCustomers]',Value(CustomerNo.Text) = CustomerNumber)

 

 I can search for any item on the list regardless of where it is. 

 

Any thoughts on this? Is there a better function to use than If that will not cause this 100 row limit?

If is not delegable.

 

You can add all of the items to a collection, build your gallery from the collection, and filter the collection

I tried that as well, it is only bringing the first 500 rows into the collection. 

don't collect with a filter--bring all records into the collection, then filter in the gallery.

 

there is a setting in your app to increase the number of non delegated results to 2,000

 

I put this in the OnStart property of the app:

Collect(CUSTOMERS, '[dbo].[vCustomers]')

and when I use  CountRows(CUSTOMERS) to see how big the collection is it shows 500 rows. I know I can increase the limit to 2000, but the '[dbo].[vCustomers]' table is over 18,000 rows so that doesn't help much. 

This appears to be a limitation of SQL server. 

 

You have two options if you have more than 2000 records: iterative batches or flow.

 

For the iterative batch method, see https://powerusers.microsoft.com/t5/Building-Power-Apps/500-item-limit-in-CDM-entity-search-filter-n...

 

for the flow method, see https://michalguzowski.pl/how-to-overcome-500-items-limit-in-powerapps/ step 6

 

I guess the question is "do you need more than 2,000 records, or do you need to search more than 2,000 records?" typically you don;t need all of the data in the app.

Thank you I will look into both of those options. 

 

The goal here is for a sales rep to be able to enter a customer number into a text input, and have the corresponding customer name populate in a 1 item gallery next to it. So I don't need all of the records at once, I just need to be able to search through all of them to pull the record I need out of it.

 

It gets tricky because of the different filters that need to happen. The same customer number may be present in all 4 companies but have different names. 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,099)