Power Apps Custom SharePoint List Form - Calculating Values from Multiple Fields

[tas_ranson]

I have a SharePoint list with user related information on it.  I have created a Power Apps Custom form connected to the list.

I want to make some calculations of user name length and create some warnings based on a persons proper names, preferred names, and whether they want to use preferred names in eMail etc.

The form works fine to create and edit records and save these changes.

I am trying to add some on-screen calculations of user name based on the users Proper Given Name, Proper Family Name, Preferred Given Name, Preferred Family Name, and a check box "Use Preferred Names in Email"  These are all SharePoint list fields.

I have tried two main approaches: Helper Fields on the Form (not connected to the SharePoint list), and Context Variables.  Neither seem to work.  

Helper fields with formulae calculating the values of parts of the user name dont update when the user edits the values of the SharePoint fields on the form that the formula uses.  The helper fields are set accurately when the form opens, but do not responde to any changes on the form.  Is this because these fields are not bound to a List Column?   If I use helper fields that are actually in the list will this work?   I then have to hide these from the end users, so this seems like extra work that should not be necessary.

In the Context Variables approach I created OnChange forumulae in each of the contributing SharePoint fields updating the values (using the same forumule as for the Helper fields) of the Context variables.   I can not find where to set the values of these variable correctly when the form first opens.  But most problematic  is that the values of the Context Variables only update once, and only when the 'Use Preferred in Email' check box is checked.   I have tried putting the same formule in the OnSelected attributes of the data cards as well with no change in behaviour.   Is the OnChange event only going to fire once for any field on the form?  This does not seem right?  Surely any change to the value in a field on the form should fire the OnChange event, and the formulae below shoud update the value of the context variables, and these values should refresh in the fields that reference them?

Here are some details for the Context Forumulae approach.  Similar Formulae were used for the Helper Fields approach.

OnChange: ('Preferred Given Name' SP Field DataCardValue)
UpdateContext({cvunGiven: Substitute(Substitute(Trim(If(ThisItem.'Use Preferred in Email' && Len(ThisItem.'Preferred Given Name') > 0,ThisItem.'Preferred Given Name',ThisItem.'Given Name'))," ",""),"'","")})

OnChange: ('Preferred Family Name' SP Field DataCardValue)
UpdateContext({cvunFamily:Substitute(Substitute(Trim(If(ThisItem.'Use Preferred in Email' && Len(ThisItem.'Preferred Family Name')>0,ThisItem.'Preferred Family Name',ThisItem.'Family Name'))," ",""),"'","")})

OnChange: ('Use Preferred in Email' SP Field DataCardValue)
UpdateContext({cvunFamily:Substitute(Substitute(Trim(If(ThisItem.'Use Preferred in Email' && Len(ThisItem.'Preferred Family Name')>0,ThisItem.'Preferred Family Name',ThisItem.'Family Name'))," ",""),"'",""),cvunGiven: Substitute(Substitute(Trim(If(ThisItem.'Use Preferred in Email' && Len(ThisItem.'Preferred Given Name') > 0,ThisItem.'Preferred Given Name',ThisItem.'Given Name'))," ",""),"'","")})

Helper Field: unGiven
Text: cvunGiven

Helper Field: unFamily
Text: cvunFamily

[ShaheerAhmad · Accepted solution]

Based on your description, it seems like you're experiencing difficulties in updating and recalculating the helper fields or context variables in your Power Apps custom SharePoint list form. I'll provide some insights and suggestions that might help you resolve these issues.

1. Helper Fields Approach:

   • If the helper fields are not bound to a list column, they won't update automatically when the form values change. You can consider using calculated columns in the SharePoint list instead. These calculated columns can contain the necessary formulas to calculate the values based on the other fields.
   • If you decide to use helper fields in the SharePoint list, you can hide them from end users by adjusting the field properties in SharePoint. This way, they will still be accessible in the form and update correctly.

2. Context Variables Approach:

   • The OnChange event should fire whenever the value of a field on the form changes. If you're experiencing issues with the OnChange event not firing, there might be an error in the formula or a configuration issue.
   • To set the initial values of the context variables when the form opens, you can use the Form's OnVisible property. Set the formula in the OnVisible property of the form to update the context variables with the initial values based on the SharePoint list fields.
   • Make sure that the formulas in the OnChange events of each field are correct and reference the correct fields. Check for any typos, missing or extra parentheses, or incorrect logical conditions that might cause the formulas to not work as intended.

Here's an example of how you can set the initial values of context variables when the form opens:

Form.OnVisible:

UpdateContext({
  cvunGiven: Substitute(Substitute(Trim(If(ThisItem.'Use Preferred in Email' && Len(ThisItem.'Preferred Given Name') > 0, ThisItem.'Preferred Given Name', ThisItem.'Given Name'))," ",""),"'",""),
  cvunFamily: Substitute(Substitute(Trim(If(ThisItem.'Use Preferred in Email' && Len(ThisItem.'Preferred Family Name') > 0, ThisItem.'Preferred Family Name', ThisItem.'Family Name'))," ",""),"'","")
})

Please ensure that the formula references the correct fields and matches your specific requirements.

Remember to save and publish your app after making any changes to ensure the latest version is deployed.

I hope these suggestions help you resolve the issues you're facing with your Power Apps custom SharePoint list form.

[tas_ranson · Accepted solution]

Thank you these are helpful suggestions.   I finally figured out the main issue with both approaches, in that the formulae were all referencing the ThisItem.[FieldName] values which are the underlying list values.  Clearly these dont change during the form edit, only when the form data is saved back to the list.   When I changed the formulae to refernce the appropriate form data fields, things started to work as I wanted them too.   I have finished up using more complex formulae and placing these in the data controls for existing list fields that were being populated by a flow after the form was saved.  This has made the User experience significantly better, and I can keep all the formulae in one place .