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

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 Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,906)