OOB email table gives duplicate key error even when its not a duplicate

(0) Share

Report

Posted on by Billy

396

Hi there,

So I found a really weird issue here, I have a powerautomate flow that adds an Outlook email to my dataverse database in the email message table.

For every email message I set the message ID to be the ID of the Outlook email, now I noticed that I got a duplicate key error.

Which in my opinion is weird since every Outlook email is unique.

So I started to investigate what the issue might be, and this is what I found:

Let's say email 1 has the ID: Something

Email 2 has the ID: something

As you can see both IDs are the same yet different, so in a way you would think that this is not a duplicate key, yet you will get the error.

The reason is that apparently the dataverse email message IDs are not case-sensitive, causing duplication errors.

I did notice that the email table has a Message ID dup check, but I am unable to find any information about it so I have no clue what it does.

Is there a way to enable case sensitivity for it, or atleast a way to ensure its unique?

Any ideas how to solve this?

Thanks in advance!

Kind regards,

Billy

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Billy_C ,

The Outlook message id should be globally unique with a GUID, is that not what you are seeing?

Was this reply helpful? Yes No
Billy 396 on at

Like (0)

Report
Copy link

Link copied!

Hi @dpoggemann,

Yes, the Outlook message-id is unique, but it's an id that is case-sensitive so it's not really a GUID as they don't have capital letters in them.
To give an example of such a message-id:
AAQkADZlOTc1YjRmLWJm...
As you can see both small and capital letters are used, which is something the dataverse email table cannot handle.
Apparently, the message-id column has a secondary field called message-id dup check which is a unique identifier.
Since a unique identifier is built mainly for GUID it doesn't work for this kind of id.

This is why I am wondering if it is possible to disable either the duplication check or either to enable case sensitivity for that field or whether there is another way to keep the message-id.

Any ideas?

Thanks in advance!

Kind regards,

Billy

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Billy_C ,

What about something like the following:

Add another field called Outlook Message Id and place the value there

Create an autonumber field that maybe starts at 1 and is just numeric

Create a calculated field that concatenates 1 & 2

Create a business rule that sets the message_id field to the calculated field in #3

Was this reply helpful? Yes No
Billy 396 on at

Like (1)

Report
Copy link

Link copied!

Hi @dpoggemann,

That's actually a smart way of solving this issue!
I kinda have something similar to this actually but without extra fields instead this is what I did/found out:

I did some research and I noticed that the length of the outlook message id is always 152 characters (unless its a coincidence).
So what I did was I edited my flow and instead of passing in the outlook message id I changed the value to be a combination of the outlook message ID and the email received on time stamp.
Since the chance of having 2 IDs that are the same except for 1 character on the same received on time stamp is small.
Now whenever I want to use the message ID, I just have to retrieve the first 152 characters with no issues at all.
(Unless the amount of characters would change, of course).

As far as I know I haven't run into any issues yet, so I am hoping it solved the issue, but I'm not entirely sure.
I really hope there is going to be a better solution for this at some point.

Nonetheless, thanks for the advice!

Kind regards,

Billy

Was this reply helpful? Yes No