cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Medoomi
Resolver I
Resolver I

Custom intermediary table (N:N relationship) in CDS - What is best practice?

I thought I had my ducks in a row when I created the three tables to form a N:N relationship within Common Data Services.

 

Table A and Table C both used Autonumbers for the Primary Column (given that when creating the N:N relationship, these needed to be auto-created as well as unique).

 

Table B (the intermediary table) also used autonumbers for the Primary Column (this also needed to be auto-created)

 

Am I doing it the best way?

 

The reason I ask is because I recently found out (to my horror) that the counter on the autonumbers had regressed after migrating the environment manually (I used the data import function in Dynamics), and the autonumbers were handing out duplicates of numbers previously found in the system.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User II
Super User II

Hi @Medoomi 

I don't use an autonumber.  It seems counterintuitive that it is a text type field and not an integer.  I simply create a whole number field and call it ID.  When I add a new record, I use First(Sort(tablename, ID, Descending)).ID+1 as the Default property of the ID field.  If I am using it on a single form that I use for entering new data as well as editing existing data, I set the default property of the textbox in the ID card as 

 

 

Coalesce(Parent.Default, First(Sort(table, ID, Descending)).ID+1)

 

 The way it is written, old numbers are never reused, delegation is not a problem since First(Sort( Descending,  will always find the last number in the ID field regardless of the number of records in the table. 

With regards to Coalesce(), when the form is in New mode, the Parent.Default is null so it puts in my formula and when the form is in Edit mode, the Parent.Default exists and is kept as the value for the Textinput box.

View solution in original post

5 REPLIES 5
Drrickryp
Super User II
Super User II

Hello again @Medoomi ,

I am old school with respect to database design and I manually create the Primary and Foreign keys.  A sample structure is shown in the screenshot.  In PowerApps, when a many to many relationship is created using the OOB method, the junction table is hidden and cannot be accessed or customized. 

In my experience, there are often fields that apply only to the junction table, for example like grade or class location or time of a particular class in the screenshot.  These fields do not belong in either the Students table or the Classes table and the Junction table is ideal for them.  I also like to use my own ID column for every table, make it a whole number and increment it by 1 whenever I add a new record to that table. I have done this consistently since I began using Access and have continued to do it in CDS even though there are shortcuts provided by PowerApps.  I have been using essentially the same database that I constructed in 2003 and have migrated it through from MS Access to Dataverse (CDS).  It has been robust and has not failed me for over 14,000 patients with 45,000 visits. The basic principles of database design have not changed.  Please see how to design the tables here:  https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-... and the follow up with applying the principles to PowerApps here: https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Imple...  _1.png

@Drrickryp 

Thank you so very much for the links & explanation.

I also like to create primary & foreign keys manually & like to keep the primary key as an incremented autonumber.

Doing this however within CDS, has meant using "autonumber" as the primary key... which brought me to my point of panic, when I found the CDS autonumber field had regressed, and was handing out values that already existed in that field in the database.

I can change the autonumber type to "date prefixed number," which I think would add a level of robustness for the future, but my main question is whether my initial choice of "autonumber" for the primary key was appropriate, and if so, whether the number regression I experienced is a bug in CDS.

Drrickryp
Super User II
Super User II

Hi @Medoomi 

I don't use an autonumber.  It seems counterintuitive that it is a text type field and not an integer.  I simply create a whole number field and call it ID.  When I add a new record, I use First(Sort(tablename, ID, Descending)).ID+1 as the Default property of the ID field.  If I am using it on a single form that I use for entering new data as well as editing existing data, I set the default property of the textbox in the ID card as 

 

 

Coalesce(Parent.Default, First(Sort(table, ID, Descending)).ID+1)

 

 The way it is written, old numbers are never reused, delegation is not a problem since First(Sort( Descending,  will always find the last number in the ID field regardless of the number of records in the table. 

With regards to Coalesce(), when the form is in New mode, the Parent.Default is null so it puts in my formula and when the form is in Edit mode, the Parent.Default exists and is kept as the value for the Textinput box.

View solution in original post

@Drrickryp 

Thank you so much again for your reply. I like your code & it's elegance & will consider using it in the future. It certainly looks robust as well as simple.

Given that the autonumber data type seems intended for the same purpose (to be used as a primary key), should I not report the number regression I experienced as a bug?

Couldn't hurt.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (39,568)