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

[djallen]

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

[GarethPrisk · Accepted solution]

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.

[djallen]

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

[GarethPrisk]

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
• 

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
• 

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!

[djallen]

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