cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RandyBristol
Frequent Visitor

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

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?

7 REPLIES 7
Drrickryp
Super User II
Super User II

Hi @RandyBristol 

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

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-i...

 

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.

Drrickryp
Super User II
Super User II

@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.

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?

Prakash4691
Resolver II
Resolver II

Hi @RandyBristol ,

 

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

 

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

 

 

Regards,

Prakash

RandyBristol
Frequent Visitor

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?

Drrickryp
Super User II
Super User II

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. 

freshly dead.gif

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (30,535)