UPDATE 2/19/17: I had written a new solution for this method here. It works more efficiently for CDS.
____________________
A few days ago I shared the solution I have been using to pull in all records of an Entity into a temporary collection to overcome the 500 record limitation. You can read about it here.
Now that I've had some time to play with ForAll, I have a more elegant solution for pulling in 500 records at a time for reading. I still do not have a good solution for writing though. For this to work, you will still need a column in your Entity which describes which set of 500 it belongs to.
Big idea:
- Find out what the maximum n value is that describes how many sets of 500 you have. The formula I included also repairs the datasource if the last entry did not correctly have an n recorded.
- Create a dummy collection that includes whole numbers that are less than or equal to the n value you found in step 1.
- Use the dummy collection in step 2 as an argument in ForAll--"For each n in the dummy table, collect the 500 records from the datasource which are equal to that n."
UpdateIf(datasource,IsBlank(n),
{n: RoundDown(Value(PrimaryId)/500,0)+1
}
);
UpdateContext({maxn: First(Sort(datasource,PrimaryId,Descending))});
ClearCollect(iter,
Distinct(Filter(HundredChart,Num<=maxn.n),Num)
);
Clear(datasource_temp);
ForAll(iter,
Collect(datasource_temp,
Filter(datasource,n=Result)
)
)This can be done in a Button, Toggle, Timer, or whatever you want to trigger.
The only requirement is that you create a Table of whole numbers in a column [1,2,3,4,5, etc.] from which to pull your dummy collection. You can connect it to PowerApps as static data. I just used an existing "Hundred Chart" from a datasource I already connected. I do not know another way of making a collection with such whole number sets.
EDIT: Unfortunately, you will need to create an n value in your entity. I tried the following formula to try working around writng a column for n, but it has service limitations:
ForAll(iter,
Collect(datasource_temp,
Filter(datasource,(RoundDown(Value(PrimaryId)/500,0)+1)=Result)
)
)
EDIT2:
Since UpdateIf does not delegate, you will not be able to fix all n that are blank. Instead, when writing a record, write n as 0 instead of blank so you can fix it. The change below can only fix the last record and may miss any others that do not have an n.
UpdateContext({maxn: First(Sort(datasource,PrimaryId,Descending))});
If(IsBlank(maxn.n) || maxn.n=0,
Patch(datasource,First(Filter(PrimaryId=maxn.PrimaryId)),
{n: RoundDown(Value(maxn.PrimaryId)/500,0)+1
}
)
UpdateContext({maxn: First(Sort(datasource,PrimaryId,Descending))})
);
ClearCollect(iter,
Distinct(Filter(HundredChart,Num<=maxn.n),Num)
);
Clear(datasource_temp);
ForAll(iter,
Collect(datasource_temp,
Filter(datasource,n=Result)
)
)
@Meneghino, @hpkeong, @AmitLoh-Powerap: I think you would be interested in this.
