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

[HansHeintz]

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.

????

[SeanHenderson · Accepted solution]

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:

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

[v-siky-msft]

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.

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

[HansHeintz]

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