cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Arioule
Helper III
Helper III

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

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

5 ACCEPTED SOLUTIONS

Accepted Solutions
EricRegnier
Super User
Super User

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-datafl...

Hope this helps

 

View solution in original post

v-xida-msft
Community Support
Community Support

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-datafl...

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

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/define-alternate-keys-entit...

 

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

1.JPG

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

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi,

 

Thanks a lot for your help,

 

Regards,

 

Arioule

View solution in original post

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

View solution in original post

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-i...

View solution in original post

10 REPLIES 10
EricRegnier
Super User
Super User

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-datafl...

Hope this helps

 

View solution in original post

v-xida-msft
Community Support
Community Support

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-datafl...

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

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/define-alternate-keys-entit...

 

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

1.JPG

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

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi,

 

Thanks a lot for your help,

 

Regards,

 

Arioule

View solution in original post

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

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

View solution in original post

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

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,

 

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

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-i...

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,044)