cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GarryPope
Impactful Individual
Impactful Individual

Best way to compare values in different Date Picker columns

Hello Power Apps Community,

I hope you can help please.

I'm using Dataverse for Teams to build a timesheet app.

I have a Form with a number of Date and Time columns, "Monday Start", "Monday End", "Tuesday Start". "Tuesday End" etc.

 

I want to add some validation, so when the date and time in say the column "Friday Start" is greater than "Friday End" I want to notify the user and stop them saving the form with incorrect data.

 

Currently, as a test, on one of the columns, "Friday Start", on the "HourValue" part of the datacard I've added this formula:

If(
         crdFridayStart.Update > crdFridayEnd.Update,
         Notify(
                     "Day Start hour is greater than day End hour. Adjust your Start hour to be earlier than your End hour",
                     NotificationType.Error,
                     10000
                   ),
        false
)

This notifies the user but still allows them to save the record. This test, or course, is not what I really want.

 

GarryPope_0-1626004228744.png

 

 

I want the entire datacard of the "Friday Start" to be compared with "Friday End" and if "Friday Start" is greater than "Friday End", even by a minute, I want the notification to appear and to stop the user saving the record.

Also, of course, I want this for all the datacard pairs, so "Monday Start" vs "Monday End", "Tuesday Start" vs "Tuesday End" etc.

 

Any guidance would be greatly appreciated.

Thanks very much,

Garry

2 REPLIES 2
CNT
Community Champion
Community Champion

@GarryPope Place this code in the OnSelect of the Save icon (remember to change control names as required),

With({
	varMondayStart:DateTimeValue(crdMondayStartDate.SelectedDate & " " & crdMondayStartHour.SelectedText & " " & crdMondayStartMinute.SelectedText),
	varMondayEnd:DateTimeValue(crdMondayEndDate.SelectedDate & " " & crdMondayEndHour.SelectedText & " " & crdMondayEndMinute.SelectedText),
	varTuesdayStart:DateTimeValue(crdTuesdayStartDate.SelectedDate & " " & crdTuesdayStartHour.SelectedText & " " & crdTuesdayStartMinute.SelectedText),
	varTuesdayEnd:DateTimeValue(crdTuesdayEndDate.SelectedDate & " " & crdTuesdayEndHour.SelectedText & " " & crdTuesdayEndMinute.SelectedText)
	// Add variables for other days here
	},
	If(
		DateDiff(varMondayStart, varMondayEnd, Minutes)<0 ||
		DateDiff(varTuesdayStart, varTuesdayEnd, Minutes)<0 ||
		// Add comparisions for other days here
		, Notify("ERROR MESSAGE"),
		// Save the data
	)
)

 

GarryPope
Impactful Individual
Impactful Individual

Hello @CNT,

Thank you so much for your quick response, I really appreciate it. I don't doubt it's not working, but when I try it, I'm getting errors. I'm terrible with variables. Leave it with me, and once again, thanks for your reply.

Garry

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,212)