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.
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.
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:
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?
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.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.
We are excited for the next Super User season.
FIll out a quick form to claim your community user group member badge today!
Features releasing from October 2020 through March 2021