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

[calerof]

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:

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

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":

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

Thanks,

Fernando

[AndKanPA]

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 😉

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.

[calerof]

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

[Anonymous]

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

[calerof]

Hello @Anonymous ,

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

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

I appreciate any help here.

Thanks,

Fernando

[Anonymous · Accepted solution]

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

[AndKanPA]

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.

[calerof]

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]

Hi @calerof ,

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

BR
AndKan.

[calerof]

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