Filtering gallery by unique username(Office365)

[Anonymous]

Hello everyone,

Wasn't sure where to post this so will do in this section.

I have a Sharepoint list as my database origin, as seen here:

It has as title the project name (Project 1,2,3), daysDIFF of the difference in days of start date and end date, name of task (tarea1,2,3), start date, end date, real end date, person responsible of task (this is with office365 accounts) and comment.

I have already a DISTINCT in the dropdown list:

I use that one as filter in the Gallery and ordered by the end date ("Fecha Termino"):

Now, as a final filter, I need to show ONLY those of which the user who is seeing the application is in the column "Encargado" of the Sharepoint list, which has people's name using Office365 accounts (and their emails).

Is there any way to filter by using User().Email / Fullname and comparing it to the ones on the sharepoint list, and thus show only those of the user viewing the app?

Any help would be greatly appreciated.

Thanks in advance,

[RandyHayes · Accepted solution]

@Anonymous  give this a try in your filter.

   SortByColumns(Filter(demo_project; Título=ListaPryectos.Selected.Value && User().Email in ShowColumns(Encargado, "Email")); "Fecha_x0020_Termino";If(SortDescending1;Descending;Ascending))

The simplicity of this is that it is turning the Encargado field into a single column table of emails and then just throwing the "In" operator at it.

Hope this helps.  ¡Buena suerte!

[iAm_ManCat]

Ok but what happens when they reach more than 500 records, as it won't be able to filter the records anymore past the limit set for the App - 'in' is not currently a delegable command for SharePoint 🙂

Additionally, @Anonymous is comparing whether the values are identical (does their email = that email in the column), so there's no need for an 'in' when the values need to be equal 🙂

For filtering efficiency purposes, you should set a variable 'UserEmail' in the App's OnStart (as we don't want it calling the User().Email Function every time for each and every every record it is checking against:

Set(UserEmail, User().Email)

This way it is only referencing a local variable when doing comparisons during the filtering, which will be a lot faster.

Then we slightly tweak the formula:

SortByColumns(
                          Filter(

                                    demo_project;

                                    Título=ListaPryectos.Selected.Value

                                       &&

                                    Encargardo = UserEmail
                           );
                          "Fecha_x0020_Termino";
                          If(
                              SortDescending1;
                              Descending;
                              Ascending
                          )
)

[Anonymous]

@RandyHayes 

Your solution works for what I wanted, thank you. However, it gives the warning of the yellow triangle of delegation. Is there any way to solve that?

@iAm_ManCat 

Your solution, sadly, didn't work for me. Maybe as another way? As to avoid the delegation problem.

The column in Sharepoint that has the people in it, is both name and email since it is linked to office365 accounts, so maybe it is that?

[RandyHayes · Accepted solution]

@iAm_ManCatthe original question dealt with Multiple people in the people column.  This was evident in the picture showing the data source from SharePoint.   If you look at the second record, you will see two names listed in the Encargado column. Thus, an equality operator will not work in this case and the use of the In operator was necessary.

As well, the Encargado column is a people column and thus is not an email address with which to do an equality comparison to UserEmail.  You would need to compare against Encargado.Email...which is not delegable. 

@Anonymousunfortunately, there is not a good work around for the delegation issue on that type of field.  First, consider if you will be dealing with more than 500 records in that list.  If not, then ignore the delegation warning.

If so, then you will need to consider alternate methods, either at your dataSource (SharePoint), or through local Collections.  

If through SharePoint, you will need to abandon the use of the Person type column, or at least augment the column with another column that is a string type (as some formulas for SharePoint can be delegated).  This could be a challenge for the multiple selection feature you are using.

If through local Collections, then you will need to gather all the records to a local collection and then you can filter against that without delegation issues.  However, there are additional challenges that come along with that patterns.

Hope this is helpful.

[Anonymous]

Thank you so much @RandyHayes

It does work, like I think I said before (if I didn't, saying it now), and regarding the delegation problem, I think that I won't have more than 500 users. If I did have, could you link me some documentation to see the local collection topic? I am kind of (very) new to PowerApps, so don't know much of it yet but am discovering by doing, so to speak 😛

[RandyHayes]

No problem.  I don't have a specific link to point to for details on collecting locally to avoid delgation issues.  I tend to find it is very specific to your needs in each case. But, certainly if you run into the need, feel free to reach out direct or post again.