cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ingovals
Helper II
Helper II

Using properties over multiple relationships

As a developer I'm having problems with Dataverse and Power Apps being used to SQL type relational databases. The issue is generally around multiple joins between tables. Almost all examples I find are using and displaying items from a single table, but I think rarely that is a real life situation. If things were that simple, we wouldn't really need this tool.

 

So my stumbling block comes when I have to access properties more than one LookUp away. To create a sample data lets say we would have these 3 tables/entities with 1 to many relationships ( hopefully self explanatory ).

 

Client

Order

Item ( this would be items inside orders, not standalone items, lets not complicate with many to many relationships at this time, let us assume items are created custom made for the client or something, or hand picked each order ).

Now working just Dataverse and creating a data view for the Items, I want to add a column with the name of the Client. This seems to be a no go. I can access the Order Lookup, but only from it the Client property that I guess will only give me the key/id.

Fair enough, maybe a limitation in the view part of DataVerse

 

Switch to PowerApps, lets say we want a screen that shows us all items that have not been served on, and we want to sort those items by the Client. Here client is two relationships away and I get stuck finding out how to do this. What if I wanna display the Client name in the DataTable as well. I can't seem to do it. I would have to do a LookUp and DataTable doesn't seem to allow me to do a ThisItem inside LookUp ( it just returns empty).

 

What I'm getting at that there are loads of issues that materialize when getting properties from linked tables and nigh impossible if there are more than one relationships.

 

So my question is, am I missing something. Is there a way to do this. Is there something akin to VIEWS in relational databases where I can create computed views that are just collected data from multiple tables, or are there maybe computed columns that I can add that show data from related entities through relationships?

2 REPLIES 2
Drrickryp
Super User
Super User

@ingovals 

In some ways, Dataverse is superior to SQL in that you can "walk" through relationships with dot notation.  You can also create views similar to how they are done in SQL.  See this article and the sample Northwind database for more information. https://powerapps.microsoft.com/en-us/blog/one-to-many-relationships-for-canvas-apps/ .

There is also an OOB way to create many to many relationships in Dataverse, however, I prefer to create a traditional junction table for them. 

I'll look into the view article, thank you.

 

Regarding the dot notation, that is exactly the problem, I can only access the properties of one level of relationships. If i try any deeper I get this error

Multiple levels of many-to-one relationships expansion aren't supported.

Is that perhaps a setting, or something to do with my datasources ( Dataverse )?

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,820)