How to get unique numerical ID in Dataverse table?

[PimJ]

Hello,

I'm trying to load a large Dataverse table (>2000 records) into a collection, but therefor I need to have a unique numerical column (RecordID) to make the trick possible (see With() Statement managing Delegation – Practical Power Apps). 

This unique numerical RecordID should be created during patch of multiple records from myCollection, with this code:

Patch(MyDVtable,ShowColumns(myCollection,"Name"));

Ideally, I would like to copy the unique ID of the created Dataverse records into the column RecordID, probably with an extra Patch-command?

However, I have no idea how to do this, so please help me to get this done.

Thanks a lot!

[gulshankhurana]

Hi @PimJ 

You could use the autonumber column for this.

This video may help:

https://youtu.be/pwF-NXs2tHI?si=NW4PjH35WeWplJLd

And here's the link to Microsoft docs if you prefer reading:

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/autonumber-fields

I hope this helps.

Kind regards

Gulshan

[PimJ]

Yes thanks, I am aware of this autonumbering feature!

However, there is one problem as this autonumbering field is not a real numerical value, but a string value - you can see that in the screenshot below (Abc instead of 123):

And because of this, these value cannot be handled as real numbers..

So, I'm still looking for an alternative way to auto-fill a column with real numerical values.

Any help is welcome!

[ivan_apps]

Can you share what the Delegation warning is that you’re trying to avoid? You may be going about this the wrong way, particularly because the article you shared is focused on SharePoint delegation and makes note that it’s because it’s not a relational database that you have to make certain workarounds. 

Dataverse is a relational database and has a broader range of delegable filters and operators than SharePoint. Perhaps there is a way to do what you need to do in a fully supported Dataverse delegable filter rather than loading a variable using With().

[PimJ]

Ok, let me explain more in detail what I'm trying to do...
I'm developing a voting-App, with a large table "DV_Votes" in Dataverse, containing all the names that have been voted. And I have a collection "col_Candidates", which contains a column with the candidate-names (used for voting) and an empty column "Votes" that should get the number of votes for each candidate. See picture below:

I tried to get the number of votes per candidate from the DV-table with the CountIf() function, but the problem is that my DV-table contains ~5000 records, which is much more than the delegation limit of 2000. And the CountIf() function is non-delegable, so I think this will not work.

As an alternative I tried to copy the whole large DV-table into a new collection, so that I can run the CountIf() function on that collection, without having the delegation problem. But in order to do this copy, I need to deal with the same delegation issue and therefor I thought the numbered column would help in combination with the suggested With() solution.

So, basically my 2 questions are these:

1) What would be the best way to count the votes in the large DV-table? Can I do this directly in the table itself, or is it required to copy the table first into a collection?

2) If I really need to copy the large DV-table into a collection, what would be the best way to do?

I hope this helps to understand my case and looking forward for good suggestions!

[ivan_apps · Accepted solution]

Ok to answer your questions:

1) There isn't a best way but different approaches depending on what you're comfortable with. If you end goal is to tally up votes and have periodic updates and track it "live" - use Power BI. You'll be able to create great visuals and have a "scoreboard" type effect and not run into delegation. If you want to keep it within the canvas app - my recommendation is to offload the counts onto the server via Power Automate. FetchXml can aggregate or "Group By" for your, or you can create your own OData query. Take a look at XrmToolbox's FetchXML Builder if you want a visual UI to create the query.

-Another trick you can try is a roll-up column. You can create a parent table "elections" and associate all votes to that table. You can create a roll-up column for each candidate and then configure it to only summarize when their name is in a column. Note that there is a roll-up column limit per environment and that they only automatically update once a day - you can manually update it as many times as you want though. - i would only really try this method if you don't like the 2 above.

2) I would worry about loading too much data locally, but batch loading is probably what you'd want. In your scenario, if you know all the candidates already - can you filter by candidate name startswith (John) or something similar? Startswith is delegable and should return all votes, and then load it into a generic collection and keep collecting more candidates. This seems more of a pain to manage than simply triggering an instant flow by clicking a button and having it return all the data to you - or return just summary data.

[PimJ]

Thanks for helping me, I found a nice solution!

There was no need to count the votes real-time, so I found a solution to copy the whole DV-table to a collection - in a delegable way - and do all the grouping and counting from there. This is good possible because my large DV-table contains just one numerical column, so the amount of data (used memory) is limited.

Regards, Pim