cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HansHeintz
Helper V
Helper V

Caching > 2000 dataverse tables using autonumber field, but this is a text field so how???

Recently I asked here how to cache dataverse tables.

SQL tables often have integer ID fields so you can determine min value, max value, number of passes and the cache the table in chunks.

The advise I got was to use the autonumber field.

But the autonumber field is a text field so I cannot filter chunks using the < and > operators.

I tried creating a calculated numeric field from the text autonumberfield but the Value function is conveniently left out of the calculator field editor.

So how?

In example ID is an autonumberfield with seven characters: 0000001;0000002;etc

 

Value(ID) >= Value * 2000 && Value(ID) < (Value+1) * 2000 will not work either of course: delegation error

 

 

UpdateContext({
               lMaxProduktID
               :First(
                      FirstN(
                             Sort(
                                  Produkt
                                  ;ID
                                  ;Descending
                                  )
                             ;1
                             )
                       ).ID
                })
;;UpdateContext({
                 lMinProduktID
                 :First(
                        FirstN(
                               Sort(
                                    Produkt
                                    ;ID
                                    ;Ascending
                                    )
                                ;1
                                )
                        ).ID
                })
;;Set(NumberOfPasses; RoundUp((Value(lMaxProduktID) - Value(lMinProduktID)) / 2000;0))
;;Clear(CachedProdukt)
;;ForAll(
        Sequence(NumberOfPasses;0)
        ;Collect
                (
                    CachedProdukt;
                    Filter(
                        Produkt
                        ;ID >= Text(Value * 2000)  && ID < Text((Value+1) * 2000)
                        )
                )
        )

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi  @HansHeintz ,

 

You could use canvas+flow/

vxiaochenmsft_0-1627872151445.png

vxiaochenmsft_1-1627872164969.png

vxiaochenmsft_2-1627872186063.png

vxiaochenmsft_3-1627872216497.png

[{"crba2_test_id":"A"}]

 

// crba2_test_id is a autonumber field.

 

Add a button control and set its onselect property to:

ClearCollect(Col2,Test123.Run())

 

The result is as follows:

vxiaochenmsft_4-1627872304004.png

vxiaochenmsft_5-1627872316073.png

 

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

4 REPLIES 4
v-xiaochen-msft
Community Support
Community Support

Hi  @HansHeintz ,

 

You could use canvas+flow/

vxiaochenmsft_0-1627872151445.png

vxiaochenmsft_1-1627872164969.png

vxiaochenmsft_2-1627872186063.png

vxiaochenmsft_3-1627872216497.png

[{"crba2_test_id":"A"}]

 

// crba2_test_id is a autonumber field.

 

Add a button control and set its onselect property to:

ClearCollect(Col2,Test123.Run())

 

The result is as follows:

vxiaochenmsft_4-1627872304004.png

vxiaochenmsft_5-1627872316073.png

 

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

Thanks a lot! Your code produces a table/collection.

A few questions:

-it does not cache the table as such, it caches the fields you specify in the JSON schema code, correct?

-I do not see where the numeric field is of added value, if it did not exist a collection with >2000 records would still be made, correct? (my table does not have 2000 records yet so I cannot check)

-I can add fields to the code, which show as columns in de collection in powerapps. I just do not know how I should specify certain fields like an option set field. If I specify it as string the result is [object Object] whatever option it contains if it contains an option. Is there somewhere an explanation on syntax for this?

v-xiaochen-msft
Community Support
Community Support

Hi @HansHeintz ,

 

For your first question:

Yes, you are correct. The returned value is determined by json. 

For example:

If there are 2 columns in your json, it returns 2 columns to canvas collection. It will not return all columns in your dataverse table.

 

For your second question:

Yes. Flow does not have the delegation problem like canvas. 

 

For your third question:

I did a simple demo for you.

This is my optionset column in my table.

vxiaochenmsft_0-1627960588637.png

vxiaochenmsft_1-1627960602541.png

vxiaochenmsft_2-1627960620271.png

vxiaochenmsft_3-1627960688438.png

 

You could see the history of your flow  and get its column name you want.

vxiaochenmsft_4-1627960782319.png

I copied and pasted it into Notepad.

vxiaochenmsft_5-1627960843999.png

You could see the true name of my autonumber field .

And the optionset column is the same.

vxiaochenmsft_6-1627960921816.png

 

So if i want these 2 columns will be returned to canvas , please modify your json like this:

vxiaochenmsft_7-1627961035658.png

[{"crba2_test_id":"A","crba2_test_optionset@OData.Community.Display.V1.FormattedValue":"A"}]
 
The result is as follows:
vxiaochenmsft_8-1627961065259.png
vxiaochenmsft_9-1627961080159.png

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

 

 

 

 

 

 

 

 

HansHeintz
Helper V
Helper V

Thanks, it all works! Still would be curious where to find documentation on syntax (fe for other field types) for this. Had not used flows much yet.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,993)