I have a table of Events, each Event has an Event Start Date. When entering a new Event, I need to check if the Event Start Date entered already exists.

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
Photo of Eamonn

Eamonn

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,024 Points 20k badge 2x thumb
This can be done with script. In fact you can probably configure the DatePicker associated with the [Event Start] date field to simply not allow you to pick dates on which an event is already scheduled. I have done this before to exclude weekends for the DatePicker. You use the beforeShowDay option to configure this feature:

http://api.jqueryui.com/datepicker/#option-beforeShowDay


Unfortunately I can't show you a demo as I have other work to do and I already reached my daily limit for playing in the forum. Feel free to contact me via the information in the profile if you want this feature implemented.

UPDATE2

Well my cartoon watching session was canceled because someone is hogging the TV so I whipped out this example. Here I configure the DatePicker to exclude this fixed set of dates: 


["2015-07-23", "2015-07-25", "2015-07-27", "2015-07-29", "2015-07-31"];

See the screenshot attached and look for the grayed out cells.
Exclude Dates On Datepicker
https://haversineconsulting.quickbase.com/db/bj47ftvp2?a=nwr

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=428

Notes:

(1) QuickBase uses the jQuery UI DatePicker widget for their dates

(2) You could probably type in a date and bypass the date exclusion - easy to fix

(3) I used a fixed set of dates for simplicity of the demo but it would be a simple matter to query for the future event dates and exclude them

(4) If you allow multiple events per day (say a morning, afternoon or midnight trist) you could modify the logic to inform the user that there may be a conflict if ...

(5) see also this weekend date excluder:

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=257


Oops it looks like this excludes the first day of each week - not weekends.
Photo of Eamonn

Eamonn

  • 0 Points
Thanks for your advice - really useful.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
There is an easier way natively.

Make a checkbox field called [Allow Duplicate Dates]

Make a formula text field called [Duplicate Event for this Date] with a formula of


IF([allow duplicate dates]=false,[Event Date])

Make that formula field to be unique.

What will happen is that when you go to save the record, it will abort the save with a big yellow message saying that there is already an Event for that Date.  Then to allow the save to continue anyways, check the checkbox and try again.

If you want to easily see the conflicting events on the same date, then Create a new Report Link field for the events table, and base it on the Event Date on the "left" side of the configuration, and then navigate and set up the same field on the right side. Then put the e=vents report right on the event record.