Power Apps - Microsoft Dataverse

Answered

CDS

(0) Share

Report

Posted on by Marco Pezzutti

We are currently moving data from Microsoft Azure SQL to CDS

I managed to import the tables but I have a few questions that I hope you can help me with

Q1. In SQL I have the field ID which is an auto number and my primary key . In CDS this is not possible because it saying it has to be a text field. What can I do in this regard?

Q2. Below is a piece of my code where I am building a Collection collAllValidQuesA from the '[frm].[_0QuestionBank]' table. When I run this code in SQL the Collection collAllValidQuesA creates the columns exactly like the table

However when I import the '[frm].[_0QuestionBank]' table into CDS and make this new entity and I run the below code it goes and creates all these extra columns in my collection that I do not need and this is the problem

Is there a way to limit the number of columns in my collection so that I do not have all these extra ones? Could you please show me what I have to do in the coding so that this is avoided?

ClearCollect(

collAllValidQuesA,

SortByColumns(

Filter(

'[frm].[_0QuestionBank]',

ID >= 1 And ID <= 1899

"QuestionOrder"

)

Categories:

Entities

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

v-xida-msft on at

Like (0)

Report
Re: CDS

Hi @mpezzutti ,

For your first question, actually, within CDS Entity, we use Unique Identifier type column to act as our Primary Key. The Unique Identifier column is a GUID value, e.g. '79a7f5cd-fa5d-ea11-a811-000d3a80cf8d'. And the Unique Identifier type column normally has same name with the Entity.

Of course, you could also consider add a Whole Number type column (called "ID") in your Entity before loading data from your SQL Table, and then within the "Field Mapping" section, match the Id from your SQL Table to the Whole Number type column in your CDS Entity as below:

After finishing loading data, you could also use the "Whole Number" type column as Primary Key in your CDS Entity.

For your second question, I think the ShowColumns function could achieve your needs. Please modify your formula as below:

ClearCollect( collAllValidQuesA, SortByColumns( ShowColumns( Filter( 'CDS Entity', ID >= 1 And ID <= 1899 ), "Column1", "Column2", "Column3", ... ... ), "QuestionOrder" ) )

Note: The ShowColumns function includes columns of a table and drops all other columns. Please make sure you have set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

Best regards,

Was this reply helpful? Yes No
David Jennaway 716 on at

Like (0)

Report

Re: CDS

Re the first question, you can create an auto-number field in CDS, and define it as an additional key on the entity. However, this will need to be a text value, based on a pattern that you decide. The easiest option for migrating existing values would be to convert then to strings, with appropriate padding and/or formatting as part of the migration.

It really isn't viable to use integer fields as IDs in CDS. Another issue is that CDS applies the same numbering format to all integer fields across an environment

Was this reply helpful? Yes No

Join the conversation

Community site session details

CDS

Helpful resources

News and Announcements

Quick Links

Coming soon: forum hierarchy changes

Chiara Carbone – Community Spotlight

Congratulations to the October Top 10 Community Leaders!

Leaderboard > Power Apps

Featured topics

Product updates