Using properties over multiple relationships

[ingovals]

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?

[Drrickryp]

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

[ingovals]

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 )?