cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JonRead
Level: Power Up

Using Formulas in SharePoint Online PowerApp customised form

Hi Community,

 

I have been using InfoPath for forms within SharePoint over a number of years and one of the things that I have always managed to do is have a form field input based on a calculation from 2 other fields on the form. I am totally new to PowerApps.

 

What I do not seem to be able to do is create that within PowerApps so it's either not possible or I am not doing it correctly.

 

I have 2 date fields and what I want to capture is the number of years between the 2 dates but 1 of the dates is not always present so it needs to be amendable to enable a manual (hence not using SharePoint to calculcate the output as that would then not show in the form and therefore not be amendable as required).

 

The output would need to be the number of years as an Integer so I was looking at something along the lines of If(FirstDate="",0,INT((SecondDate - FirstDate)/365)) as the default value for the Years field. I am guessing it would need to be included after change to the SecondDate field or if not then it would need to be able to change based on the inputs in those 2 fields and not just at the time the form loads.

 

Thanks in advance for any assistance that anyone can be provide.

3 REPLIES 3
JonRead
Level: Power Up

Re: Using Formulas in SharePoint Online PowerApp customised form

I have managed to get a formula that works and will provide what I need but cannot get it to update once the form is opened up and details entered into the date fields.

RoundDown(DateDiff('First Date','Second Date',Days)/365,0) Within the SecondDate field I did put a formula into the OnChange property as 'Calc Field'=RoundDown(DateDiff('First Date','Second Date',Days)/365,0) which is not then updating the details in the Calc Field on the form.

I also put the RoundDown(DateDiff('First Date','Second Date',Days)/365,0) formula within the OnSelect property of Calc Field which again is not doing the calculation when you go into the field.

The only time it does the calculation is within the Default property but then that does not amend either when the dates are entered - just when the form is created so gets a 0 value.

As such, I am guessing that I am missing something important in terms of the way PowerApps works within a form to do these calculations and apply them to field from SharePoint list.

Community Support Team
Community Support Team

Re: Using Formulas in SharePoint Online PowerApp customised form

Hi @JonRead ,

 

By "Calc Field", did you mean that you have set the calculation formula for this field inside PowerApps, but not that it is a calculated column type in SharePoint list, right?

 

I need to make sure about this because if it's a calculated column in SharePoint, then it is supported as Read-only for PowerApps. See more information here:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/connections/connection-sharepoint-onlin...

 

If it isn't a calculated column, then what is this column's column type in SharePoint? And are both 'First Date' and 'Second Date' date only column?

 

Regards,

Mona

 

 

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
JonRead
Level: Power Up

Re: Using Formulas in SharePoint Online PowerApp customised form

Thanks @v-monli-msft ,

"Calc Field" is just the name I have used to represent an input field (possibly should have just used the word Age or something else to ensure no confusion).

It is not calculated in SharePoint for the exact reason you mention below as it would then not be updateable in PowerApps form as required - where there's no First Date and the input for "Calc Field" is available from other details provided.

The SharePoint field is a Number field and the "First Date" and "Second Date" are date fields in SharePoint and can only accept date inputs.

 

Jon

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,811)