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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (849)