Unanswered

Alternate Key w/ Null values

(0) Share

Report

Posted on by KyleRichardson1

Hi,

For a model-driven app, I have an alternate key on a Dataverse entity comprised of 3 lookup columns, one of which is left null a majority of time. I still need to enforce uniqueness, which I've come to understand isn't supported for alternate keys.

My question is- has anyone out there found a workaround?

My only idea so far has been to default create a "Null" placeholder value in the problematic field. This is not ideal for my current environment but will use as a last resort.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

AhmedSalih 6,680 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hello, @KyleRichardson1, Alternate keys must be unique. If you have your Alternate Key from three Columns: A, B & C, you can’t have two exact combinations.

Was this reply helpful? Yes No
KyleRichardson1 20 on at

Like (0)

Report
Copy link

Link copied!

@AhmedSalih Thank you for the response.

Unfortunately, if there's a null value in any of the alternate key fields, it will not enforce uniqueness.

For instance, if columns A, B, and C below are my key fields, it will allow the duplicate entry with null values in C, even though it should be recognized as a duplicate with the exact combination.

Column A Column B Column C
Record 1 Lookup Value 123 Lookup Value 456 null
Record 2 Lookup Value 123 Lookup Value 456 null

Was this reply helpful? Yes No
AhmedSalih 6,680 Moderator on at

Like (0)

Report
Copy link

Link copied!

@KyleRichardson1, I see, the null value is not a value that can be tested against the Alternate Key. In this case your last resort is the only solution.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

An alternative approach is to create a string column on the table which contains a concatenation of all the other keys, including the nullable key, adding this to the keys, and making sure it is alway populated.
So you could have end with a concatenated "key" of "00000000-0000-0000-0000-00000000000011111111-1111-1111-1111-11111111111122222222-2222-2222-2222-222222222222" when the nullable column isn't null, and a key of 33333333-3333-3333-3333-33333333333344444444-4444-4444-444444444444 when it is null.
Hope this makes sense.

Was this reply helpful? Yes No
Guido Preite 1,490 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

don't make things complicated. As you already imagined, just create a lookup value called "NULL" and set the lookup to be required. (you can also set it as default value with some javascript so users don't need to select it manually if often the value should be this null)

EDIT: sorry, didn't notice the thread was old

Was this reply helpful? Yes No