Importing problem of related tables - from excel to Dataverse and creating lookup data type (with drop down fild)

(0) Share

Report

Posted on by Community Power Pla...

I have 2 related excel tables, originally came from SQL:

tbl Program - "Dimension" - with unique records (WHID = primary key)

tbl Attendance -"Fact" - with duplicate records (ProgramWHID = foreign key)

I need to import them into Dataverse and then - to create PowerApp canvas (or any type) Form, based on tbl Attendance.

So, that the [ProgramName] Attendance field would be a drop-down type, taking data from [ProgramName] Program.

And that [ProgramWHID] Attendance value would be automatically populating from [WHID] Program,

after user changes / creates record.

For that my actions were:

1. Created tbl Program, as

WHID = Primary Name Column (Text);

ProgramName = Text

I really need WHID as "Whole Number", but the choices there "AutoNumber" or "Text" only.

2. Created tbl Attendance, as:

AttendID = Primary Name Column (AutoNumber)

Date = Date Only

ProgramName = Lookup (Related table = Program)

ProgramWHID = Lookup (Related table = Program)

3. Imported tbl Program from excel, mapped fields to create tbl Program in Dataverse

This table imported with no problems.

4. Tried to import tbl Attendance from excel, tried to map fields to create tbl Attendance in Dataverse

Here is the main problem:

After I mapped all custom fields including ProgramName and ProgramWHID -> to my excel fields from pic above

- receiving "Import Failed" error: "Couldn't resolve the guid for the lookup field: cr118_programname"

Please, help!

Did I make my relations or fields wrong types? Should I use different approach when importing from excel?

I am new to this system. Any ideas or info - strongly appreciated.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (12)

Answers (1)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (2)

Report

Hi @Anonymous ,

You have added two columns on the Attendance table for lookup to the Program. You would only want 1 column and this will map to your Primary Name column. You should be able to then import the Attendance records with the columns:
AttendId, ProgramWHID, Date

You will enter the 5000 in the ProgramWHID and this will automatically lookup based on that Primary Name column from Program and match.

Note, you can download the data template for the Excel import from the Dataverse for the attendance table by going here:
https://admin.powerplatform.microsoft.com/envirnoments

Hope this helps. Please accept if answers your question or Like if helps in any way.

Thanks,
Drew

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

To dpoggemann ,
Sorry it took so long to answer (I've been drawn away with a different projects, but now coming back to this one):
Sorry, but the following is not clear for me:
<...You would only want 1 column and this will map to your Primary Name column. ...>
Do you mean I have to make only ProgramWHID as Lookup field in my Attendance table?
And ProgramName would be Text type?

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (2)

Report

Hi @Anonymous ,
The Lookup type will always connect with the Primary Name column of the referenced table, in your case you have the WHID as the Primary Name column from the Program table so in your import you would populate this field. If you wanted the Program Name on the attendance table as well as on the Program table then yes, you would have this as a Text type and populate in the Import.

Overall you could add the Program Name to your views etc. without adding it to the Attendance table since is is part of an N:1 relationship. You can show any field from the Program table on the Attendance views (see Related tab when adding fields to your view) and you can also add a Quick View form to your Attendance table form to show fields from the Program table on the model view form for Attendance as well.

Quick View Form: https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/create-edit-quick-view-forms

Hope this helps.

Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi dpoggemann,
I tried - I changed my ProgramName to text type, then tried to re-import "from excel" - getting absolutely the same mistake. The problem is that for some reason it's impossible to import Lookup type. Once I change both ProgramWHID and ProgramName in the Attendance tbl to "text" - then the import is successful!

I don't understand why can't it be possible to import the Lookup type fields from excel sheet.

My end goal is to create "Canvas" App based on Attendance table - so that a users will be able to perform data entry (to enter data into Attendance App). And in that Attendance App I want ProgramName field would be taken from ProgramName filed of the Program tbl as data source. So it would be a drop-down field!

May be this is just impossible to do using Dataverse ?
May be I should create model-driven app somehow differently?

Kind of stuck there...

Was this reply helpful? Yes No
wwweber 146 on at

Like (0)

Report

The field will work as a search field (lookup field) so it will be possible to search for the existing values or create new ones. If you wanted a drop-down then this should be created as a "choice" or "choices" field instead. This will limit the options to only include the ones you have selected.

As @dpoggemann mentioned a reference to another table will always use the "name" field because this is the default (required) name field for the record on the table. This field is always a text field.

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

Hello @Anonymous,

Obviously yes, you are mapping wrong fields, try mapping Parent table with Name instead GUID field.
As per Error Statement you are mapping cr118_programname(its a GUID) try mapping to the Primary Field of the record.

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hello @rampprakash,
Do you mean - I should map ProgramWHID ando not toucb ProgramName? Do not map this field?

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report

Hi @Anonymous ,

From what I read above the ProgramWHID is your Primary Name field. This is what you should be able to map to in your import on the related table. You should not need the ProgramName field in this import file.

If you are completely stuck, we could do a screen share or something to walk through, just private message me and we can work through times.

Thanks much!

Drew

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @dpoggemann ,
Yes, unfortunately, stuck completely on this
I'd love to share screen or to connect - so I'd show my problem directly from Dataverse...
It'd be great to set up a date-time and tool (when we both can connect).

After I was trying to send you pm - the following msg appears:
"you have reached the limit for number of private messages that you can send for now. please try again later "

Even after I cleared cache

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@dpoggemann
Thank you so much for all the info and for the HELP!
Please let me know if I can click anything else (here or anywhere) in order to add a score to your profile or to these comments!
(Not very familiar with this website yet)

Was this reply helpful? Yes No