Create relationship between 2 existing CDS entities doesn't work

[svenvu]

Hi

I'm stuck at probably a simple question but I can't seem to figure it out.

I have 2 existing entities that I want to connect through a relationship.

• Entity1: has 500 records and has to become the one side of the relationship
• Entity2: has 10.000 records and has to become the many side of the relationship

So in Entity2 I create a many-to-one relationship. With this relationship a new "lookup" field gets added automatically to Entity2 which is called Entity1ID. At this time this field/column has no values in it. So I click on "Edit data in Excel" and fill in the 10.000 values for the column Entity1ID but when I click on "Publish" nothing happens. It seems that I can't save the values and therefore can't create the relationship because it is an empty column.

Any help is much appreciated!

[Drrickryp]

I’m guessing but do both fields need to be text and the “one” field designed the name field?

[v-xida-msft · Accepted solution]

Hi @svenvu ,

Could you please share a bit more about your scenario?
Do you want to populate the EntityID column (LookUp column) in your Entity2 with values (Primary Name) from your Entity1 within the Excel table (which opens via clicking "Edit data in Excel")?

Based on the issue that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. We could populate the LookUp column of my CDS Entity within the generated Excel Table successfully:

Please check if there are some records existed within your Entity1 already. The EntityID column (LookUp column) in your Entity2 would retrieve available values from your Entity1, so please make sure you have added related reocrds within your Entity1 already.

When you click the EntityID column box within the generated Excel table (from your Entity2), it would load available values from your Entity1 as above screenshot. Then you could select appropriate value from the right panel as above. After that, you could click "Publish" button, to push your modification to your Entity2.

Note: You could not type a custom value within the EntityID column in the genrated Excel table, you could only choose/select a value from the avaiables values in right panel as above.

If the issue still exists, please consider remove the relationship between your Entity1 and Entity2, then re-create a new relationship. Then try above solution, then check if the issue is solved.

Best regards,

[Drrickryp]

Hi @v-xida-msft 

It seems impractical to use your suggestion for 10k records updated one by one.  Is there another way to bulk update the column? I am having the same problem with a database containing 23000 items in a child table.

[svenvu]

Hi @v-xida-msft 

You are correct. I want to populate the Entity1ID column (LookUp column) in my Entity2 with values (ID column) from my Entity1 within the Excel table (which opens via clicking "Edit data in Excel") or through another way that can achieve the same.

The solution you provide works! It was not clear for me that I had to select a cell in Excel and choose the value from the right panel. However I encounter 2 issues with this way of working:

1. Like @Drrickryp  explains: this is a lot of work to manually select the value for more then 10.000 records + we will need to add 2000 records every 6 months which creates a lot of maintanance.
2. When the Excelfile grows too large, it is possible I will no longer be able to update records because there's a limit in how much CDS-data that can be shown in Excel and if the records are not shown, I will not be able to update those records

Your answer is indeed a solution that I wasn't aware of but unfortunately it is not a maintanancefriendly solution.

Is there any other creative solution that could make this easier?

For example: I was wondering if it is possible to create 2 text fields: one in Entity1 and one in Entity2 where I populate the values in Excel and finally connect those columns with a relationship? Or maybe I can update a lookup column without Excel?