Unanswered

How to "join" Dataverse/Common Data Service tables in a Canvas app

(0) Share

Report

Posted on by RandyBristol

Hello,

I am developing a Canvas app that will require me to work with field/attribute data from a related parent, grandparent, and great-grandparent table/entity. I realize that I can use dot notation to traverse one level to a parent record (which is great) however, I need to be able to traverse more than one level at a time to grandparent and great-grandparent records as well.

My idea is to create a Collection and then use the Lookup function to gather the parent, grandparent and great-grandparent fields that I need, and then use this collection in my app. I have not been able to figure out the formula syntax to "join" records together using the Lookup function (or any other method) on a Dataverse/CDS data source. Can someone show me an example of this? Even if it is just joining a parent record to a child record for starters, then perhaps I can figure out how to nest them together to get grandparents and great-grandparents.

For example to join the Contact table/entity to the Account table/entity using the Company Name field in the Dataverse/CDS base database using the Company Name lookup field relationship. Or can you point me to a blog or video or forum item where someone has done this without using dot notation?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Hi @RandyBristol

Please see this thread. It has links to my blog.Re: Custom intermediary table (N:N relationship) i...

Was this reply helpful? Yes No
RandyBristol 22 on at

Like (0)

Report

Hello @Drrickryp . I reviewed both links you provided on the topic:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Custom-intermediary-table-N-N-relationship-in-CDS-What-is-best/m-p/789091#M251533

I understand how to create and work with relationships on the Dataverse/CDS side of things. What I am looking for is specifically how to "join" the tables in a One to Many (1:N) relationship without using dot notation, so that I can access fields from parent/grandparent/great-grandparent records. I did not see any information in your linked articles that provided instructions on how to do that.

Do you have an example of a formula that will; create a collection, join two Dataverse/CDS tables that have a One to Many (1:N) relationship, and return records with fields from the child and parent records (without using dot notation)?

Thanks in advance for your help.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

@RandyBristol

I add my own primary keys to all my tables and manually add the foreign keys to each table. The relationships are managed with lookups and filters based on these keys.

Was this reply helpful? Yes No
RandyBristol 22 on at

Like (0)

Report

With Dataverse/CDS, I do not need to add an additional primary key to my tables/entities as it automatically adds a GUID value through the Primary Name Column. It is my understanding that Canvas Apps can use this somewhat hidden primary key to relate one table to another using dot notation. I would like to understand the formula syntax for "joining" two tables that have a One to Many (1:N) relationship in Dataverse/CDS without using dot notation so that I can access fields from grandparent and great-grandparent records. Do you know how to do this?

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report

Hi @RandyBristol ,

Below formula is for filtering contacts based on account selected in dropdown box.

Filter(Contacts, 'Company Name' = Dropdown1.Selected)

Regards,
Prakash

Was this reply helpful? Yes No
RandyBristol 22 on at

Like (0)

Report

Thanks for the post, @Prakash4691. Filtering contacts based on a company name is not what I am looking to do. I am attempting to "join" two tables in a Dataverse/CDS database in my Canvas app. Does anyone know how to do this?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (2)

Report

Hi @RandyBristol

I use dataverse tables exclusively in my own apps and do not particularly use dot notation. I always assign my own numeric ID to every table I create and include it as the foreign key in child tables. This creates the join you are looking for. My Filter() and Lookup() functions are used extensively to traverse the levels of tables within PowerApps as you have described in your initial post. In particular, I create my own junction tables and don't rely on dataverse to create 1:M and M:M built in relationships. I find that these shortcuts have disadvantages and prefer to do it completely manually. I may be a database purist, but it has worked for me for over 20 years and I'm used to it. Classical database design is not quite dead yet.

Was this reply helpful? Yes No