Unanswered

Issue importing systemuser lookup field values using Dataflow and Azure Active Directory ID

(1) Share

Report

Posted on by jmcdm1

Hi,

I am trying to import data into an employees table, that contains a lookup field to the systemusers table, using Dataflow.

The systemusers table has an alternate key automatically configured using the Azure AD ID field.

In my Dataflow, I am merging a raw employees table (without any lookups) with the systemusers table, using the employee email, in order to obtain the Azure AD ID per employee. I then map this Azure AD ID to the lookup field in the target employees table.

When I run the Dataflow, I receive the following in an excel refresh report:

https://XXX-cat.crm11.dynamics.com/api/data/v9.1/cr0fa_a_employeetouserlookuptests(cr0fa_employeenumber='44140289'){
"cr0fa_email": "XXX@XXX.co.uk",
"cr0fa_employeename": "XXX",
"cr0fa_employeenumber": "44140289",
"cr0fa_Userlookup@odata.bind": "/systemusers(azureactivedirectoryobjectid='74f02344-fa73-452d-8853-a4233d71c14e')"
}

{"error":{"code":"0x0","message":"Bad Request - Error in query syntax."}}

I have tried using a different alternate key such as email address but this still produced a similar error.

I was wondering if anyone has experienced issues with Dataflow imports into user lookup fields and whether there are any solutions?

I have configured lookups to non users table fields successfully using Dataflow so it feels like it's the Azure AD ID that is causing issues.

Thanks in advance for your help.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (13)

Answers (0)

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Hi @jmcdm1,

The Azure Application ID is meant for a service principal in AAD. You should be using the username column/field instead which should map to the AAD login (e.g. first.lastname@company.com)

Hope this helps!

Was this reply helpful? Yes No
jmcdm1 8 on at

Like (0)

Report

Thanks @EricRegnier for responding so quick!

I tried using the username field (domainname) in the mapping section of the dataflow but I received a similar error as before:

https://XXX-cat.crm11.dynamics.com/api/data/v9.1/cr0fa_a_employeetouserlookuptests(cr0fa_employeenumber='44140289'){
"cr0fa_email": "XXX@XXX.co.uk",
"cr0fa_employeename": "XXX",
"cr0fa_employeenumber": "44140289",
"cr0fa_Userlookup@odata.bind": "/systemusers(azureactivedirectoryobjectid='XXX@XXX.co.uk')"
}

{"error":{"code":"0x0","message":"Bad Request - Error in query syntax."}}

I then tried to create an additional key using the username field (I can't delete the Azure AD ID key because it is configured automatically by Dataverse); however, I received the error in UserKeyError.JPG which states:

The table could not be updated: Index size exceeded the size limit of 900 bytes. The key is too large. Try removing some columns or making the strings in string columns shorter..

I then decided to use the internalemailaddress field as an alternate key in the systemusers table and configured the mapping in the Dataflow as in ColumnMapping.JPG but I received the following error:

Failed to validate keys for cr0fa_Userlookup column targeted entity systemuser. systemuser table's key cr0fa_username could not be created. Please delete the existing key and create new key for the table and try again. Additional information: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.SystemUserBase' and the index name 'ndx_for_entitykey_cr0fa_UserName'. The duplicate key value is <redacted>.

Should I be trying a different approach?

Thanks again for your help in this.

UserKeyError.JPG

ColumnMapping.JPG

Was this reply helpful? Yes No
Fubar 8,350 Super User 2025 Season 2 on at

Like (0)

Report

What are you trying to update in the Users table? you can't insert into it, and some fields like First Name, Last Name and Email address get updated via Azure AD (e.g. if you change someones First Name, it can be overwritten from AD)

Your initial description sounded like you had a custom table Employee and it had a Lookup to System User and you were trying to populate that Lookup (no update required on the User table)

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

You can insert into systemuser table (e.g. with CSV import)

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

You can import with a Excel/CSV import (I haven't personally tried yet with Dataflow). Can you tried with CSV import just to ensure your mapping is correct? Question: once your users are imported, why do u need to update them? Shouldn't it only be a one time thing?
More info on CSV import: https://docs.microsoft.com/powerapps/developer/data-platform/import-data

Was this reply helpful? Yes No
jmcdm1 8 on at

Like (0)

Report

Thanks both for your response.

I’m not trying to import data into system users, only to try to change the alternate key in an attempt to resolve the issue I am facing.

My issue is that I have a custom table employee with a lookup field to the system users table. I need to use a dataflow to populate this lookup field (for automation purposes) but the Azure Active Directory ID Object field does not seem to work in the mapping section of the dataflow.

Thanks

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Got it, thanks for clarifying! You get much better details when creating alternate keys in the classic solution explorer which I tried. It turns out, you can't create an alt key on username nor on internalemailaddress or Azure Object ID (I believe they already exists behind the scenes out-of-the-box), but you can create on employeeid or custom columns. Note also that you can't map Owner type of columns with Dataflows even when alt keys are defined on the User table. You'll need to create a new custom Lookup pointing to User (systemuser) table and then your Dataflow will auto detect it. Here's an example:

If you really want to then set the Owner, have a Power Automate flow that triggers when the custom User lookup is updated to set the Owner column. If you absolutely need that in sync/real-time, you can use a classic workflow.

Hope this helps!

Was this reply helpful? Yes No
jmcdm1 8 on at

Like (0)

Report
Thanks for those details, classic solution explorer was a good tip.

I've also looked into this a bit further and observed the following:

1) Currently, using the AAD object ID as the alt key in the dataflow produces my initial error because the Dataflow query automatically adds single quotes to the AAD object ID value.

For example, if I type the URL: https://xxx.crm11.dynamics.com/api/data/v9.1/systemusers(azureactivedirectoryobjectid='74f02344-fa73-452d-8853-a4233d71c14e'), I receive the same error as the Dataflow error:
{"error":{"code":"0x8006088a","message":"Bad Request - Error in query syntax."}}
But if I remove the single quotes from the AAD object ID value and have https://xxx.crm11.dynamics.com/api/data/v9.1/systemusers(azureactivedirectoryobjectid=74f02344-fa73-452d-8853-a4233d71c14e) , I receive the correct details.

Therefore, the Dataflow query is automatically adding single quotes to the AAD object ID values meaning the user lookup doesn't work.

2) When I tried using internalemailaddress as the alt key, I received the following error:

There was a problem obtaining table ovtr_test_employee_lookup Column ovtr_testuserlookup.internalemailaddress information from Dataverse. Please verify that column exist on Dataverse.

I don't understand why this is happening but I think it is due to either:

a) cache issues in Dataflow
b) Dataflow not reading the field name correctly because I noticed in the mapping section internalemailaddress field is shown as InternalEMailAddress:

The above are just observations which could be bugs.

My solution if I can't get the above to work will be to use Dataflow to import all the employee data and then have a separate cloudflow that configures the user lookup part. The cloudflow will be triggered everytime a record is added/updated in my employees table.

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

The workaround solution is a good one. Another option is to create a custom alt key on a custom column/field? Say you introduced a employee ID or email (although these columns both exists OOB)?

Was this reply helpful? Yes No

365-Assist

2,324 Moderator on at

Like (0)

Report

I have the same issue. I have a custom table that has lookup for System Users. I tried using different Alt keys but keep getting errors on the Refresh.

{"message":"A record with the specified key values does not exist in systemuser entity","cdsErrorCode":"0x80060891","HelpLink":"http://go.microsoft.com/fwlink/?LinkID=398563&error=Microsoft.Crm.CrmException%3a80060891&client=platform","InnerErrorMessage":"A record with the specified key values does not exist in systemuser entity"}

I also had to switch to doing it in Power Automate Cloud flows. Unfortunately, they are very slow. Would have been better to do this in Dataflows.