Answered

Connecting 2 tables

(0) Share

Report

Posted on by u122945

Hello,

I still don´t unterstand how to connect two tables. Both of them are imported from MS Access and included Primary Key and data (maybe that´s the problem?).

Structure:

Table 1: Customers (Primary Key is Cust.Nr.), name, address etc.

Table 2: Specific information for each Customer (Primary Key is Cust.Nr. as well)

What I need is a 1:1 relationship.

Of course I can add a lookup-field to create a relationship between the 2 tables. But I have to fill hundreds of rows with the Cust.Nr. manually to get the records connected. Or can I automate this process? And for every new customer I have to type in the Cust.Nr. two times ...??

Possible solution? Create a new Table 2. Cust.Nr. is a lookup-field and Primary Key is another information, e.g. an automatic generated field?

I hope my explanation is clear. Every hint is appreciated :-) Thank you!

Best regards

Roland

.........................................................................................................................

hello tsa ! (I can´t find a reply button, so I edit my first post)

It seems to me, that you refer to a Canvas-App. So I didn´t pursue your suggestion any further, but thank you anyway.

I tried the following:

First I exported data.

Then I created a new Table 2. Cust.Nr. is a lookup-field and Primary Key is a GUID-field.

I imported the data in the new table and it works :-)

best regards, Roland

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (2)

Sort by

tsa-svd2srv 204 on at

Like (0)

Report
Copy link

Link copied!
I have only tried something like this a couple times, so I wonder if you'll give it try:

AddColumns( Table1, "Table2Data", LookUp( Table2, 'Cust.Nr.' = ThisRecord.'Cust.Nr.' ) )

This stores the columns from Table2 in a column in Table1 called, "Table2Data" as a record.

Let's say your using it in a galley's Items property, you can now use stuff from Table1 that might be something like:

ThisItem.FirstName

And, if you want to use a column from Table2, maybe something like:

ThisItem.Table2Data.ShoeSize //A ColumnName from Table2

Let me know if it works.

Was this reply helpful? Yes No
Verified answer

Ram Prakash Duraisamy 5,714 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Based on my knowledge, you ca create a lookup between 2 tables and use the Primary key.

By Creating a Record in Table 2 and map that with Table 1 using Patch Query.

Please mark as answer if my suggestion helps.
Subscribe here for More Useful videos : https://www.youtube.com/@rampprakash3991

Was this reply helpful? Yes No
Verified answer

Jonathan Manrique 2,695 on at

Like (0)

Report
Copy link

Link copied!

Hi

Relationships in Dataverse do not exist 1:1

We have 1:N relationships, meaning that an element can be related to N elements, its representation in a form would be a list of elements associated with the parent record.

The other relationship we have is an N:1 relationship relating an element N times to a single record.

The closest thing to what you are looking for would be to use the N:1 relationship that generates a lookup against table B

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
You can accept more than one post as a solution
Follow me on Linkedin, I talk about Power Platform
www.linkedin.com/in/jonathan-manrique-rios

Was this reply helpful? Yes No