cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
calerof
Helper III
Helper III

Can't add a relationship between two tables created with Dataflows

Hello,

I created a Dataflow in Power Apps to add data to a table from SQL Server. I created two tables, one called "OITM" and another called "OWOR".

 

Each of them have the column "ItemCode", which I want to use to create the relationship:

error_2.png

 

error_3.png

 

First, the tables don't have a relationship on that column (ItemCode):

error_0.png

 

Then, I add a relationship 1:N from OTIM to OWOR and use the column ItemCode as Lookup column, but after saving the table I get an error message:
"The table could not be updated: An attribute with the specified name xxx_ItemCode exists for entity xxx_OWOR":

 

error_1.png

 

 I'd appreciate a lot any help to continue with this app.

Thanks,

Fernando

1 ACCEPTED SOLUTION

Accepted Solutions
awerda
Resolver II
Resolver II

Hello @calerof,

 

I can't help you further about adding the relatioship and I don't think it is possible in your case to add it. But what I can suggest is that you ignore the relationship and you manage the data in the app in the way it would be managed if a relationship existed.

What I mean by that is that you create a lookup (dropdown) for that field in table2 (in the app level) and you populate it with distinct (table1, field) and go from there..  Your data will still be accurate and you wouldn't need the relationship.

 

Hope this helps you..

Regards

View solution in original post

9 REPLIES 9
AndKanPA
Resolver I
Resolver I

Hi @calerof 

you should remove column ItemCode from table OWOR, because it's only a simple custom column.

And then you replace it in OWOR with your relationship to OTIM, where ItemCode is primary field.

Because it becomes a relationship you dont need this column in OWOR.

 

If you dont want remove, because your dataflow needs it. Remove it and recreate with another name.
Create the new column with displayname 'ItemCode' and name itemcodesql (or what ever you want, but different from itemcode 😉

Screenshot 2020-12-22 122454.png

While importing with dataflow you can choose which SQL column to be imported in which Dataverse column.

 

But I'm sure you can simply remove column from OWOR, create relationship as you tried and then import again.
That's will be the way to create relation between records in different tables.

 

Hop this was helpfull,

AndKan.

 

Hello @AndKanPA ,

Thank you for your response. I still don't quite understand, because both tables are populated with data, OITM is the list of materials, and OWOR is the table with work orders that are being generated all the time by the company, and it includes the item code that is going to be manufactured.  If I remove it and rename it to bring it back, so what's the point to add a new relationship with an empty column?

Regards,

Fernando

awerda
Resolver II
Resolver II

Hello @calerof ,

 

I think the way relationships work in dataverse, if you want that field to be a lookup, you don't create the field and then add the relationship,. Rather, when you create the field, you choose its type as lookup and then link it with a field from second table and it will automatically create the relationship for you.

 

I would suggest as @AndKanPA said that you delete the field and then create it again as I explained with same name.. Maybe export your data before in excel to not loose it and then reimport it after adding the field with the relationship.

 

Regards,

Akram

 

Hello @awerda ,

Thank you for your response, but, I don't understand. I don't create the column ItemCode, I get data from SQL Server using Power Query from those two tables already populated with data, as shown below:

List of products (OTIM)List of products (OTIM)

 

List of work orders (OWOR)List of work orders (OWOR)

 

The work orders table (OWOR) includes the part number (ItemCode) which will be manufactured. 

In Power BI this is the essential for a data model, to create a 1:N relationship between the List of Products table (OITM) and the List of Work Orders table (OWOR) with the ItemCode common column. Here I just don't understand how to do that.

Also, I can't change the type of the ItemCode column in the OWOR table, it's either Text or Autonumber:

OITM_ItemCode_type.png

This should be a very common case for any data model.

I appreciate any help here.

Thanks,

Fernando

awerda
Resolver II
Resolver II

Hello @calerof,

 

I can't help you further about adding the relatioship and I don't think it is possible in your case to add it. But what I can suggest is that you ignore the relationship and you manage the data in the app in the way it would be managed if a relationship existed.

What I mean by that is that you create a lookup (dropdown) for that field in table2 (in the app level) and you populate it with distinct (table1, field) and go from there..  Your data will still be accurate and you wouldn't need the relationship.

 

Hope this helps you..

Regards

View solution in original post

AndKanPA
Resolver I
Resolver I

Hi @calerof ,

you can either create column OR a realtionship. But not both. Both would be useless and at least duplicate. Because both coluns are standalone without any relation.

 

So import table OTIM with column ItemCode from SQL.
Create table OWOR with realtionship to column ItemCode of OTIM.
Then import OWOR from SQL and import OWOR-ItemCode into relationship column to keep relations alive - then that's the most important point for you.

Dataverse/CDS handles realtionsships in an other way the SQL.

Cu,
AndKan.

Thank you for the support on this question. I'm going to leave it like that by now while I continue my journey to learn and use Dataverse, and don't create a relationship, instead I'm going to use LOOKUP to reference the columns in the other tables.

Thanks,

Fernando

AndKanPA
Resolver I
Resolver I

Hi @calerof ,

when you create a lookup-field in your table a relationship is created in background.

 

BR
AndKan.

Hi @AndKanPA ,

That's clear, problem is I'm not creating any field or table, I'm importing it from SQL Server with Power Query as is.

Thanks,

Fernando

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Kudoed Authors
Users online (83,624)