cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skoopman1988
Level: Powered On

Filter and sorting modelformintegration in a gallery

Hi,

I'm trying to filter activities from Dynamics in a gallery using the [@modelformintegration].item.task. But unfortunately I get an error that the size table is to large and will not work.

Is there anyone who has experience of filtering and sorting dynamics data within a powerapp?
1 ACCEPTED SOLUTION

Accepted Solutions
PowerAddict
Level 8

Re: Filter and sorting modelformintegration in a gallery

Hi, 

 

Apologies for the delay. Just use the following formula as the Items property of the Activities gallery: 

 

SortByColumns(Filter(Activities, Regarding = [@ModelDrivenFormIntegration].Item ,  'Activity Status' = 'Activity Status (Activities)'.Completed),"modifiedon")

 

You shouldn't get any delegation warnings. Let me know if this doesn't work. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

 

View solution in original post

10 REPLIES 10
PowerAddict
Level 8

Re: Filter and sorting modelformintegration in a gallery

Hi,

Can you share a screenshot of the items property of your gallery that also highlights the exact error message you are seeing? I have created many embedded canvas apps and have not had any issues with there being a lot of data.

----
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)
skoopman1988
Level: Powered On

Re: Filter and sorting modelformintegration in a gallery

Hi Hardit,

 

Thank you for your reply. 

 

Below is the error I ment: ( a translation of the formula: filter([@ModelDrivenFormIntegration.Item.Activities, Activitystatus = 1)

activityApp_filtererror.pngFilter modeldrievenforminregration

I also want to add the Sort on the field: modifiedon, but I can not get it to work in combination with the filter. Do you have any ideas? 

Community Support Team
Community Support Team

Re: Filter and sorting modelformintegration in a gallery

Hi @skoopman1988 ,

Could you please share a bit more about the Activitystatus column? Is it a Number type column in your Activity Entity?

 

Based on the screenshot that you mentioned, I think you have faced Delegation issue with your formula. Please note that the Delegation issue is not an error, it just means that you could not delegate the data process to your data source itself from your app, instead, you could only process data locally.

In default, you could only process 500 records at most in your app locally, you could change this limit to maximum value -- 2000, then you could process 2000 records locally at most. If the amount of your activities records is not more than 2000, you could ignore this issue.

Please refer to the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

 

In addition, I also found an error with the "=" operator in your formula, please make sure the data type of the Activitystatus column in your Activity Entity is a Number type.

If the Activitystatus column is a Option Set type column in your Activity Entity, please modify your Filter formula as below:

Filter([@ModelDrivenFormIntegration.Item.Activities, Activitystatus = OptionSetName.Option1)

If the Activitystatus column is a Two Options type column in your Activity Entity, please consider modify your Filter formula as below:

Filter([@ModelDrivenFormIntegration.Item.Activities, Activitystatus = 'Activitystatus (EntityName)'.Option1)

 

If you also want to sort above Filter result based on the modifiedon clumn, please consider modify above formula as below:

SortByColumns(   // Activitystatus is Option Set type column 
              Filter([@ModelDrivenFormIntegration.Item.Activities, Activitystatus = OptionSetName.Option1),
              "modifiedon"
)

or

SortByColumns(   // Activitystatus is a Two Options type column
              Filter([@ModelDrivenFormIntegration.Item.Activities, Activitystatus = 'Activitystatus (EntityName)'.Option1),
              "modifiedon"
)

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
skoopman1988
Level: Powered On

Re: Filter and sorting modelformintegration in a gallery

Hi @v-xida-msft

 

Thanks for your complete explanation. Unfortunately I can not get it to work still.. I hope I can clarify myself:

 

I have Dynamics 365 CE and i'm using the standard "Case"-entity. On this entity i'm using the standard "Activity"-entities like Tasks, Appointments, and a custom acitivity named "Log". I am trying to get the activities which are "Completed" in my PowerApp, named "ActivityApp". I have embed this app on my "Case" form. 

 

Here some screenshots:

 

My Case-form with my acitivies:

Dynamics - Closed Avtivities.png

 

My Case form with the ActivityApp:

Dynamics - activityApp showing no records.png

 

And hereby my powerapp with the formula you told me (including the delegation warning):

activityApp - Delegation warning.png

 

My problem is that when I get this warning, my app won't work at all. When I remove the filter and all of my activities (open, closed and completed) are shown. But I only want to have the "Completed" activities. 

 

I hope my question/problem is clear. Do you have any idea?

 

With kind regards,

 

Sebastiaan

PowerAddict
Level 8

Re: Filter and sorting modelformintegration in a gallery

Yes, you need to create a gallery of cases filtered based on the ModelDrivenIntegrationForm to show the case you are on. Hide this gallery or have it on a screen that will not be exposed to a user on the Model Driven form. 

 

Then have an activities gallery which shows activities tied to the case from the other gallery and you can filter those activities on status if you want. It should work this way. 

 

Let me know if you are not able to put this to work and I can give you an example. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

skoopman1988
Level: Powered On

Re: Filter and sorting modelformintegration in a gallery

Hi @PowerAddict,

 

I have tried it: but unfortunately it wont work. 

 

Would you help me by trying out yourself and post me some screenshots? 

 

Thanks in advance!

PowerAddict
Level 8

Re: Filter and sorting modelformintegration in a gallery

Sure, give me a few minutes and I will come back with a document showing you how to do it step-by-step. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

PowerAddict
Level 8

Re: Filter and sorting modelformintegration in a gallery

Hi, 

 

Apologies for the delay. Just use the following formula as the Items property of the Activities gallery: 

 

SortByColumns(Filter(Activities, Regarding = [@ModelDrivenFormIntegration].Item ,  'Activity Status' = 'Activity Status (Activities)'.Completed),"modifiedon")

 

You shouldn't get any delegation warnings. Let me know if this doesn't work. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

 

View solution in original post

skoopman1988
Level: Powered On

Re: Filter and sorting modelformintegration in a gallery

Hi @PowerAddict,

 

Thanks a lot! I can't tell you how happy I am, but believe me, I am 😉.

 

I see I need to learn when to use a constant in a filter, but also neet to understand the datatypes I'm working with. 

 

Again, thanks a lot!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 160 members 5,386 guests
Please welcome our newest community members: