Unanswered

Power Platform - Dataverse

(0) Share

Report

Posted on by JH_IC

Hi,

there seems to be conflicting information in documentation and community posts regarding Dataverse handling of unique keys at the database level.

I have created a table

- with a primary name column = Name

- with a key on Column = Code

I have set up a duplicate detection rule on Code column.

Using Powerapps or Dataverse form to enter data I can enter multiple records with the same code and also the same name

Code=FONS; Name=AAA

First record saves as expected. On saving the second (and beyond) i get a message that a duplicate has been detected with an option to ignore. If I ignore I can store duplicate records in Dataverse.

Is this expected behaviour that I need to code around in PowerApps (and also APIs?).

thanks,

Jon

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

Duplicate Detection is designed to detect, not prevent the entry of duplicates. But when you create a Dataverse table it will create a uniqueidentifier column (GUID) that will always uniquely identify that record. You can also create a KEY column that will be unique for each record. That column can have a variety of formats. This is known as an alternate key.

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report

Hi @JH_IC,

@Pstork1 is correct in that a duplicate detection rule will prompt you of other potential duplicates but still give you the flexibility to save the duplicate as there may be valid reasons for having the duplicate.

If you set up a (Alternate) Key on the column or combination of columns that should be unique, it will prevent the user from saving the duplicate and present an error back to the user. The error message will just tell you that a duplicate value exists. This is enforced at the Database level and so will be consistent across all user experiences and any integrations trying to write data into Dataverse.

If you want to prevent the duplicate being saved, I'd recommend using both a duplicate detection rule and a key for the best user experience, since the dup detection rule will prompt you with the record that has been identified and let's you easily navigate to it..

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
JH_IC 9 on at

Like (0)

Report

Thanks for responses to date but I am not seeing unique constraint being enforced at database level.
I am attaching image of table columns, key and data (saved after ignoring the duplicate detection warning).

data.PNG

table.PNG

keys.PNG

Was this reply helpful? Yes No
JH_IC 9 on at

Like (0)

Report

Hi @ChrisPiasecki I have a key but it is not being enforced. See my uploaded images. Am I missing something?
Thanks

Was this reply helpful? Yes No
Verified answer

ChrisPiasecki 6,422 Most Valuable Professional on at

Like (1)

Report

Hi @JH_IC,

You'll have to clean up your duplicates before you can use an alternate key, as the database indexing job behind the scenes will fail due to to the duplicates.

If you Switch to Classic interface, browse to your table and the keys and you will see an additional Status column for the key.

When you first create the key it will be in a Pending status with a system job that creates the database index. This can take some time to complete depending on the number of records in your table. You can click on the job to see the status of that job, if and why it failed.

Once it is completed successfully then the status should change to Active as shown below. The system job gets cleaned up automatically.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
JH_IC 9 on at

Like (0)

Report

Hi @ChrisPiasecki , thanks for that. Yes that is the issue. Tip for anyone else - you can switch to classic view via Solution Explorer.
Thanks,
Jon

Was this reply helpful? Yes No