HUGE BUG: SubmitForm creates duplicate Primary Keys after importing values to Dataverse via Excel

[tgh]

Created an applicant tracking system (ATS) tool in PowerApps. Candidates enter the Dataverse "Candidates" table in two ways: 
1) Via a form (SubmitForm function) in the Power App

2) Via the "Import data from Excel" option for the Dataverse table (this is temporary until I fully flesh out the Power Automate flow to do this)

All the imports came in and I made sure to increment the ID (see Figure 1 for the full info on the "Candidate ID" primary key) beyond the last value in the Dataverse table. However, now any time a candidate is added via the Form in the app, the newly added candidate added shares the same ID as the newly imported candidates via Excel. For example, the first Excel candidate I imported had a value of 1140 as show in in Figure 2 (the last candidate in the table had an ID of 1139). So now, every time a candidate is added via the Form in the App, it increments based on the last value it as the SubmitForm function added to the table  (i.e., the next candidate added was also given a primary key of 1140). This occurred for 12 candidates and I then had to delete them and re add them AS WELL AS all their related data (One Candidate : Many Interactions like Coffee Chats). 

It appears the SubmitForm function does not autoincrement properly or fails to do so with an #Autonumber "data type".

Alternatively, this could be an issue of me putting the Candidate ID in as a number and the import data from Excel function not properly converting it to a type string in the Dataverse table. Either way, I somehow had multiple primary keys (Figure 2) of what I assume were the same type as well.  

I also did this imports at ~3:00 AM ET to further guarantee that the IDs would be unique and that no one would upload candidates while I was concurrently doing so (this was indeed the case after looking at the created on time stamps). 

(Related but also an aside: Some of the candidates I mapped didn't take the keys I mapped (e.g., my 1145 in the Excel actually became 1159 and my 1193 became 1189). Imported values were raw and in a table with column names exactly matching the db as the subset shown in Figure 3.)

Figure 1

Figure 2

Figure 3

[tgh · Accepted solution]

I fixed this issue in the short term with this:

Text(Index(SortByColumns('Candidates',"candidateid",Descending),1).'Candidate ID'+1)

where I find the Max of the Dataverse Candidate ID column and then increment. (this avoiding the Max delegation warning approach is courtesy of @RezaDorrani's videos). Hopefully this fix updates in real-time and doesn't run into an issue when two users submit at the same time (maybe I'll multiply this number by the timestamp of submission...) but hopefully the Dataverse table just bounces the duplicate ID and OnFailure is triggered in the submission form. 

[v-jefferni]

Hi @tgh ,

Nice work! Please consider accept your reply as the solution, help other users find it.

Best regards,

[SultanWasif]

How to validate or throw error if there is duplicate primary key submitted like email or phone number ?, I added the field key as primary through keys in dataverse.

An unknown failure has occurred. Error ID # [ee842b8d-a181-4f7c-bd2b-8886914fa24e]