Common Data Model entity with Lookup Field

[tobystatham]

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?

[rgruian]

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.

[mr-dang · Accepted solution]

@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! 🙂

[hpkeong]

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.

[tobystatham]

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