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?
Hello @Drrickryp . I reviewed both links you provided on the topic:
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.
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?
Hi @RandyBristol ,
Below formula is for filtering contacts based on account selected in dropdown box.
Filter(Contacts, 'Company Name' = Dropdown1.Selected)
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?
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.