Confused about CDS relationships

[kman42]

I have two entities that I would like to relate, 'proposal' and 'team'. This should be a one-to-many relationship using the proposal_id field as the key. I can't figure out how to do this. When I create a new relationship, it doesn't give me the option to select the common key field. There are fields for Lookup Field Name and Lookup Field Display Name, but I can't select proposal_id. It tells me there is already a field of that name. I guess I am used to just selecting the columns in the tables that form the relationship and not quite understanding how this works in CDS with entities.

Thanks!

[v-yutliu-msft]

Hi @kman42 ,

Firstly, only 1:N or N:1 relationship has a lookup field.

Secondly, you do not need to create a lookup field.

After you create a 1:N/N:1 relationship, a lookup field will generate in primary entity(the one represents N) automatically.

Then in the app, you could use this "N" entity to get the data from "1" entity by using this lookup field.

For example: 1 entity named testone, N entity named testmany, I create a 1:N relationship between them. 

In the app, connect your app with testone entity and testmany entity.

Insert a gallery, set its data source to testmany, set one label inside the gallery's Text: ThisItem.lookupfieldname.field in test one

Then you could get data from testone by using this lookup field.

You could only filter data based on it.

Here's a doc about relationship for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-entity-lookup

Best regards,

[kman42]

I don't understand. How does it know which records in the many table are associated with which records in the one table? Normally, this would be done with a key/foreign key connection. If I don't tell it which column to use as the key/foreign key after I import my data, how does it know how to relate the tables?

Proposal

ID 

1

2

3

Team

ID          Name

1            Bob

1            Susie

1            Phil

2            Bob

2            John

3            Lisa

3            Susie

3            Kent

3            John

[Drrickryp]

Hi @kman42 

PowerApps uses its own GUID to maintain the relationships so it is not necessary to manually map the foreign keys going forward.  However, if you want to import pre-existing data into related entities, at the present time, the best way to do this is using the data import wizard from Dynamics 365.  There is a useful blog post that shows you exactly how.  https://devblogs.microsoft.com/premier-developer/how-to-import-related-data-into-dynamics-365/  You can get there from https://admin.powerplatform.microsoft.com/  sign in and select environments.  The gear labelled settings in the header will open this page.  The wizard is under the Data Management section.  Once you have imported the data and mapped the Lookup fields, the relationships should be maintained automatically by PowerApps using its own GUIDs. 

[kman42 · Accepted solution]

Thank you. How do I keep the data up to date? Can the import wizard run on a refresh schedule like dataflow?

[Drrickryp]

You can save the data map and reuse it to import new records. There is no way to schedule the data import at this time.