Filter Gallery by current user Dynamics 365 Canvas App

[Anonymous]

I am working on a Dynamics365 Canvas App and trying to show the data by current user.

Scenario

I have created a drop down list for Open, Qualified, Disqualified and My Open Leads. By using the following formula, eveything is working fine except My Open Leads.

SortByColumns(Search(Filter(Leads,If(LeadDropDown.SelectedText.Value="Open Leads",_statecode_label="Open",LeadDropDown.SelectedText.Value="Disqualified Leads",_statecode_label="Disqualified",LeadDropDown.SelectedText.Value="All Leads",(_statecode_label="Open"||_statecode_label="Disqualified"||_statecode_label="Qualified"))),TextSearchBox_Lead.Text,"subject"),"subject",If(SortDescending1,Descending,Ascending))

I tried the instructions on the blog by but this is not working for me.

https://www.cloudfronts.com/filter-records-in-powerapps-based-on-the-current-user-of-dynamics-crm/#c...

What is the best method for filtering the data by current user? I can get the Onwerid/Username but I do not know how to incorporate this in the formula. Any help on this will be greatly appreciated. 

[v-xida-msft]

Hi @Anonymous ,

Could you please share a bit more about your scenario?

Do you want to filter your Gallery Items based on current login user, and filter these records which created by current sign user?

Based on the needs that you mentioned, I think the "Created By" property and User() function could achieve your needs. I have made a test on my side, plase take a try with the following workaround:

Set the Items property of the Gallery to following formula:

SortByColumns(
              Search(
                     Filter(
                             Leads, 
                             If(
                                LeadDropDown.SelectedText.Value = "Open Leads", _statecode_label="Open",
                                LeadDropDown.SelectedText.Value = "Disqualified Leads", _statecode_label="Disqualified",
                                LeadDropDown.SelectedText.Value = "All Leads", (_statecode_label = "Open" || _statecode_label = "Disqualified" || _statecode_label = "Qualified")
                             ),
                             'Created By'.'Primary Email' = User().Email     /* <-- Modify your formula here */
                     ),
                     TextSearchBox_Lead.Text, "subject"
              ),
              "subject",
               If(SortDescending1, Descending, Ascending)
)

If you could not get the "Create By" property from your Leads Entity, please check if you have turned on the "Relation data, option sets, and other new features for CDS" option within Advanced settings of App setting of your app.

Or modify above formula as below:

SortByColumns(
              Search(
                     Filter(
                             Leads, 
                             If(
                                LeadDropDown.SelectedText.Value = "Open Leads", _statecode_label="Open",
                                LeadDropDown.SelectedText.Value = "Disqualified Leads", _statecode_label="Disqualified",
                                LeadDropDown.SelectedText.Value = "All Leads", (_statecode_label = "Open" || _statecode_label = "Disqualified" || _statecode_label = "Qualified")
                             ),
                             createdby.internalemailaddress = User().Email     /* <-- Modify your formula here */
                     ),
                     TextSearchBox_Lead.Text, "subject"
              ),
              "subject",
               If(SortDescending1, Descending, Ascending)
)

Please consider take a try with above solution, then check if the issue is solved.

If you want to filter your Gallery Items based on Owner field, please consider take a try with the following workaround:

Your need to add Users Entity and Teams Entity as data source within your app firstly, then set the Items property of the Gallery to following:

SortByColumns(
              Search(
                     Filter(
                             Leads, 
                             If(
                                LeadDropDown.SelectedText.Value = "Open Leads", _statecode_label="Open",
                                LeadDropDown.SelectedText.Value = "Disqualified Leads", _statecode_label="Disqualified",
                                LeadDropDown.SelectedText.Value = "All Leads", (_statecode_label = "Open" || _statecode_label = "Disqualified" || _statecode_label = "Qualified")
                             ),
                             If(  /* <-- Modify your formula here */
                                IsType(Owner, Users), 
                                AsType(Owner, Users).'Full Name' = User().FullName, 
                                AsType(Owner, Teams).'Team Name') = User().FullName
                            )  
                     ),
                     TextSearchBox_Lead.Text, "subject"
              ),
              "subject",
               If(SortDescending1, Descending, Ascending)
)

Note: The Owner represents the Owner field in your Leads Entity. On your side, it may be ownerid. So you may type the following formula:

SortByColumns(
              Search(
                     Filter(
                             Leads, 
                             If(
                                LeadDropDown.SelectedText.Value = "Open Leads", _statecode_label="Open",
                                LeadDropDown.SelectedText.Value = "Disqualified Leads", _statecode_label="Disqualified",
                                LeadDropDown.SelectedText.Value = "All Leads", (_statecode_label = "Open" || _statecode_label = "Disqualified" || _statecode_label = "Qualified")
                             ),
                             If(  /* <-- Modify your formula here */
                                IsType(ownerid, Users), 
                                AsType(ownerid, Users).'Full Name' = User().FullName, 
                                AsType(ownerid, Teams).'Team Name') = User().FullName
                            )  
                     ),
                     TextSearchBox_Lead.Text, "subject"
              ),
              "subject",
               If(SortDescending1, Descending, Ascending)
)

Please check the following blog for more details:

https://powerapps.microsoft.com/en-us/blog/owner-customer-and-regarding-for-canvas-apps/

Best regards,

[Anonymous]

Hi @v-xida-msft 

Thank you very much for this. I just forgot to mention that I have another Option in the drop dow list named 'My Open Leads' and I want to apply this filter on this option, for example when user selects 'My Open Leads' then the results should show the Leads owned by the current user.

To Clarify, I want to show the records, owned by the current sign user. If the current signed in user selects 'Open Leads' from the Dropdown list, then the results should show all the Leads with Open Status and when the current signed in user selects 'My Open Leads' then the results should show only Leads with Open Status owned by the current user.

Also I am using Dynamics365 Datasource not CDS.

[v-xida-msft]

Hi @Anonymous ,

Based on the needs that you mentioned, I have made a test on my side, please consider modify above formula as below:

Your need to add Users Entity as data source within your app firstly, then set the Items property of the Gallery to following:

SortByColumns(
              Search(
                     Filter(
                             Leads, 
                             If(
                                LeadDropDown.SelectedText.Value = "Open Leads", _statecode_label="Open",
                                LeadDropDown.SelectedText.Value = "Disqualified Leads", _statecode_label="Disqualified",
                                LeadDropDown.SelectedText.Value = "All Leads", (_statecode_label = "Open" || _statecode_label = "Disqualified" || _statecode_label = "Qualified"),
                                LeadDropDown.SelectedText.Value = "My Open Leads", _statecode_label="Open" && AsType(ownerid, Users).'Full Name' = User().FullName /* <-- Modify formula here */
                             )
                     ),
                     TextSearchBox_Lead.Text, "subject"
              ),
              "subject",
               If(SortDescending1, Descending, Ascending)
)

Note: The Owner represents the Owner field in your Leads Entity. On your side, it may be ownerid. So you may type the following formula:

SortByColumns(
              Search(
                     Filter(
                             Leads, 
                             If(
                                LeadDropDown.SelectedText.Value = "Open Leads", _statecode_label="Open",
                                LeadDropDown.SelectedText.Value = "Disqualified Leads", _statecode_label="Disqualified",
                                LeadDropDown.SelectedText.Value = "All Leads", (_statecode_label = "Open" || _statecode_label = "Disqualified" || _statecode_label = "Qualified"),
                                LeadDropDown.SelectedText.Value = "My Open Leads", _statecode_label="Open" && AsType(Owner, Users).'Full Name' = User().FullName       /* <-- Modify formula here */
                             )
                     ),
                     TextSearchBox_Lead.Text, "subject"
              ),
              "subject",
               If(SortDescending1, Descending, Ascending)
)

Please take a try with above solution, then check if the issue is solved.

Best regards,

[Anonymous]

Hi @v-xida-msft 

Thanks for the quick reply. I have add Users as Data Source and followed the instructions as suggested in your post but it is showing that The function 'AsType' has some invalid arguments.

Note: I tried both Owner and ownerid in the formula

When I enetered this 

SortByColumns(Search(Filter(Leads,If(LeadDropDown.SelectedText.Value="Open Leads",_statecode_label="Open",LeadDropDown.SelectedText.Value="Disqualified Leads",_statecode_label="Disqualified",LeadDropDown.SelectedText.Value="All Leads",_statecode_label="Open"||_statecode_label="Disqualified"||_statecode_label="Qualified", LeadDropDown.SelectedText.Value = "My Open Leads",_statecode_label = "Open" && AsType(ownerid,Users).'Full Name' = User().FullName)),TextSearchBox_Lead.Text,"subject", "fullname"),"subject",If(SortDescending1,Descending,Ascending))

Then all the dropdown options do not return the results. When I remove this code

LeadDropDown.SelectedText.Value = "My Open Leads",_statecode_label = "Open" && AsType(ownerid,Users).'Full Name' = User().FullName

from the formula then Open Leads, Disqualified Leads and All Leads return the results. 

I am still unable to get the results for My Open Leads.

[v-xida-msft]

Hi @Anonymous ,

Based on the formula that you mentioned, I think there is something wrong with the column name (ownerid) you typed within your formula.

Please consider turned on the "Relation data, option sets, and other new features for CDS" option within Advanced settings of App setting of your app, and then modify your formula as below:

SortByColumns(
             Search(
                     Filter(
                             Leads,
                             If(
                                  LeadDropDown.SelectedText.Value="Open Leads",
                                  _statecode_label="Open",
                                  LeadDropDown.SelectedText.Value="Disqualified Leads",
                                  _statecode_label="Disqualified",
                                  LeadDropDown.SelectedText.Value="All Leads",
                                  _statecode_label="Open"||_statecode_label="Disqualified"||_statecode_label="Qualified", 
                                 LeadDropDown.SelectedText.Value = "My Open Leads",
                                 _statecode_label = "Open" && AsType(Owner, [@Users]).'Full Name' = User().FullName  /* <-- Modify formula here */
                              )
                    ),
                   TextSearchBox_Lead.Text,
                  "subject", "fullname"
             ),
             "subject",
             If(SortDescending1,Descending,Ascending)
)

Or

SortByColumns(
             Search(
                     Filter(
                             Leads,
                             If(
                                  LeadDropDown.SelectedText.Value="Open Leads",
                                  'State Code'="Open",
                                  LeadDropDown.SelectedText.Value="Disqualified Leads",
                                  'State Code'="Disqualified",
                                  LeadDropDown.SelectedText.Value="All Leads",
                                  'State Code'="Open"||'State Code'="Disqualified"||'State Code'="Qualified", 
                                  LeadDropDown.SelectedText.Value = "My Open Leads",
                                 'State Code' = "Open" && AsType(Owner, [@Users]).'Full Name' = User().FullName  /* <-- Modify formula here */
                              )
                    ),
                   TextSearchBox_Lead.Text,
                  "subject", "fullname"
             ),
             "subject",
             If(SortDescending1,Descending,Ascending)
)

Note: The 'State Code' represents the State Code column in your Entity. The Owner represents the Owner column in your CDS Entity.

Best regards,

[Anonymous]

Hi @v-xida-msft 

Thanks for your help and I appriciate your efforts to solve this issue.

I have turned on the "Relation data, option sets, and other new features for CDS" option within Advanced settings of App setting and then modify the formula as suggested but it is showing the following errors:

Owner: "Invalid arrgument type GUID. Expecting a Polimorphic value instead"
AsType: "Function 'AsType' has some invalid arguments" 

'Full Name': "Name is not valid, The identifier does not recohnized"

Would yo think this is because I am using Dynamics 365 Data Source rather than CDS?

[v-xida-msft]

Hi @Anonymous ,

The Entities you created in your Dynamics 365 instance would also be existed within your corresponding CDS in your PowerApps.

Note: The Dynamics 365 instance is actually your PowerApps Environment.

Please consider create a connection to the Entity in your CDS from your app instead of the Entity in your Dynamcis 365. Then take a try with above solution I provided, check if the issue is solved.

If you want to use Dynamics 365 Entity as data source, I afraid that there is no way to filter your Entity reocrds based on Owner field within PowerApps currently.

Best regards,

[Anonymous]

Hi @v-xida-msft 

I created a connection of Lead entity in CDS and applied the suggested solution, however, the solution does not work.

_statecode_label and 'State Code' are not recognisable. I have add 'Status' field but solution is not returning any results.

I am getting six similar errors:

Issue

Incompatible type. We can't evalute your formula because the values being comparedin the formula aren't the same type.

Location

BrowserGallery1.Items

How to fix

You might need to convert the value to be the same type, such as converting a date string (e.g., "12/31/2018" to a date value.)

if you're comparing records or tables, the field or column types must match exactly.

SortByColumns(
Search(
Filter(
Leads,
If(
Dropdown1.SelectedText.Value="Open Leads",
Status="Open",
Dropdown1.SelectedText.Value="Disqualified Leads",
Status="Disqualified",
Dropdown1.SelectedText.Value="All Leads",
Status="Open"||Status="Disqualified"||Status="Qualified", 
Dropdown1.SelectedText.Value = "My Open Leads",
Status = "Open" && AsType(Owner, [@Users]).'Full Name' = User().FullName
)
),
TextSearchBox1.Text,
"subject", "fullname"
),
"subject",
If(SortDescending1,Descending,Ascending)
)

I tried th efollowing formula as well with Status values as 0,1,2 and Open, Qualified, Disqualfied but same results

SortByColumns(
Search(
Filter(
Leads,
If(
Dropdown1.SelectedText.Value="Open Leads",
'Status (Leads)'="0",
Dropdown1.SelectedText.Value="Disqualified Leads",
'Status (Leads)'="1",
Dropdown1.SelectedText.Value="All Leads",
'Status (Leads)'="0"||'Status (Leads)'="1"||'Status (Leads)'="2", 
Dropdown1.SelectedText.Value = "My Open Leads",
'Status (Leads)' = "0" && AsType(Owner, [@Users]).'Full Name' = User().FullName
)
),
TextSearchBox1.Text,
"subject", "fullname"
),
"subject",
If(SortDescending1,Descending,Ascending)
)

[v-xida-msft]

Hi @Anonymous ,

Could you please share a bit more about the error message within your app?

Further, could you please show more details about the Status field in your CDS Entity? Is it a Option Set type column?

If the Status column is a Option Set type column in your CDS Entity, I think there is something wrong with your formula. On your side, please consider modify your formula as below:

SortByColumns(
              Search(
                     Filter(
                            Leads,
                            If(
                               Dropdown1.SelectedText.Value="Open Leads",
                               Status = Status.Open,
                               Dropdown1.SelectedText.Value="Disqualified Leads",
                               Status= Status.Disqualified,
                               Dropdown1.SelectedText.Value="All Leads",
                               Status= Status.Open || Status= Status.Disqualified || Status = Status.Qualified, 
                               Dropdown1.SelectedText.Value = "My Open Leads",
                               Status = Status.Open && AsType(Owner, [@Users]).'Full Name' = User().FullName
                             )
                     ),
                     TextSearchBox1.Text,
                     "subject", "fullname"
             ),
             "subject",
              If(SortDescending1,Descending,Ascending)
)

Or

SortByColumns(
              Search(
                     Filter(
                            Leads,
                            If(
                               Dropdown1.SelectedText.Value="Open Leads",
                               'Status (Leads)' = 'Status (Leads)'.Open,
                               Dropdown1.SelectedText.Value="Disqualified Leads",
                               'Status (Leads)'= 'Status (Leads)'.Disqualified,
                               Dropdown1.SelectedText.Value="All Leads",
                              'Status (Leads)'= 'Status (Leads)'.Open || 'Status (Leads)'= 'Status (Leads)'.Disqualified ||'Status (Leads)' = 'Status (Leads)'.Qualified, 
                               Dropdown1.SelectedText.Value = "My Open Leads",
                              'Status (Leads)' ='Status (Leads)'.Open && AsType(Owner, [@Users]).'Full Name' = User().FullName
                             )
                     ),
                     TextSearchBox1.Text,
                     "subject", "fullname"
             ),
             "subject",
              If(SortDescending1,Descending,Ascending)
)

Note: If the Status column is a Option Set type column in your CDS Entity, you should use the following formula to reference the available options:

Status.OptionValue1
Status.OptionValue2
...

Please also check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-apps/

Best regbards,