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

[hell-1931]

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.

[dpoggemann]

Hi @hell-1931 ,

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

[hell-1931]

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? 

[dpoggemann · Accepted solution]

Hi @hell-1931 ,

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

[hell-1931]

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

[wwweber]

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. 

[rampprakash]

Hello @hell-1931,

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

[hell-1931]

Hello @rampprakash,

Do you mean - I should map ProgramWHID ando not toucb ProgramName? Do not map this field?

[dpoggemann]

Hi @hell-1931 ,

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

[hell-1931]

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