Unanswered

Common Data Service - Modelling relationships

(0) Share

Report

Posted on by RoryJC

Hi all,

Everyone is saying that CDS is a a great relational data manager - but I'm struggling to see how the relationships actually work. I'm trying to use it to build a data model like you would in Excel/Power BI, that's then queryable in Power BI/Excel etc.

It seems when you're building relationships, it's actually just creating a lookup.

The first thing I don't understand is, how does the relationship know which field to lookup against? Does it only looup using the primary field in the parent entity, for example?
If I want to create a relationship, that is then queryable in Power BI, for example.... counting how many books an author has written is this possible?
- It seems like you'd create a lookup for book titles in your author entity, but then the count of those?

Am I on the wrong track here?

Cheers!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

JisungHan on at

Like (0)

Report
Hi @RoryJC ,

The relationship for the Common Data Service is exactly the same as the MS SQL PK/FK scenario (1:N, N:1) and N:N with subgrid.

Please refer to the following reference:

Entity relationships overview

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-edit-entity-relationships

Customize entity relationship metadata

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/developer/customize-entity-relationship-metadata

The document is from Dynamics 365. The Common Data Service structure was originated from the Dynamics 365, it is the same mechanism.

Answer to your questions:

The first thing I don't understand is, how does the relationship know which field to lookup against? Does it only looup using the primary field in the parent entity, for example?

[Answer] if you create 1:N relationship, a new lookup field will be created to the entity: N side

And the N:N relationship will not create a visible field since it will have relationship(matching) table in the backend side

You can add subgrid or call a related data from the view

If I want to create a relationship, that is then queryable in Power BI, for example.... counting how many books an author has written is this possible?

[Answer] Yes, this is possible.

It seems like you'd create a lookup for book titles in your author entity, but then the count of those?

[Answer] You are right. 1 (author): N (book) relationship can establish the relationship table and call the data through filter(multi records)/lookup(1 record)

Hope this helps. Thanks!

Best regards,

Jisung Han

Was this reply helpful? Yes No
RoryJC 6 on at

Like (0)

Report

Thanks,

I'm slowly understanding. I'm used to a graphical relationship builder, like on Power BI. So it's taking some getting used to.

I've created my two entities in a test solution:
- Registration record
- Cash transfers

We register someone, and take their number. We then transfer money and record an amount and date on the Cash Transfers table.

I then created a relationship. 1:N from Registration record to Cash transfers. As there will be multiple cash transfers for each record.

I don't follow how to customise the relationship. I've never used Dynamics so don't recognise the info on the link you shared..

For the lookup column:
- how to I tell it that the telephone number is the common field?
- How do I tell it I want the name back?

It looks to me that the Lookup field display name and Lookup field name are just the column headers for the lookup field in the table/entity, and don't drive behaviour.

All help much appreciated

P.S what I then want to do is have a lookup in the Reg record that lists all the transfers, and gives sum of total transfers etc

Was this reply helpful? Yes No
Joel CustomerEffective 3,224 on at

Like (0)

Report

Lookup field always shows the name field of the related entity, but you can make other fields appear on the form as well using what is called a quick view form.

Also, you can define find columns in the quick find view to allow you to type other fields than the name into the lookup field and resolve to the record.

Was this reply helpful? Yes No
Verified answer

Joel CustomerEffective 3,224 on at

Like (0)

Report

@RoryJC I made a video that should tell you everything you need to know about working with relationships and lookup fields. https://youtu.be/jMwRlMUytRA

Was this reply helpful? Yes No