Calendar Next Available Time Slot

Hey guys! I have been searching the forums and could not find a solution to the problem below. Any help would be greatly appreciated.

App: Calendar View with “events” every half hour
Data: Jobs with [Schedule Date], [Schedule Start Time], and [Schedule End Time]
Issue/Request: When adding a new event, I’m trying to figure out how to auto fill the start time to the next available half hour time slot for the specified [Schedule Date] column. The [Schedule End Time] is simple enough with a formula to subtract 30 minutes. I have checked Calendar slot checking which checks for previous scheduled dates, not times.

So if I add a new event, I select the date required, it checks all events on the specified date, and chooses the next available time slot.

@BrianM
I may advise this:
1.) Create a VC of datetime type with a simple concatenation of your [Schedule Date] and [Schedule Start Time]
2.) Create a VC i.e. LatestEvent with MAXROW() expression referring to the 1st VC you have created above

MAXROW(
	"LatestEvent",
    "Your_Concatenated_DateTime_VC_Name",
	(
		[Schedule Date] = [_THISROW].[Schedule Date]
	)
)

This will return a ref value for each specific [Schedule Date]'s latest record where you can de-reference
3.) You can now set an initial value for your [Schedule Start Time]

IFS(
	ISBLANK([LatestEvent]),TIME("09:00"), //if this is the first record, return a default start time
	TRUE, [LatestEvent].[Schedule End Time] //return the latest event's end time as a start time
)
1 Like

Thank you for the quick response. Here is what I had previously tried which seemed to work sometimes… not sure if it was just grabbing the previous rows time from the entire table and not the previous rows time within the specified schedule date.

Virtual Column Name: [Previous Row] Type: Ref

MAXROW(
“Rough Jobs”,
“_ROWNUMBER”,
AND(
([Schedule Date] = [_THISROW].[Schedule Date]),
([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])

)
)

Schedule Start Time Initial Value

IFS(
AND(
ISNOTBLANK([Previous Row]),
[Job Phase]=Rough),
[Previous Row].[Schedule Start Time] + “000:30:00”,
AND(
ISBLANK([Previous Row]),
[Job Phase]=Rough),
“00:00:00”
)

I will try your suggestion! Thank you!

You’re welcome but your approach is already pretty much same as mine actually

Sorry for the late response, I am just now testing your suggestion.

I created both VC columns, no issues there. I added the

IFS(
	ISBLANK([LatestEvent]),TIME("12:00 AM"),
	TRUE, [LatestEvent].[Schedule End Time] 
)

to Initial Value of the Schedule Start Time column. Clicked on “Test” and everything seems to come back perfect. After clicking “Save” the app fails to load with the error in the attached screenshot.

Which seems to be happening as App sync is very slow: up to 60 seconds. Is ... suggests.