cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sheppegr
Frequent Visitor

Large Table in Dataverse, Filtering down to a collection

First a thank you out to @WarrenBelz  I appreciated your blog and code on this subject. (Manageing Delegation with the with() Statment )

I have done what most people do, is I have tried to form the collection off the data, instead of planning the data for the collection.

I have a collection of over 100k records and grow daily, and only want the last 15k records for the collection (last day). 

Here is the code I modified from your blog:

 

Clear(colAllList);
With(
   {wSets: 
      With(
         {wLimits: 
            With(
               {wLimit: 
                  Sort(
                      '2-Finviz_Stockss',
                     Value(FinViz_Primary_Key),
                     Descending
                  )
               },
               RoundDown(
                  First(wLimit).FinViz_Primary_Key / 2000,
                  0
               ) + 1
            )
         },
         AddColumns(
            RenameColumns(
               Sequence(
                  wLimits,
                  0,
                  2000
               ),
               "Value",
               "LowID"
            ),
            "HighID",
            LowID + 2000
         )
      )
   },
ForAll(
      wSets As MaxMin,
      Collect(
         colAllList,
         Filter(
             '2-Finviz_Stockss', Value(FinViz_Primary_Key) > MaxMin.LowID && Value(FinViz_Primary_Key) <= MaxMin.HighID
      ),CountRows(colAllList)
      )
   )
);

ClearCollect(Todays_Filtered_Data, Filter(colAllList, crb36_import_date = Today())
            )

 

My primary key is an auto generated number, and is unique, unfortunately it is stored as Text, so i used the Value statement to get the value to use in the Max and Min.

I was first trying to get the collection of all records, to ensure i had working code, before filtered it down to just the last 15k.
the last line, is creating a collection of just records imported today.

here is where it is strange. I used the countrows to see how many rows i retrieved, it is (124)

and the last collection is blank.

I have verified in the table that there is 15k rows of data with today's import_date.
This is a hard subject to get my head around. if there is a better way to propagate the data, i can change the schema, this is still in development. 

As always, any ideas and suggestions are appreciated.

 

Guy

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @sheppegr ,

I am not a Dataverse user, but is your primary key numeric and sequential. Also is it Delegable for > or < (greater than or less than) as I know the SharePoint ID is not. Also I assume your Delegation limit is set to 2,000. 

The code you posted is correct in structure if all of the above is true.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

 

View solution in original post

5 REPLIES 5
WarrenBelz
Super User
Super User

Hi @sheppegr ,

A bit low-tech compared with the last one, but this process from my Delegation blog with 8 instances will get you 16k.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

sheppegr
Frequent Visitor

Ok i tried this, and maybe i am not doing it quite right.

ClearCollect(
   colAllList,
   Sort(
      '2-Finviz_Stockss',
      FinViz_Primary_Key,
      Descending
   )
);
If(
   CountRows(colAllList) = 2000,
   Set(
      vID,
      Min(
         colAllList,
         FinViz_Primary_Key
      )
   );
   Collect(
      colAllList,
      Sort(
         Filter(
            '2-Finviz_Stockss',
            Value(FinViz_Primary_Key) < vID
         ),
         FinViz_Primary_Key,
         Descending
      )
   )
);
If(
   CountRows(colAllList) = 4000,
   Set(
      vID,
      Min(
         colAllList,
         FinViz_Primary_Key
      )
   );
   Collect(
      colAllList,
      Sort(
         Filter(
            '2-Finviz_Stockss',
            Value(FinViz_Primary_Key) < vID
         ),
         FinViz_Primary_Key,
         Descending
      )
   )
);

If(
   CountRows(colAllList) = 6000,
   Set(
      vID,
      Min(
         colAllList,
         FinViz_Primary_Key
      )
   );
   Collect(
      colAllList,
      Sort(
         Filter(
            '2-Finviz_Stockss',
            Value(FinViz_Primary_Key) < vID
         ),
         FinViz_Primary_Key,
         Descending
      )
   )
);
If(
   CountRows(colAllList) = 8000,
   Set(
      vID,
      Min(
         colAllList,
         FinViz_Primary_Key
      )
   );
   Collect(
      colAllList,
      Sort(
         Filter(
            '2-Finviz_Stockss',
            Value(FinViz_Primary_Key) < vID
         ),
         FinViz_Primary_Key,
         Descending
      )
   )
);
If(
   CountRows(colAllList) = 10000,
   Set(
      vID,
      Min(
         colAllList,
         Value(FinViz_Primary_Key)
      )
   );
   Collect(
      colAllList,
      Sort(
         Filter(
            '2-Finviz_Stockss',
            Value(FinViz_Primary_Key) < vID
         ),
         FinViz_Primary_Key,
         Descending
      )
   )
);
If(
   CountRows(colAllList) = 12000,
   Set(
      vID,
      Min(
         colAllList,
         Value(FinViz_Primary_Key)
      )
   );
   Collect(
      colAllList,
      Sort(
         Filter(
            '2-Finviz_Stockss',
            Value(FinViz_Primary_Key) < vID
         ),
         FinViz_Primary_Key,
         Descending
      )
   ),CountRows(colAllList)
);

ClearCollect(Todays_Filtered_Data, Filter(colAllList, crb36_import_date = varDate))

'2-Finviz_Stockss', is a dataverse table with over 100k rows. I am using the dataverse connector.

sheppegr_0-1645506003501.png

and it has 48 columns, with the FinViz_Primary_Key is an auto numbered - formatted 2022-21-02-1000;

sheppegr_1-1645506487291.png

 

so i used the Value(Finviz_primary_key) to get the value versus the text. 

Not sure why this only shows 2000 on the count rows.

sheppegr_2-1645506578219.png

I think this is the right track, i just dont know where i am messing up on the data.

 

Thanks

Guy

 

 

Hi @sheppegr ,

I am not a Dataverse user, but is your primary key numeric and sequential. Also is it Delegable for > or < (greater than or less than) as I know the SharePoint ID is not. Also I assume your Delegation limit is set to 2,000. 

The code you posted is correct in structure if all of the above is true.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

 

sheppegr
Frequent Visitor

thanks, I have to figure out if it is Delegable, if not i need to figure out a way to create one 😛

thank you for looking over the script. i know i am close 😛

Once i figure this out, i will return the answer here, to pass it along.

-Guy

sheppegr
Frequent Visitor

Ok, what I have found with some research is that the Primary Key is not delegable, it is a Text format, even if it is just numbers. 

I went ahead and changed a column i had that was a number (id) stored as text, i removed all the data, reformatted that column to an integer, reimported al the data, and it worked as intended.

thank you @WarrenBelz 

 

-Guy

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,865)