Data Import to lookup columns referencing AADUser

[DennyThomas]

Hi Guys,

I've a model driven app with a look up field referencing to aaduser table. However when I try to import data using dataflow, this field is not appearing in the mapping screen even though I added it as an alternate key. But it works if the field is referencing to user table. Then, I tried "Import from excel" option in the model driven app and it shows the look up field  in the mapping screen, however the import job returns an error "the lookup reference could not be resolved". The look up values I'm trying to import are available in aaduser table and no duplicates. I tried most of the fields in aaduser like  guid, email id etc and nothing worked.

Failure Details

AAD User Table

Is this a limitation of aaduser being a virtual table? Is there any other way to import historical data to the look ups referencing aaduser? I really appreciate your help.

[Jakczxcv]

> ...import actions are not supported for AAD User...

I hope you don't understand the question, and there's still an answer out there somewhere. People don't want to import to AAD user, they want to import in any other table that has a lookup column to AAD user. Seeing as how no one can get it to work, you're probably right.

All the system should need from the virtual table is to read data and autocomplete the ID, saving it behind the scenes in the dataverse table's lookup column. We can see that it already can do that in the normal one-record-at-a-time interface, but during an import, it seems to always bomb out.

It sounds like the conclusion is that no import actions are supported for any dataverse table with a lookup field related to AAD User? Why would MS even bother creating AAD User if nobody can ever get started with it, unless their org was born yesterday and has no data to import? It sounds like a big waste of time to try and use it only to have to start over and create our own "AAD User except that it works" mirror table, rebuilding it with the exact same data, ourselves? That would be a shame.

[kentonh]

Thanks, @NHelgren. However, the answer you provide doesn't match the issue:

• The import was *not* into the AAD User virtual table.
• The import was into a Dataverse table with a lookup into AAD User. Therefore the relevant import column is  AAD User GUID.

Ideally, one should be able to provide the User Principal Name (the unique AD user identifier) in the import and resolve the lookup to the AAD User GUID. Model Driven apps are smart about lookups elsewhere, so it seems like a reasonable expectation.

The documentation for the feature does not list the limitations or how to handle lookup values: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/data-platform-import-export#option-....

I was new to Powerapps and drinking from a firehose. Afterward, I found DataFlows, which is a better option. As mentioned above, I used SQL in XrmToolkit's SQL 4 CDS, but this doesn't work for "citizen programmers," which I presume is who this feature targets.

Thanks,

Kenton

[Jakczxcv]

I think that the template and import/export features are supposed to be usable even by modestly technical end-users. Any authorized user who wants to modify more than a few records at once (with information they acquired faster than they could re-type or copy/paste it all) should have some means to do it. I will try it with dataflows, too, though.

[sallyolle]

We dont want to import to the AAD User (if everyone here has the same problem as me), we want to import a reference to the AADUser into a lookup column in Dataverse eg: Job assigned to.

Im thinking maybe export Sharepoint list to excel, update Assigned to column by looking up the guid then trying to import that?  Ill report back.

An even worse scenario is a multi person lookup.

[Amik]

There seem to be multiple requirements in this thread that people need resolved, but just to add my 2 cents on AAD user names, you can certainly bulk import AAD user names from an Excel file for example into a custom Dataverse table which uses the AAD User table as a Lookup column.

See thread here I posted a few weeks on this specific topic: 

https://powerusers.microsoft.com/t5/Microsoft-Dataverse/How-can-i-import-the-excel-file-to-Dataverse...

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution

If you like my response, please give it a Thumbs Up.

[Lenny3]

Hey @NHelgren, I'm having the same that others have mentioned in response on this thread.  Say you have a custom table that has a lookup field to the AAD User virtual table.  When trying to import records into that custom table via CSV file in a model driven app I get an error saying "The lookup reference could not be resolved" for every row in the import. AAD user ids are used in the CSV file and are properly mapped in the import wizard to the AAD user id column in the virtual table.

I've looked at so many forum and blog posts and can't figure this out.  Please help!

[sallyolle]

Exporting the AAD table then looking up the user guid and importing THAT into an existing table (that looks up the AAD) via excel worked for me. Note that importing via excel is not available in teams so do it in a non-teams environment and export the solution.

[Lenny3]

Thank you, @sallyolle, for responding!  How did you export the AAD User table?  The import and export buttons are greyed out for me.  I just figured this was a limitation for virtual tables.

[sallyolle]

Once you are in the table section, go to edit in excel (sorry not export).

[Lenny3]

Ah, I see.  Is the GUID you're referring to one of these two fields? "AAD user id" or "A unique identifer for AAD User".  These are the only ID fields I've been able to find, and they're exactly the same from what I can tell.  If this is what you're referring to, this is already the method I've been getting the error with.