Valid If for Calendar Availability?

[Jason_Stetler]

Hello,

I am having a difficult time trying to structure a Valid If statement that will help me out with limiting which users can be selected for events on a calendar that references a second calendar with the same potential user’s unavailability (posted as events). My goal is to make certain users on a list error as unavailable if their schedule is blocked off on the referenced crew calendar.

Here is what I am working with right now, and I feel close, but am just not sure what to do:
The following is what I have written in the Valid If section of the column [PIC] on a google calendar table where:
StreamFleet Crew = The referenced calendar that holds the crew schedules
Start and End are the date/times of the event postings
PIC = a crew member selection when creating a calendar event (Enum - Ref - Crew table)
Creator = references the creator email of events on the StreamFleet Crew schedule calendar

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

([_THISROW].[PIC] <> [Creator])

)
)

-LIST([_THISROW])
) ,

[_THISROW].[Start] >= TODAY()
)
AND(
ISBLANK(
FILTER(“StreamFleet Crew”, AND(
([_THISROW].[End] <= [End]),
([_THISROW].[End]>= [Start]),

([_THISROW].[PIC] <> [Creator])
)
)

-LIST([_THISROW])
) ,

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

Any and all help is very much appreciated!

[Steve]

Here’s your expression(s?) reformatted for my clarity:

AND(
  ISBLANK(
    FILTER(
      "StreamFleet Crew",
      AND(
        ([_THISROW].[Start] >= [Start]),
        ([_THISROW].[Start] <= [End]),
        ([_THISROW].[PIC] <> [Creator])
      )
    )
    - LIST([_THISROW])
  ),
  ([_THISROW].[Start] >= TODAY())
)

AND(
  ISBLANK(
    FILTER(
      "StreamFleet Crew",
      AND(
        ([_THISROW].[End] <= [End]),
        ([_THISROW].[End] >= [Start]),
        ([_THISROW].[PIC] <> [Creator])
      )
    )
    - LIST([_THISROW])
  ),
  ([_THISROW].[End] >= TODAY())
)

It appears you have two adjacent-but-independent AND() expressions. If the expression is in fact written exactly as you provided in the original post, the second AND() expression will be silently ignored entirely and have no effect on the condition. If you want both expressions to be checked, you need to tie them together by wrapping both together in an AND() or OR() expression (as appropriate).

[Jason_Stetler]

Thanks for the response, Steve. I’ve updated like you said so that my second expression isn’t being ignored. Thanks!

However, I am still not getting the results I am after. Right now it is throwing an error for unavailable on every single user…I’d like it to only error for users who are scheduled already on the referenced crew calendar. Any thoughts?

AND(
  ISBLANK(
    FILTER(
      "StreamFleet Crew",
      AND(
        ([_THISROW].[Start] >= [Start]),
        ([_THISROW].[Start] <= [End]),
        ([_THISROW].[PIC] <> [Creator])
      )
    )
    - LIST([_THISROW])
  ),
  ([_THISROW].[Start] >= TODAY()),

AND(
  ISBLANK(
    FILTER(
      "StreamFleet Crew",
      AND(
        ([_THISROW].[End] <= [End]),
        ([_THISROW].[End] >= [Start]),
        ([_THISROW].[PIC] <> [Creator])
      )
    )
    - LIST([_THISROW])
  ),
  ([_THISROW].[End] >= TODAY())
))