LookUp() or create Relationship between tables - what do you prefer to retrieve related data and why?

[ManishM]

I have yet to create my first relationship with multiple custom entities in CDS for PowerApps. I am still using LookUp() to retrieve related data based in "autonumber" generated ref ID, and it's been working smoothly. I am working with over 800 records, just so you know the size of the data my app is handling.

Now, my questions for experts is:

is there a significant improvement in data access if I build relationship?

is there a significant improvement in dev time when using relationship?

will there be better data-access flexibility as compared to Lookup()?

would you say I should move to building relationships for my next app?

What else is going to be better by moving to relationship?

I am feeling compelled to go with relationship approach because that's how I have been doing in my good old days on RDBMS. This is going to be a huge shift hence reaching out to you experts to get more guidance.

Thanks you in advance 🙂

[v-xida-msft]

Hi @ManishM ,

Do you want to build up relationship between the multiple CDS Entities, and retrieve related records based on specific condition?

Based on the needs that you mentioned, I think Relationship in CDS Entity could achieve your needs without using LookUp function. If you set up relationship (One-to-Many, Many-to-One) between your CDS Entities, it would improve/increase the data access/processing speend.

More details about the Relationship in CDS Entities, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-edit-entity-relationship...

In addition, if you created a One-to-Many from one Entity to another Entity, it would generate a LookUp field in the Related Entity automatically, then within your canvas app, you could retrieve the related records through this LookUp field directly:

Add two Gallery controls in your app, connect Gallery A to Entity A, Entity A has a Many-to-One relationship to Entity B. Set the Items property of the Gallery B to following:

Gallery1.Selected.'LookUp Field'

Please consider take a try with above solution, then check if the issue is solved.

Please also check and see if the following video resource would help in your scenario:

https://www.youtube.com/watch?v=X6PaYaC1iR4

Best regards,

[ManishM]

Thank you for the response and I appreciate all the guidance you provided through links.

Everything you listed in your response can be done without building entity relationships. e.g. the lookup fields being generated automatically when relationship is created between entities - I can achieve the same using 'Filter' with negligible difference.

Additionally, what I have experienced, when filling Edit Form for a related entity, it got challenging. it became a lot complex to build and program it. This could be all me ignorant of ways of the world but I found it not as simple. But I only use Patch() now so may not be relevant anymore.

One other difference I remember one of the Power Platform dev told me that one can achieve nested references with LookUp() function but that is not the case with relationship just by itself.

What else - experts ? This list may help a lot of budding citizen devs.

Thank you in advance 🙂

Manish

[Drrickryp]

Hi @ManishM 

I have done it both ways and also converted from the way you are doing it to the one using built in entity relationships. These would be my observations, When I am referring the built in entity relationships, I am referring to those that are automatically created by using lookup fields in the entity.

First, I don't see any performance enhancement to using built in relationships.

Second, When I have a many to many relationship, I use a junction table.  Additional fields can be added to this table.  However, the built in Many to Many relationship in an entity is hidden and can not be modified. 

Third, if you start from scratch and build empty entities that you intend to fill in with data from a another data source while using the table relationships you already have, the best way to do this is through the Dynamics 365 Data Import Wizard.  If you go this route, it is a bit tricky but here is a very helpful blog post that describes how to do it.   https://crmbook.powerobjects.com/basics/data-management-in-microsoft-dynamics-crm/importing-data-in-...  Also, I have an illustrated post showing how to do this that includes some tips:   https://powerusers.microsoft.com/t5/Building-Power-Apps/Update-on-importing-relational-data-into-CDS...   

Fourth, if you are going to build a Model driven app, then definitely use the entity relationships by using lookup type fields and go through the trouble of using the data import wizard in dynamics to import your data. Otherwise, it will be difficult to show related data using the standard forms and views.  If your app is a Canvas type, I would stick to what you have if it works for you.

[v-xida-msft]

Hi @ManishM ,

Yeah, I agree with you. Currently, there are some known limits on the nested reference through Relationship in CDS Entities. You could only reference deep 2 level through the Relationship in CDS Entities.

To be honest, in some conditions, the LookUp function would be simple than relationship. But, the Relationship would do better on data retrieving than the LookUp function.

Actually, for choosing LookUp function or Relationship, it would be subject to your Business Scenario. In addition, if you want to bind or unbind one record of a Entity from another Entity quickly, the Relationship would be the best solution.

You could bind or unbind one record of a Entity from another Entity using Relate or Unrelate function quickly based on the Relationship you set up between the two Entities.

Best regards,

[ManishM]

@Drrickryp  Thanks for your detailed response. I appreciate the insight into Model-Driven app and D365. Unfortunately, I do not use either in their entirety. I use PA with CDS (and Flow as needed)

 @v-xida-msft 

One additional benefit I noticed with creating relationship is that the making "Views" with related values is possible. This reduces processing time during app launch.

I also use ClearCollect a lot to make my app fast by retrieving data from memory as well as not have 500 limitation (can be increased to 2000 but that does affect the performance). I use this method and LookUp() to create master table with all values and then access it that way. Would you (and other experts) say that I should not do that but build relationship between entities, and then create Views, and then access the view in the app.

Just to give you my background with PA - I have been building apps using PA since Jan 2017. During that time, a lot of features were not present so had to learn other ways to achieve desired outcome. Now, that has become my habit and if the update/change is not leaner, it becomes hard to shift, eventually falling back on what I have been practicing - basically it is hard to unlearn. Therefore gauging how experts are doing and recommending...which, I believe will help some budding citizen developers as well 🙂

Thank you,

Manish

[Drrickryp]

Hi @ManishM 

Thanks for that well thought out response.  I am in the same position as  you are with respect to PA and Dynamics365.  Fortunately, I have discovered some very interesting methods that are available to use some of the features of D365 as a back door to manipulating data, particularly adding  large data sets to entities and maintaining the relationships between the tables.  The secret is in the gear in the upper right hand column and the Advanced Settings.  Then click the down arrow next to settings and select Data Management.  There are a number of great tools to use here and all this can be done from within PowerApps.