Solved: Calendar valid if

Damian_Turcios · 03-20-2020 04:05 PM

hi, i have an app where i fill the date and hour of an apointment in a form and in a calendar view i can see the the clients in the dates they where registered but i cant see the calendar view when im filling the form couse it doesnt support it, is there a way that i can create a valid if so if a date and hour is already assigned it doesnt let me save on top of it? or a way that the sistem shows me the available hours for appointments in that date?

Steve

Yep.

ISBLANK(
  FILTER(
    "CalendarTable"
    AND(
      ([_THISROW].[Date] = [Date]),
      ([_THISROW].[Time] = [Time])
    )
  )
  - LIST([_THISROW])
)

FILTER(...) gets list of all appointments that already exist with the form’s date and time. FILTER(...) - LIST([_THISROW]) removes the row in the form from that list, making it a list of other appointments at the same date and time. ISBLANK(...) asks, are there no other appointments at the same date and time? If yes (there are no others), the form’s date and time is available.

Yep.

(
  LIST(
    "07:00", "08:00", "09:00", "10:00", "11:00",
    "12:00", "13:00", "14:00", "15:00", "16:00"
  )
  -
  SELECT(
    CalendarTable[Hour],
    ([_THISROW].[Date] = [Date])
  )
)

LIST(...) is the list of all possible appointment times in a day. SELECT(CalendarTable[Hour], ...) gets the list times for all currently-scheduled appointments on the given date. LIST(...) - SELECT(...) removes the scheduled appointments from the possible appointments slots, leaving only unused appointment slots.

See also:

Damian_Turcios

Thanks

Rui

Hi Steve,

but where would you write those formulas?

Thanks

Fabinonet

Greetings everyone
I've tried to replicate the dating formula but it doesn't work perfectly
In practice, more users have the possibility of booking periods during the day with a difference of 30 minutes
example in suggested values
LIST(
“08:30:00”,
“09:00:00”,
“09:30:00”,
“10:00:00”,
“10:30:00”,
“11:00:00”,
“11:30:00”,
“12:00:00”,
“12:30:00”,
"13:00:00",
"13:30:00",
“14:00:00”,
“14:30:00”,
“15:00:00”,
“15:30:00”,
“16:00:00”,
“16:30:00”,
“17:00:00”,
“17:00:00”,
“18:00:00”,
)
In Valid if for START time and END time I have entered

AND([START] < [END],
COUNT(FILTER(“PRENOTA”,
AND(
[EMAIL_TECNICO] = USEREMAIL(),
[DATA] = [_THISROW].[DATA],
AND(
([START] >= [_THISROW].[START]),
([START] <= [_THISROW].[END])
))
)
) = 0
)

I cannot understand and cannot resolve the error that 
IT WON'T LET ME INSERT 10:00:00 - 10:30:00
but in the period is free!!

these are the data entered
06/10/2023 08:30:00 10:00:00
06/10/2023 10:30:00 11:00:00
06/10/2023 14:00:00 16:30:00
--------------------------------------------
06/10/2023 10:00:00 10:30:00 (NOT ENTERED) ERROR

why ?
there is another solution and/or method ?

Mike_T

I thought that this might have solved a problem I am having too but sadly, I don’t think I’m inputting it correctly. My situation is slightly different as a Trainer can be booked for multiple days in a row. So somehow I’m trying to invalidate any start date that is on a previously booked start date or during any multiday period between start date and end date. For the same Trainer. And obviously can’t book a trainer in the past. I modified Steve’s suggestion above with the below. I put this in the ValidIf formula in the Start Date column of my Book a Trainer Form. Thoughts?

ISBLANK(
FILTER(“TrainerSchedule”, AND(
([_THISROW].[Start Date] = [Start Date]),
([_THISROW].[Start Date] > [End Date]),
([_THISROW].[Start Date] >= Today()),
([_THISROW].[Trainer_Name] = [Trainer_Name])

)
)

-LIST([_THISROW])
)

Suvrutt_Gurjar

May I request to try something like below as Valid_if expression

AND(
ISBLANK(
FILTER(“TrainerSchedule”, AND(
([_THISROW].[Start Date] >= [Start Date]),
([_THISROW].[Start Date]<= [End Date]),

([_THISROW].[Trainer_Name] = [Trainer_Name])

)
)

-LIST([_THISROW])
) ,

[_THISROW].[Start Date] >= TODAY()
)

Mike_T

Suvrutt,
I woke up at 5am with this formula rolling around in my head and I finally realized my error and came up with the following:

ISBLANK(
FILTER(“TrainerSchedule”, OR(([_THISROW].[Start Date] < Today()),AND(
AND(([_THISROW].[Start Date] >= [Start Date]),
([_THISROW].[Start Date] < [End Date])),
([_THISROW].[Trainer_Name] = [Trainer_Name]))

)
)

LIST([_THISROW])
)

It seems to work with what I throw at it. Do you see any issues vs your suggestion?

Thanks your reply! This really is a great community of support!

Suvrutt_Gurjar

You are welcome @Mike_T

Excellent to know you have got it working.

Yes, you are correct. AppSheet’s is a great community!

Mike_T

Suvrutt,

Just when I thought I had it licked… I’m trying to validate the End Date so the user cannot overlap the next Start Date.

ISBLANK(
FILTER(“TrainerSchedule”, AND(
([Start Date]>[_THISROW].[Start Date]),
([_THISROW].[Trainer_Name] = [Trainer_Name]))

)

-LIST([_THISROW])
)

I thought I had it with the above but this filters out all of the dates > my start date.

Suvrutt_Gurjar

I believe if you are trying to apply similar logic to the [End Date], then the [End Date] valid_if may be something like below

AND(
ISBLANK(
FILTER(“TrainerSchedule”, AND(
([_THISROW].[End Date] <= [End Date]),
([_THISROW].[End Date]>= [Start Date]),

([_THISROW].[Trainer_Name] = [Trainer_Name])

)
)

-LIST([_THISROW])
) ,

[_THISROW].[End Date] >= TODAY()
)

Mike_T

These both work very well! Thanks again!