How to import data from an Excel File into an Entity with lookup fields

(0) Share

Report

Posted on by Arioule

156

Hi,

I would like to import data from an excel file into an Entity using the "Get Data" Feature. This Entity got a custom Field (named "User") which is a LookUp Field to the System User Entity.

However when I reach the "Field Mapping" screen, the "User" Field doesn't appear in the Destination Field Column, so the mapping to one of my source columns is impossible. Is there any reason why ?
How can I make the mapping to the "User" field possible ?

Thanks,

Arioule

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (10)

Answers (5)

Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Hi @Arioule, lookup fields are not fully supported yet. The workaround is to create an alternate key on the user entity and then you would be able to map the User lookup field. Here's an article that describes this more in details: https://dynamicscitizendeveloper.com/2020/04/21/how-to-map-a-lookup-field-in-a-power-platform-dataflow/
Hope this helps

Was this reply helpful? Yes No
Verified answer

v-xida-msft on at

Like (0)

Report

Hi @Arioule ,

Based on the issue that you mentioned, I agree with @EricRegnier 's thought almost.

In default, when you import data from other data source into a CDS Entity, the LookUp field would not be shown up within the "Field Mapping" part. If you want the LookUp field to be shown up in the "Field Mapping" part, I think the "Alternative Key" in CDS Entity could achieve your needs.

Please consider check and see if the following blog would help in your scenario:

https://dynamicscitizendeveloper.com/2020/04/21/how-to-map-a-lookup-field-in-a-power-platform-dataflow/

https://community.dynamics.com/crm/b/microsoftdynamicscrmsolutions/posts/define-alternate-keys-for-an-entity

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/define-alternate-keys-entity#targetText=With%20alternate%20keys%20you%20can,place%20of%20the%20primary%20key.

On your side, you should create a "Alternative Key" for your LookUp field in your CDS Entity as below:

then you could see the LookUp field in your "Field Mapping" panel. Hopes above solution could help in your scenario.

Regards,

Was this reply helpful? Yes No
Verified answer

Arioule 156 on at

Like (0)

Report

Hi,

Thanks a lot for your help,

Regards,

Arioule

Was this reply helpful? Yes No
Arioule 156 on at

Like (0)

Report

Hi @v-xida-msft,

I have tried to define the fullname field as an alternative key for the user Entity but it does not seem to be possible, is there a way to force it ?

Thanks
Regards,

Arioule

Was this reply helpful? Yes No
Verified answer

Arioule 156 on at

Like (0)

Report

Hi @EricRegnier,

I have tried to define the fullname field as an alternative key for the user Entity but it does not seem to be possible, is there a way to force it ?
Thanks,

Regards,

Arioule

Was this reply helpful? Yes No
Joel CustomerEffective 3,224 on at

Like (0)

Report

Full name isn’t a real field. It is a concatenation of first name and last name. Do a composite key with first name and last name

Was this reply helpful? Yes No
Kiki 31 on at

Like (0)

Report

Hi @jlindstrom

I have tried to create the composite key as you told me. I got this error message :
The entity 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..

Regards,

Was this reply helpful? Yes No
Arioule 156 on at

Like (0)

Report

Hi @jlindstrom
I have tried to create the composite key as you told me.
I got this error message : The entity 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..

Regards,

Arioule

Was this reply helpful? Yes No
Verified answer

Joel CustomerEffective 3,224 on at

Like (0)

Report

Yes that is a limit of sql that cds is built on. Shorten the length of some of the fields in your key https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/207912/getting-error-in-solution-import-from-development-server-to-other-server

Was this reply helpful? Yes No
Arioule 156 on at

Like (0)

Report

Hi @jlindstrom,

Thanks a lot

Was this reply helpful? Yes No