Switch broke my formula

[JR-BejeweledOne]

I have a working formula that I changed to use a switch.   Now it only evaluates the first condition in either If statement.    It was working correctly converting it to a switch.   I have other pieces I will need to add to this and Switch is much cleaner, IF it will work correctly.   I did try it with Or as well in each If statement.

Switch(
    LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateValue(LabelDate.Text)).TeamRotation,

     "Mon-Fri",
  If(
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateValue(LabelDate.Text), true), Style.DayBG,
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 1, Days), true), Style.DayBG,
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 2, Days), true), Style.DayBG,
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 3, Days), true), Style.DayBG,
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 4, Days), true), Style.DayBG,
     White
     ),

     "Fri-Sun",
    
    If(
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateValue(LabelDate.Text), true), Style.DayBG,
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 1, Days), true), Style.DayBG,
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 2, Days), true), Style.DayBG,
     White
    )
  )

This works:

If(
    LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateValue(LabelDate.Text)).TeamRotation = "Mon-Fri" &&
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateValue(LabelDate.Text), true) Or
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 1, Days), true) Or
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 2, Days), true) Or
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 3, Days), true) Or
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 4, Days), true), Style.DayBG,
     
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateValue(LabelDate.Text)).TeamRotation = "Fri-Sun" &&
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateValue(LabelDate.Text), true) Or
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 1, Days), true) Or
     LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateAdd(DateValue(LabelDate.Text), 2, Days), true), Style.DayBG,
     White
    )
  

[WarrenBelz]

Hi @JR-BejeweledOne ,

A huge amount of repetitive values there - I am not entirely sure of your logic, but you might try this structure

With(
   {
      wDays:
      With(
         {
            wRotate:
            LookUp(
               GalleryMySchedule.AllItems, 
               DateValue(cr9f7_name) = DateValue(LabelDate.Text)
            ).TeamRotation,
            wDate:DateValue(LabelDate.Text),
         },
         DateDiff(        
            wRotate,
            wDate,
            Days
         )
      )
   },
   Switch(
      wRotate,
      "Mon-Fri",
      If(
         wDays <5,
         Style.DayBG,
         White
      ),
      "Fri-Sun",
      If(
         wDays <3,
         Style.DayBG,
         White
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[JR-BejeweledOne]

I am getting a couple errors.   I can resolve the first one by wrapping it in a DateValue, but that doesn't resolve the second one.

[JR-BejeweledOne]

Also it looks like it's trying to compare a date value against a text value.  The wRotate value is either Mon-Fri or Fri-Sun, so how can it be used here:

DateDiff(        
            wRotate,
            wDate,
            Days
         )

   This should be comparing wDate (LabelDate.Text, in the Calendar) against cr9f7_name in the gallery to get the start date.

So a little more detail will probably help.   This is an On call schedule.   The date in the gallery is the first day of the rotation.   The only days that will be highlighted are the days the person is oncall.

So that DateDiff has to use the starting date that matches a date in the Gallery.

@WarrenBelz 

[JR-BejeweledOne]

I got it down to a single error, but I can't figure out why it's not recognizing wRotate.

If(LookUp(GalleryMySchedule.AllItems, DateValue(cr9f7_name) = DateValue(LabelDate.Text), true),

With(
   {
      wDays:
      With(
         {
            wRotate:
            LookUp(
               GalleryMySchedule.AllItems, 
               DateValue(cr9f7_name) = DateValue(LabelDate.Text)
            ).TeamRotation,
            wStartDate: LookUp(
               GalleryMySchedule.AllItems, 
               DateValue(cr9f7_name) = DateValue(LabelDate.Text)
            ).Name,
            wDate:DateValue(LabelDate.Text)
         },
         DateDiff(        
            DateValue(wStartDate),
            wDate,
            Days
         )
      )
   },
   Switch(
      wRotate,
      "Mon-Fri",
      If(
         wDays <5,
         Style.DayBG,
         White
      ),
      "Fri-Sun",
      If(
         wDays <3,
         Style.DayBG,
         White
      )
   )
)
)

[WarrenBelz]

@JR-BejeweledOne ,

Because it needs to be in the "outside" statement

With(
   {
      wDays:
      With(
         {
            wStartDate: 
            LookUp(
               GalleryMySchedule.AllItems, 
               DateValue(cr9f7_name) = DateValue(LabelDate.Text)
            ).Name,
            wDate:DateValue(LabelDate.Text)
         },
         DateDiff(        
            DateValue(wStartDate),
            wDate,
            Days
         )
      ),
      wRotate:
      LookUp(
         GalleryMySchedule.AllItems, 
         DateValue(cr9f7_name) = DateValue(LabelDate.Text)
      ).TeamRotation,
   },
   Switch(
      wRotate,
      "Mon-Fri",
      If(
         wDays <5,
         Style.DayBG,
         White
      ),
      "Fri-Sun",
      If(
         wDays <3,
         Style.DayBG,
         White
      )
   )
)

My initial post was a more syntax structure suggestion - did not quite get the values correct.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[JR-BejeweledOne]

With is one of the concepts I am not super familiar with yet and nesting it is something I haven't done, so I appreciate your assistance.

[JR-BejeweledOne · Accepted solution]

I was unable to get this to work for a couple reasons.   For some reason it didn't like the switch at all.  It worked with my original If statement, but as soon as I introduced Switch in any configuration it wouldn't work.   There ended up being another issue with the dates.

I resolved it by nesting a gallery inside the calendar gallery that contained the filtered schedule items for each day and color coding it that way.