cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JR-BejeweledOne
Super User
Super User

Switch broke my formula

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
    )
  )

 

switchissue.png

 

 

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
    )
  

 

switchissue2.png



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
1 ACCEPTED SOLUTION

Accepted Solutions

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.



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

View solution in original post

7 REPLIES 7
WarrenBelz
Super User
Super User

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
Super User
Super User

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.

 

witherror1.pngwitherror2.png



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
JR-BejeweledOne
Super User
Super User

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 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
JR-BejeweledOne
Super User
Super User

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
      )
   )
)
)

 

witherror2.png



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

@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.

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.

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

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.



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,890)