cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Memorable Member
Memorable Member

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

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

Highlighted
Community Support
Community Support

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

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

Highlighted
Helper III
Helper III

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

Hi,

 

Thanks a lot for your help,

 

Regards,

 

Arioule

View solution in original post

Highlighted
Helper III
Helper III

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

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

Highlighted
Super User
Super User

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

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
Highlighted
Memorable Member
Memorable Member

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

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

Highlighted
Community Support
Community Support

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

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

Highlighted
Helper III
Helper III

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

Hi,

 

Thanks a lot for your help,

 

Regards,

 

Arioule

View solution in original post

Highlighted
Helper III
Helper III

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

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

Highlighted
Helper III
Helper III

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

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

Highlighted
Super User
Super User

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

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

Highlighted
Regular Visitor

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

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,

 

Highlighted
Helper III
Helper III

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

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

Highlighted
Super User
Super User

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

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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (8,603)