cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

CDS

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"

            )

        )

                   

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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.

10.JPG

 

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:

11.JPG

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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.

10.JPG

 

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:

11.JPG

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Users online (6,626)