cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jackw
Frequent Visitor

Set Lookup Value for Child Based on Parent's Parent Relationship

Hey Everyone,
 
I am wondering what the best way is to set the value of a look up field for a CDS entity. This lookup field needs to be set to the entity's grandparent. The structure is, entity-1 is the parent to entity-2. entity-1 can have many entity-2s. Entity-2 can only have one Entity-1. Entity-2 is the parent for entity-3. Entity-2 can have many entity-3s. Entity-3 can only have one Entity-2. Currently the relationship for entity-1 to entity-3 is a lookup field on entity-3 that looks up entity-1. This lookup needs to lookup to the same entity-1 that is the parent for entity-2 which is the parent to entity-3. I do not know a good way to have this set automatically / by default. I need this because I need a subgrid that shows all of the entity-3s that are owned by entity-1. Entity-1 owns both entity-2 and entity-3, but entity-2 also owns and overall affects entity-3.
What would be the best way to set this relationship / lookup field?
Thanks, 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @Jackw ,

Do you want to set the LookUp field value within your Entity3 to the same Entity1 record which is the same parent to Entity 2 and Entity3?

 

I have made a test on my side, please consider take a try with the following workaround:

You could generate a canvas app based on your Entity3, then add your Entity1 and Entity2 as data sources into this app.

Set the OnStart property of App to following:

ClearCollect(
             Entity3ToEntity1Collection,
             ForAll(
                   Entity3,
                   Entity3ToEntity1LookUpField.'Unique Identifier Column'
             )
);
ClearCollect(
            Entity2ToEntity1Collection,
            ForAll(
                   Entity2,
                   Entity2ToEntity1LookUpField.'Unique Identifier Column'
            )
);
// SameParentCollection is used to store the same parent identifier GUID value from Entity1 for Entity2 and Entity3
Clear(SameParentCollection);
ForAll(
       Entity2ToEntity1Collection,
       If(
          Value in Entity3ToEntity1Collection.Value,
          Collect(SameParentCollection, Value)
       )
)

Then you could go to the Edit form, unlock the Entity3ToEntity1LookUpField data card, set the Items property of the ComboBox to following:

Filter(Choices([@Entity3].Entity3ToEntity1LookUpField), 'Unique Identifier Column' in SameParentCollection)

please re-load your app (fire the OnStart property of App), then the same parent record options for Entity2 and Entity3 from Entity1 would be populated into this ComboBox.

 

If you want to achieve your needs in Model-Driven app, I afraid that there is no way to achieve your needs currently. Currently, there is no way to configure one relationship based on another relationship in CDS.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-xida-msft
Community Support
Community Support

Hi @Jackw ,

Do you want to set the LookUp field value within your Entity3 to the same Entity1 record which is the same parent to Entity 2 and Entity3?

 

I have made a test on my side, please consider take a try with the following workaround:

You could generate a canvas app based on your Entity3, then add your Entity1 and Entity2 as data sources into this app.

Set the OnStart property of App to following:

ClearCollect(
             Entity3ToEntity1Collection,
             ForAll(
                   Entity3,
                   Entity3ToEntity1LookUpField.'Unique Identifier Column'
             )
);
ClearCollect(
            Entity2ToEntity1Collection,
            ForAll(
                   Entity2,
                   Entity2ToEntity1LookUpField.'Unique Identifier Column'
            )
);
// SameParentCollection is used to store the same parent identifier GUID value from Entity1 for Entity2 and Entity3
Clear(SameParentCollection);
ForAll(
       Entity2ToEntity1Collection,
       If(
          Value in Entity3ToEntity1Collection.Value,
          Collect(SameParentCollection, Value)
       )
)

Then you could go to the Edit form, unlock the Entity3ToEntity1LookUpField data card, set the Items property of the ComboBox to following:

Filter(Choices([@Entity3].Entity3ToEntity1LookUpField), 'Unique Identifier Column' in SameParentCollection)

please re-load your app (fire the OnStart property of App), then the same parent record options for Entity2 and Entity3 from Entity1 would be populated into this ComboBox.

 

If you want to achieve your needs in Model-Driven app, I afraid that there is no way to achieve your needs currently. Currently, there is no way to configure one relationship based on another relationship in CDS.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

ElyasAhmed
Frequent Visitor

Is there any way to make this happen in Model apps? Any sort of workaround would be helpful.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

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

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

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