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

Common Data Model entity with Lookup Field

I have two entities, Site and Phase. Both have a "Number Sequence" field with a prefix that is configured as the "Title" field. The Phase entity has a Lookup field called ParentSite that is linked to the Site entity.

 

In my PowerApp I have a connection to my CDM and the two entities detailed above. I have a Gallery control that has it's Items property set to  the "Site" entity and another Gallery control with the following formula on Items Filter(Phase, SiteGallery.Selected.SiteId = ParentSite.SiteId)

 

The = operator and the ParentSite.SiteId are underlined in blue with the following messsage.

 

Suggestion: Part of this filter formula cannot be evaluated due to service limitations. The local evaluation may produce suboptimal or partial results. If possible, please simplify the formula. For more information, please see the Filter function documentation.

 

What would be the better suggested way of doing this?

1 ACCEPTED SOLUTION

Accepted Solutions
mr-dang
Level 10

Re: Common Data Model entity with Lookup Field

@rgruian is correct. If you bring down Filter to one condition, it will compute.

 

However, it will still have scrolling dots at the top for every instance of those filters--which can tax you to 10GB of RAM usage as I have encountered when migrating. 

 

I have a crazy work around, but it gets the job done. Some of it is detailed here, but it is outdated since I have reworked some of its code.

 

Here's the gist of it:

  1. Knowing that PA can only return 500 records at a time from a Filter, create a column in your entity that identifies which block of 500 each record belongs to (1st 500, 2nd 500, 3rd 500, etc.). Set the type to Number.

  2. Use a repeating Timer to Collect the nth block of 500 records each time the timer ends. From here you will operate on a temporary collection rather than the original datasource--this could be a deal breaker in many situations.
  3. When writing back to the datasource, you will need to write to the temporary collection as well.

 

 

This code is based on @hpkeong's repeating timer idea.

How to pull in temporary data:

Screen.OnVisible: 

UpdateContext({getdata: true, iter: 0})

 

Timer.OnTimerEnd:
If(getdata, If(!IsEmpty(Filter(MyDataSource,n=iter+1)), UpdateContext({iter: iter+1}); Collect(MyTemporaryCollection, Filter(MyDataSource, n=Value(iter))), UpdateContext({getdata: false}) )

 

 

How to write new data:

Button.OnSelect:

UpdateContext({temp:
Patch(MyDataSource,Defaults(MyDataSource),
{[your columns here, except n]})
});

Collect(MyDataSource_1,
Patch(MyDataSource,temp,
{n: RoundDown(Value(Right(temp.Title,10))/500,0)+1})
)

How to update existing data

Button.OnSelect:

UpdateContext({temp:
Patch(MyDataSource,First(Filter(MyDataSource,[conditions to recall existing record])),
{[your columns here, except n]})
});

UpdateIf(MyDataSource_1, Title=temp.Title,
Patch(MyDataSource,temp,
{n: RoundDown(Value(Right(temp.Title,10))/500,0)+1})

 

 

Here I use MyDataSource_1 just to remind myself that it's my temporary collection of the original. Once CDM can handle more conditions in its filter or once it can handle more than 500 records, then reverting is just a matter of deleting "_1." Switching is just a matter of adding "_1," so it's not too bad.

 

You will notice that Patching to the datasource happens twice every time. This is because you need to figure out the Title value before you can calculate which block of 500 it belongs to (n).

 

The only thing I haven't done is how to Remove a record since I have not needed it. 

 

So how long does loading take? 20-30s for 5 databases. I have a loading screen and I use buttons with rounded corners as a loading bar. This does not include the time it takes to open up PA, login, and wait for the splash screen to go away. As long as the app works, it works.

 

Edit:

Here's some more. Since you are working with temporary collections, you don't get the most up-to-date live data. You will need to build in Refresh. However, Refreshing changes to your temporary collection requires code, which you don't want to repeat everywhere since you would need to re-edit every spot. It is better to have the refresh code in one spot, then trigger it when needed.

 

My solution to this is to make the Timer mentioned above be able to start remotely. A Toggle on one screen can trigger things on another screen even if the Screen is not Active or Visible. 

  1. The Button makes Toggle1 true, then false (both are on the same screen).
  2. Toggle1 is detected by Toggle2, which activates the Timer to recollect.
Buton2.OnSelect: 
UpdateContext({resetdata: !resetdata});
UpdateContext({resetdata: !resetdata})

Toggle1.Reset: resetdata
Toggle1.Default: resetdata

Toggle2.Reset: Toggle1
Toggle2.Default: Toggle1
Toggle2.OnCheck:
Refresh(MyDataSource);
Clear(MyDataSource_1);
UpdateContext({getdata: true, iter: 0}) Timer.AutoStart: getdata Timer.Repeat: getdata Timer.Start: getdata

 

 

I am sharing this solution because I am dissatisfied with it. I am hoping someone could help shorten it or share a better one Please! Smiley Happy

Microsoft Employee
@8bitclassroom
4 REPLIES 4
PowerApps Staff rgruian
PowerApps Staff

Re: Common Data Model entity with Lookup Field

What that info message is saying is that your query cannot be evaluated on the server (where the CDM data lives) because it's making use of possibly-changing local app context (SiteGallery.Selected) at each evaluated row. It is also saying that due to this situation, the formula will be evaluated locally instead, which is nothing to worry about if your data is small. Large data, however, is capped at 500 rows, so a Filter operation will operate on that reduced set, and that is something to keep in mind.

 

If your app needs to operate on data that is >500 rows, you will need to rewrite the formula to make query delegation to the back end possible. That generally means simplifying the predicate in a Filter operation so that it does not draw values from entities that are subject to continuous dataflow -- controls, dynamic data sources such as Location or Acceleration, etc.

Radu Gruian [MSFT] ** PowerApps Staff
mr-dang
Level 10

Re: Common Data Model entity with Lookup Field

@rgruian is correct. If you bring down Filter to one condition, it will compute.

 

However, it will still have scrolling dots at the top for every instance of those filters--which can tax you to 10GB of RAM usage as I have encountered when migrating. 

 

I have a crazy work around, but it gets the job done. Some of it is detailed here, but it is outdated since I have reworked some of its code.

 

Here's the gist of it:

  1. Knowing that PA can only return 500 records at a time from a Filter, create a column in your entity that identifies which block of 500 each record belongs to (1st 500, 2nd 500, 3rd 500, etc.). Set the type to Number.

  2. Use a repeating Timer to Collect the nth block of 500 records each time the timer ends. From here you will operate on a temporary collection rather than the original datasource--this could be a deal breaker in many situations.
  3. When writing back to the datasource, you will need to write to the temporary collection as well.

 

 

This code is based on @hpkeong's repeating timer idea.

How to pull in temporary data:

Screen.OnVisible: 

UpdateContext({getdata: true, iter: 0})

 

Timer.OnTimerEnd:
If(getdata, If(!IsEmpty(Filter(MyDataSource,n=iter+1)), UpdateContext({iter: iter+1}); Collect(MyTemporaryCollection, Filter(MyDataSource, n=Value(iter))), UpdateContext({getdata: false}) )

 

 

How to write new data:

Button.OnSelect:

UpdateContext({temp:
Patch(MyDataSource,Defaults(MyDataSource),
{[your columns here, except n]})
});

Collect(MyDataSource_1,
Patch(MyDataSource,temp,
{n: RoundDown(Value(Right(temp.Title,10))/500,0)+1})
)

How to update existing data

Button.OnSelect:

UpdateContext({temp:
Patch(MyDataSource,First(Filter(MyDataSource,[conditions to recall existing record])),
{[your columns here, except n]})
});

UpdateIf(MyDataSource_1, Title=temp.Title,
Patch(MyDataSource,temp,
{n: RoundDown(Value(Right(temp.Title,10))/500,0)+1})

 

 

Here I use MyDataSource_1 just to remind myself that it's my temporary collection of the original. Once CDM can handle more conditions in its filter or once it can handle more than 500 records, then reverting is just a matter of deleting "_1." Switching is just a matter of adding "_1," so it's not too bad.

 

You will notice that Patching to the datasource happens twice every time. This is because you need to figure out the Title value before you can calculate which block of 500 it belongs to (n).

 

The only thing I haven't done is how to Remove a record since I have not needed it. 

 

So how long does loading take? 20-30s for 5 databases. I have a loading screen and I use buttons with rounded corners as a loading bar. This does not include the time it takes to open up PA, login, and wait for the splash screen to go away. As long as the app works, it works.

 

Edit:

Here's some more. Since you are working with temporary collections, you don't get the most up-to-date live data. You will need to build in Refresh. However, Refreshing changes to your temporary collection requires code, which you don't want to repeat everywhere since you would need to re-edit every spot. It is better to have the refresh code in one spot, then trigger it when needed.

 

My solution to this is to make the Timer mentioned above be able to start remotely. A Toggle on one screen can trigger things on another screen even if the Screen is not Active or Visible. 

  1. The Button makes Toggle1 true, then false (both are on the same screen).
  2. Toggle1 is detected by Toggle2, which activates the Timer to recollect.
Buton2.OnSelect: 
UpdateContext({resetdata: !resetdata});
UpdateContext({resetdata: !resetdata})

Toggle1.Reset: resetdata
Toggle1.Default: resetdata

Toggle2.Reset: Toggle1
Toggle2.Default: Toggle1
Toggle2.OnCheck:
Refresh(MyDataSource);
Clear(MyDataSource_1);
UpdateContext({getdata: true, iter: 0}) Timer.AutoStart: getdata Timer.Repeat: getdata Timer.Start: getdata

 

 

I am sharing this solution because I am dissatisfied with it. I am hoping someone could help shorten it or share a better one Please! Smiley Happy

Microsoft Employee
@8bitclassroom
Super User
Super User

Re: Common Data Model entity with Lookup Field

Hi Dang:

 

Congratulations for getting over the hurdle of 500 limitation, at least before PG remove the condition.

I will keep it for my ownself to achive the same thing.

 

Thanks a lot.

hpkeong
tobystatham
Level: Powered On

Re: Common Data Model entity with Lookup Field

Thanks. Don't think we'll ever hot that limitation, but it's good to know there is a workaround.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 54 members 4,371 guests
Please welcome our newest community members: