I have a gallery that is filled from a collection. One of the fields in the gallery is a calculated date field (the difference in hours between two dates). The two date fields are are formed by dropdowns of the hour and minutes. When the gallery is first loaded and displayed, the date fields populate correctly, but the calculated field of the difference in hours is totally wrong, until you update one of the date drop downs, then it is correct. Is there a way to get the calculated field to display correctly upon initial load of the collection/gallery?
The default for the calculated difference field is:
Text(DateDiff(DateTimeValue('DateIn_4'.Text),DateTimeValue(' DateOut_4'.Text),Minutes)/60+' Addl Time_4'.Selected.Value, "[$-en-US]#.00")
Any help would be great. I can't understand why it is doing this. Thanks!
What (and where) is 'DateIn_4' and ' DateOut_4' as well as ' Addl Time_4'?
They are fields defined in the gallery.
DateIn_4 is a label in the Gallery that takes the info from dropdowns in the gallery and puts them together to form a valid date/time:
DateTimeValue(Timesheet_Date.SelectedDate & " " & 'TimeInHour_4'.Selected.Value & ":" & wMinutes & " " & 'TimeInAMPM_4'.Selected.Value)
DateOut_4 is a similar but a bit more involved, as it also puts together a Date/Time value, based on what they user selects in the dropdowns in the fields in the Gallery and then determines if that time they select is earlier that the time in the DateIn_4 field (as the user only selects a time, not an actuial date. So, the idea is that if the time in the DateOut is before the DateIn time, it must go into the next day, so I then add a day to the date part of the date/time for the DateOut_4. I do this by having two other fields for the Date_OOut4 to use.
DateOut_4 Text property is:
If(TimeValue('DateOutCk_4a'.Text) < TimeValue('DateIn_4'.Text),
DateAdd(DateTimeValue('DateOutCk_4a'.Text), 1, Days)
DateOutCk_4a Text property is basically below. There is more code that handles rounding to the nearest quarter hour (which is how wMinutes, beleow) is determined), but I am leaving that part out to simplify, as this is the main code involved:
DateTimeValue(Timesheet_Date.SelectedDate & " " & 'TimeOutHour_4'.Selected.Value & ":" & wMinutes & " " & 'TimeOutAMPM_4'.Selected.Value)
Here's a screenshot of the gallery:
I hope this isn't too confusing.
What seems to be missing in this is the reliance on the underlying record columns. Are these dates represented somehow in your Items of the Gallery?
Not really, as I figured it would just generate the correct Hours based on the gallery fields.
But, since posting this, I have added a variable that gets set when coming into the record (initially showing the gallery) and set the variable to false. And if it is false, to pull from the underlining record (as you point out). If a user changes one of the time dropdowns, I have it setting the variable to true, and then the Hours field will do the datediff from the gallery fields instead of the underlining record. So, far, it appears to fix the issue when coming into an existing record. But when adding a new one, it sets back any changes made previously in the records displayed in the gallery already. Ugh. So close.
@RandyHayes I have changed my method and this seems to handle the issue. I am patching the Hours and the field that is getting changed that affects the Hours calculation back to the Collection. This seems to work well. Thanks for the help.
Very good! Sounds like you are on the right path now.
Learn how to create your own user groups today!
Check out the new Power Platform Community Connections gallery!
Congratulations, the new Super User Season 2 for 2021 has started!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.