cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

Referencing a record from another CDS table and finding it back without delegation issues

I have two tables say A and B.

A has a field that sometimes can refence to a record in B.

In SQL databases this is no problem, just make a unique numeric id field in B etc.

But in a powerapps entity I really do not understand how to achieve this.

I made a GUID sized string field in A called BId and put the GUID of the B record in there (a GUID is a string right?)

Now if I want to find back the record in B it says that a string and a guid are incompatible.

If I find GUID(string) (LookUp etc) it does find the right record but I get blue delegation warning lines, so if B gets > 2000 records I am fubarred.

If I want to cache complete table B when it is more then 2000 records: tricks to do that depend on a unique numeric id field in the table and Powerapps entities do not have that type of field. Again Fubarred.

This should be dead simple. I am missing something for sure. The explanations and machinations of entity relations are obscure to me. If I introduce a relation now, how do I update existing related records. Does powerapps compute with the fact that some records might be related but not all of them.

????

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Impactful Individual
Impactful Individual

Re: Referencing a record from another CDS table and finding it back without delegation issues

Hi @HansHeintz 

I know what you mean, I came from a relational database mindset and also found the entity relationship explanations a little obscure.

So A has a lookup to B (sometimes). The easiest way to achieve this is to simply make a new field in A with the same name as the entity B and use the Lookup field type. For example, if some of my Employees are assigned Departments, then I make a Lookup field called Department on my Employee entity:

Annotation 2019-12-31 095012.png

Doing this will allow A to lookup to B. CDS will handle the relationship under the covers, so try not to overthink it (which was my problem for so long!)

Once you have the relationship we can work on applying the right filters in your app to use it appropriately. 

Let's work through this, and I guarantee you will start loving CDS for building apps!

 

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

View solution in original post

3 REPLIES 3
Highlighted
Impactful Individual
Impactful Individual

Re: Referencing a record from another CDS table and finding it back without delegation issues

Hi @HansHeintz 

I know what you mean, I came from a relational database mindset and also found the entity relationship explanations a little obscure.

So A has a lookup to B (sometimes). The easiest way to achieve this is to simply make a new field in A with the same name as the entity B and use the Lookup field type. For example, if some of my Employees are assigned Departments, then I make a Lookup field called Department on my Employee entity:

Annotation 2019-12-31 095012.png

Doing this will allow A to lookup to B. CDS will handle the relationship under the covers, so try not to overthink it (which was my problem for so long!)

Once you have the relationship we can work on applying the right filters in your app to use it appropriately. 

Let's work through this, and I guarantee you will start loving CDS for building apps!

 

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

View solution in original post

Highlighted
Community Support
Community Support

Re: Referencing a record from another CDS table and finding it back without delegation issues

Hi @HansHeintz ,

 

Truly, the usage of the relationship of CDS is something different from the relational database.

From your post, we can know the TableA is N:1 to TableB, so in CDS, you should create N:1 relationship, or create a Lookup Field in TableA to reference TableB. These two approaches are essentially the same.

After creating the relationship between two entities, you have to bind the TableB record to TableA. Here are two resolutions by using canvas apps or Model-driven apps.

1. Use Relate/UnRelate function to link two records or remove the link in the canvas apps. Reference: Relate and Unrelate functions 

2. Select the TableB record directly in the lookup dropdown field in a model-driven app based on TableA. First, you should add the lookup field into Form, then create a model-driven app based on TableA. Please check the following GIF, Primary Contact Column is a lookup field in Account entity to reference to Contact entity.

StoG_15.gif

Q: Does powerapps compute with the fact that some records might be related but not all of them

A: If you want to relate all of them, just set the lookup field empty.

Sik

 

Highlighted
Resolver II
Resolver II

Re: Referencing a record from another CDS table and finding it back without delegation issues

Guess I totally overlooked this field type, while I did go through the list. Thanks, works perfectly!

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,484)