Dataverse table relation

(0) Share

Report

Posted on by lorenzoflai

Hi,

I have a monthly upload of Excel data file with this columns:

Name - Surname - Region - Contract
John Doe US Worker

I'd like to relate 3 tables:

Contract (Contract.ID, Contract.Name, Contract.Number)
Region(Region.ID, Region.Name, Regione.City)
Data_Import_Table (Name, Surname, Region, Contract)

Data_Import_Table is empty, so when I import Excel data like this

Name - Surname - Region - Contract
John Doe US Worker

I'd like to relate automatically the Region and Contract fields, so I can extract Regione.City and Contract.Number.

Final es.
Data_Import_Table

Name - Surname - Region.City - Contract.Number
John Doe Philly 102937

I'm using PowerApps as frontend, so I'd like to use Flow to upload the month data file.

thanks

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (0)

MarioRing 541 Super User 2024 Season 1 on at

Like (0)

Report

Hi,

What kind of support do you need?

Just create the tables in Dataverse, remembering that the field Name is automatically created during table creation. For table "Data Import" add a lookup field to Region table, and another lookup field to Contract table.

Import your dictionary data for tables Region and Contract. You don't need to use flow, there is an out-of-the-box mechanism to import the data to Dataverse - see here Import or export data from Microsoft Dataverse - Power Apps | Microsoft Learn.

Then you have to import your data to your "Data Import Table". Because you created lookup fields, the system automatically relates regions and contracts using "Name" fields as keys during the import.

To see data from related tables you can add additional columns from Regions or Contracts tables to one of your "Data Import Table" views

I recommend this free online Microsoft Training that will explain to you all the basics: Get started using Microsoft Dataverse - Training | Microsoft Learn

Was this reply helpful? Yes No
lorenzoflai 8 on at

Like (0)

Report

@Hi @MarioRing , thanks for reply

"For table "Data Import" add a lookup field to Region table, and another lookup field to Contract table."
Do I have to create "Data Import" table like this?
Name - Surname - Region - Contract - RegionLookup - ContractLookup

I tried to follow your directions, but after the import in "Data Import Table" the lookup fields are still empty.

Where am I wrong?

Was this reply helpful? Yes No
MarioRing 541 Super User 2024 Season 1 on at

Like (0)

Report
Steps:

Create table Contract (field Name will be added by default)

Add text column ID

Add text column Number

Create table Region (field Name will be added by default)

Add text column ID

Add text column City

Create table Data Import Table (field Name will be added by default)

Add text field Surname

Add field Region, where Data Type = Lookup; Related Table = Region

Add field Contract, where Data Type = Lookup; Related Table = Contract

Go to Data Import Table -> Views and edit "Active Data Import Tables" view

Add columns to your view and rearrange them as you like.

Please note that View editor allows you to put fields from related tables, when you click "Related" tab (see the picture above). Do not create City and Contract number columns directly in Data Import Table.

Update forms for Contract, Region, and Data Import Table by adding custom fields you've created

Create a custom Model-Driven App and put these three tables into the app

Add sample Regions and Contracts using your app

Create a record of Data Import Table, fill the lookups for Contract and Region

Go back to the view of Data Import Table to see how the data is shown

When you understand the concept you can import your data in bulk. For Data Import Table file use only 4 columns: Name, Surname, Region, Contract

Was this reply helpful? Yes No
lorenzoflai 8 on at

Like (0)

Report

@MarioRing thank you very much for your so detailed reply:
I followed your instructions, but after the bulk import in Data Import Table, Region lookup and Contract lookup are still empty.

Did you mean I have to fill Region lookup and Contract lookup manually for each imported record?

Thanks

Was this reply helpful? Yes No
MarioRing 541 Super User 2024 Season 1 on at

Like (0)

Report

Please document your step-by-step approach - show me the data structure, import file, how you import the file, and what error messages you receive.

Typically, during import, if the system cannot resolve any lookup in the importing record the record is not imported as a whole. That means your import mapping is probably wrong.

Try to import a simple CSV file instead of an excel one:

Name;Surname;Region;Contract;

John;Doe;California;ABC123;

Please note you need to have an existing record of Region, where Region.Name = California, and an existing Contract, where Contract.Name = ABC123.

Was this reply helpful? Yes No
lorenzoflai 8 on at

Like (0)

Report

Hi Mario,
after several try I find the way to import my data: the problem was on import mapping. I didn't set the lookup columns in my import process.

So thanks a lot for your support.

Now I'm trying to solve another little problem about import: a Date column send back error. It is Date format in excel file and Date format in the Dataverse table.

Was this reply helpful? Yes No