Unanswered

How to get unique numerical ID in Dataverse table?

(0) Share

Report

Posted on by 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!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (1)

gulshankhurana 1,397 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
PimJ 65 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
ivan_apps 2,187 Moderator on at

Like (0)

Report

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().

Was this reply helpful? Yes No
PimJ 65 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Verified answer

ivan_apps 2,187 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
PimJ 65 on at

Like (0)

Report

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

Was this reply helpful? Yes No