cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ignacio_S
Regular Visitor

Show 3rd level entity data

Hi,

 

Im trying to create an app with CDS using data from dynamics. I have an issue based on 3 entities where te relationships are like this:  EntityA 1:N EntityB N:1 EntityC

in this case, i need to place data from EntityC that corresponds to an EntityA. What i tried to do is:

Filter('EntityB', Condition that makes it match with EntityA).EntityC.data
using this kind of formula im getting blank tables, but the strange thing is that the number of blank rows returned equals the number of records that it should get.

Is there any way to achieve this?

 

Thanks!

 

Ignacio.

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @Ignacio_S

Do you want to get records that depend on "Entity A" from "Entity C"?

Firstly, let me explain why this code doesn’t work.

The result of “Filter('EntityB', Condition that makes it match with EntityA)” is a table. But what we need is a collection of "Entity C" associated with each record in this table. So we need to perform the operation of referencing "Entity C" separately for each record. So it is recommended that you use the ForAll function.

Finally, I’ve made a test for your reference:

My data source:

EntityAS:

  • NameText/ Primary Field

EntityBS:

  • NameText/ Primary Field
  • EntityALookup
  • EntityCLookup

EntityCS:

  • NameText/ Primary Field

EntityAS 1:N EntityBS N:1 EntityCS

Step1:add a combo box control:

Item:

 

EntityAS

 

OnChange:                      

 

ClearCollect(Var,ForAll(
    Filter(
        EntityBS,
        Name in ComboBox1.Selected.EntityBS.Name
    ),
    EntityC
).Name)     
/* The records of the related "EntityCS" of all records that meet the conditions in "EntityBS".Because the whole table is traversed, there may be duplicate records at present. In order to remove the duplicate records, we must filter "EntityCS" again*/

 

Because my data source is used for temporary testing, there are many duplicate fields name. In order to eliminate ambiguity, I set up a custom set here to store the filtered results. Considering that you may have a similar situation, I added this step to ensure that this solution is feasible.

Step2:add a gallery control and set its item property to:

 

Filter(EntityCS,Name in Var.Name)

 

 

The gallery will display the records in "EntityCS" related to the records in "EntityAS" I selected

Best Regards,

Bof

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

Hi @Ignacio_S

Do you want to get records that depend on "Entity A" from "Entity C"?

Firstly, let me explain why this code doesn’t work.

The result of “Filter('EntityB', Condition that makes it match with EntityA)” is a table. But what we need is a collection of "Entity C" associated with each record in this table. So we need to perform the operation of referencing "Entity C" separately for each record. So it is recommended that you use the ForAll function.

Finally, I’ve made a test for your reference:

My data source:

EntityAS:

  • NameText/ Primary Field

EntityBS:

  • NameText/ Primary Field
  • EntityALookup
  • EntityCLookup

EntityCS:

  • NameText/ Primary Field

EntityAS 1:N EntityBS N:1 EntityCS

Step1:add a combo box control:

Item:

 

EntityAS

 

OnChange:                      

 

ClearCollect(Var,ForAll(
    Filter(
        EntityBS,
        Name in ComboBox1.Selected.EntityBS.Name
    ),
    EntityC
).Name)     
/* The records of the related "EntityCS" of all records that meet the conditions in "EntityBS".Because the whole table is traversed, there may be duplicate records at present. In order to remove the duplicate records, we must filter "EntityCS" again*/

 

Because my data source is used for temporary testing, there are many duplicate fields name. In order to eliminate ambiguity, I set up a custom set here to store the filtered results. Considering that you may have a similar situation, I added this step to ensure that this solution is feasible.

Step2:add a gallery control and set its item property to:

 

Filter(EntityCS,Name in Var.Name)

 

 

The gallery will display the records in "EntityCS" related to the records in "EntityAS" I selected

Best Regards,

Bof

View solution in original post

Hi,

Thanks for taking the time @v-bofeng-msft, it works. I've been stucked this past few days with this issue.

Thanks again,

Cheers!

 

Ignacio.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,115)