cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Retrieve Field Value from Parent Entity for Use in Calculating Child Field Value

Hi,

 

I'm new to Power Apps and am running up against some challenges.  Coming from a programming background with a lot of work using Entity Framework has, I think, polluted my thinking in how to work with Entities in CDS/Power Apps.

 

Is it possible for a child entity to retrieve a field value from a parent entity to perform a calculation in a model-driven app?  Here's a scenario:

  1. A Quote entity contains a field "Effective Date"
  2. An Insured entity contains a field "Premium"
  3. There is a one to many relationship between Quote and Insured entities.
  4. When adding/editing an Insured to a Quote via a Quick Create or Main form I need to calculate a value for the Insured's "Premium".
  5. The formula to calculate the Insured's "Premium" value requires the variable "Effective Date" from the parent Quote entity.
  6. The Premium value should display immediately in the client when editing/adding and Insured.  A user should not be required to save before the value calculates and displays in the form.

I am at a loss as to how to calculate the value for an Insured's Premium.  I like the interface of a Quick Create form in the model driven app but I'm not sure this is the correct approach.  I don't see how one could lookup the Quote's "Effective Date" value via a client script as the script's scope is limited to the the form fields.  I don't want my users to have to re-enter an effective date for each insured just so the calculation can work properly. 

 

I have several scenarios where a calculation for a field in a child entity requires some variable defined at the parent entity's level.  Perhaps using main/quick create forms in the model-driven app isn't the right approach?  Maybe embedding a canvas app within the model driven app is a better alternative?  In a canvas app could I add child entities to a Quote and perform the various calculations that require some of the parent field values?  

 

Thanks in advance for any help.  I'm sure I'll be back with more questions as I get deeper into this new-to-me platform.

 

Thanks,

Dan

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Retrieve Field Value from Parent Entity for Use in Calculating Child Field Value

A relatively simple approach is to use a Business Rule to calculate the value.

  • This can run on Quick Create forms
  • This is easy to configure via the Business Rule editor
  • This requires the contextual data needed for the Business Rule to execute (it cannot inherently use parent values)

To accomplish this, you would:

  • Create an Effective Date field (maybe Quote Effective Date, to disambiguate) on your child entity (Insured)
  • Update the 1:N relationship, to map the parent field to the new child field
    • Parent (Quote) Field (Effective Date) to
    • Child (Insured) Field (Quote Effective Date)
  • Add the new field to the child quick create form
    • Optionally: Hide this field from view, but it must be on form for Business Rule to reference it
  • Create a Business Rule which calculates the Premium using this Quote Effective Date value
    • Set it up as All Forms
    • Determine appropriate conditional logic (such as If Quote contains Data and Premium does not contain data)
    • Set the field value of the Premium
    • Notes
      • I didn't see any details as to how this is calculated, Business Rule may not be robust enough
      • If it is, however, then this will run in real-time for the User

 

Your thinking isn't polluted; PowerApps just has some nuances. You are correct that a calculated field will not populate data until after the record is created/updated/retrieved, since it determining its value at run-time.

View solution in original post

4 REPLIES 4
Highlighted
Solution Sage
Solution Sage

Re: Retrieve Field Value from Parent Entity for Use in Calculating Child Field Value

A relatively simple approach is to use a Business Rule to calculate the value.

  • This can run on Quick Create forms
  • This is easy to configure via the Business Rule editor
  • This requires the contextual data needed for the Business Rule to execute (it cannot inherently use parent values)

To accomplish this, you would:

  • Create an Effective Date field (maybe Quote Effective Date, to disambiguate) on your child entity (Insured)
  • Update the 1:N relationship, to map the parent field to the new child field
    • Parent (Quote) Field (Effective Date) to
    • Child (Insured) Field (Quote Effective Date)
  • Add the new field to the child quick create form
    • Optionally: Hide this field from view, but it must be on form for Business Rule to reference it
  • Create a Business Rule which calculates the Premium using this Quote Effective Date value
    • Set it up as All Forms
    • Determine appropriate conditional logic (such as If Quote contains Data and Premium does not contain data)
    • Set the field value of the Premium
    • Notes
      • I didn't see any details as to how this is calculated, Business Rule may not be robust enough
      • If it is, however, then this will run in real-time for the User

 

Your thinking isn't polluted; PowerApps just has some nuances. You are correct that a calculated field will not populate data until after the record is created/updated/retrieved, since it determining its value at run-time.

View solution in original post

Highlighted
Helper I
Helper I

Re: Retrieve Field Value from Parent Entity for Use in Calculating Child Field Value

Thanks for the help!  

 

This was the key part I was missing:

 

Update the 1:N relationship, to map the parent field to the new child field

  • Parent (Quote) Field (Effective Date) to
  • Child (Insured) Field (Quote Effective Date)

 

After finding where I could manage the mapping this is starting to make a lot more sense.

 

I'm still uncertain a Business Rule is the correct approach.  Unless I'm mistaken (quite likely), a Business Rule doesn't support formulas.  For example, it appears I can only add or subtract days to a date via a Business Rule.  I would need to find the difference between two dates and use that difference as a variable in a longer formula. 

 

For example, without posting the exact math, the Premium value would be determined by something like:

([Quote Effective Date] - [Some Date Entered for an Individual Insured on Quick Create Form] ) * [Some Factor From an Option Set on the QC Form] * [Another Parent Quote Field Value] + [etc, etc...]

 

My plan was to use client scripts to perform the math when values in the Quick Create form change since I couldn't see where a Business Rule could satisfy my requirements.  Unless I'm again missing something in the Business Rule designer.

 

Thanks again!

Dan

Highlighted
Solution Sage
Solution Sage

Re: Retrieve Field Value from Parent Entity for Use in Calculating Child Field Value

Glad that helped!

 

Business Rules have some capabilities to perform parts of the logic you described. However, they will start to show their limitations when multiple and/or complicated logic begins to factor in.

 

For example, you can achieve a relative condition with dates:

  • See below (not a valid example, but conceptually)
  • There are formulas here to determine a relative value, based on another field and/or value
  • BusinessRuleEditor_DateCondition.png

For example, you can achieve a relative multiplier with numeric/currency fields:

  • See below (not the same example, but conceptual)
  • There are formulas available to apply a multiplier (or other operator) with a fixed/field value
  • BusinessRuleEditor_NumericAction.png

 

That being said. You could potentially account for all of your scenarios with conditional branches, but the logic may too dynamic, or too complicated to accomplish. There are some inherent advantages to this approach (if it works), since the Business Rule will execute on all forms, and the logic even extends to other facets of the Power Platform. Happy coding!

Highlighted
Helper I
Helper I

Re: Retrieve Field Value from Parent Entity for Use in Calculating Child Field Value

Very good point regarding the Business Rules.  I will continue to explore that route some more.  The logic isn't really that complicated to be honest.  It's simple algebra and a few lookups.  I mean it starts with: if the difference between dates is A than use B factor.  If the difference between dates is C than use D factor, etc.  So, I might be able to rethink my approach and accomplish this via a Business Rule.  

 

Thanks again!

 

Dan

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,016)