cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
daniels471
Helper II
Helper II

variable in for data source

little stuck here 

 

so I have a formula which is currently a load of nested if statements but understand I could move this to use the switch function.

 

If(varcurrentroom="MS-ICT",
    If(First(Filter('MS-ICT','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).booked= true,First(Filter('MS-ICT','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).name & Char(10) & First(Filter('MS-ICT','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).class & Char(10) & First(Filter('MS-ICT','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).subject,"Book"),

 

 

however my question is that I'm looking for a way to not have to keep repeating this formula and have a variable with the room name in ( the data source), this would make it far easier to maintain and add additional rooms .

 

I have already tried changing the data source within the filter functions to be my variable but that doesn't seem to work 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @daniels471 ,

I must admit I looked at the volume of your code and initially thought "good luck with that", but I had a bit of spare time and put the below together.

NOTE - this is free-typed and is only a guide to the syntax I believe you need to approach it. It is unlikely I have got all the commas and brackets exactly right.

With(
   {
      wBooked:
      DateAdd(
         LookUp(
            'week list',
            week=varCurrweek,
            'start date'
         ),
         4
      )
   },
   Switch(
      varcurrentroom,
      "MS-ICT",
      With(
         {
            wLesson:    
            LookUp(
               'MS-ICT',
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyA",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyA,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyB",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyB,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyC",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyC,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.Booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyD",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyD,
               ''booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyE",
       With(
         {
            wLesson:    
            LookUp(
               TrolleyE,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyF",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyF,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "KS3-laptop-1",
      With(
         {
            wLesson:    
            LookUp(
                KS3-laptop-1,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "KS3-laptop-2",
      With(
         {
            wLesson:    
            LookUp(
                KS3-laptop-2,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "KS2-laptop-1",
      With(
         {
            wLesson:    
            LookUp(
                KS2-laptop-1,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "Classwatch",
      With(
         {
            wLesson:    
            LookUp(
                Classwatch,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "Sensory-room",
      With(
         {
            wLesson:    
            LookUp(
                Sensory-room,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "Outdoor-Gym",
      With(
         {
            wLesson:    
            LookUp(
                Outdoor-Gym,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      )
   )
)

 

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.

View solution in original post

7 REPLIES 7
WarrenBelz
Super User
Super User

Hi @daniels471 ,

Firstly, it is helpful to post using "Format Text" - I ma not sure this is completely your format, but you need a With() statement to avoid all the repetition of the same value

With(
   {
      wBooked:
      First(
         Filter(
            'MS-ICT','booked from'= 
            DateAdd(
               LookUp(
                  'week list',
                  week=varCurrweek,
                  'start date'
               ),
               4
            ),
            Lesson="1"
         )
      )
   },
   If(
      varcurrentroom="MS-ICT",
      If(
         wBooked.booked=true,
         wBooked.name & Char(10) & wBooked.class & Char(10) & wBooked.Subject & "Book"
      )
   )
)

Note that I have simply copied your filter and assume it works.

 

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.

 

Hi @daniels471 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

i have used your formula with the slight modification of the last if statement since the "book" should be the else value

 

however I'm now a little lost as to how to get the other rooms in that formula ( the list 'MS-ICT' is specific to that room)

my original very heavily nested formula is below which does work but can see maintaining it will be an issue

If(varcurrentroom="MS-ICT",
    If(First(Filter('MS-ICT','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter('MS-ICT','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).name & Char(10) & First(Filter('MS-ICT','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter('MS-ICT','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book"),

If(varcurrentroom="TrolleyA",
    If(First(Filter(TrolleyA,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).booked= true,First(Filter(TrolleyA,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).name & Char(10) & First(Filter(TrolleyA,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).class & Char(10) & First(Filter(TrolleyA,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).subject,"Book"),

If(varcurrentroom="TrolleyB",
    If(First(Filter(TrolleyB,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).booked= true,First(Filter(TrolleyB,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).name & Char(10) & First(Filter(TrolleyB,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).class & Char(10) & First(Filter(TrolleyB,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).subject,"Book"),

If(varcurrentroom="TrolleyC",
    If(First(Filter(TrolleyC,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).booked= true,First(Filter(TrolleyC,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).name & Char(10) & First(Filter(TrolleyC,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).class & Char(10) & First(Filter(TrolleyC,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),Lesson="1")).subject,"Book"),

If(varcurrentroom="TrolleyD",
    If(First(Filter(TrolleyD,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter(TrolleyD,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).name & Char(10) & First(Filter(TrolleyD,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter(TrolleyD,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book"),

If(varcurrentroom="TrolleyE",
    If(First(Filter(TrolleyE,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter(TrolleyE,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).name & Char(10) & First(Filter(TrolleyE,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter(TrolleyE,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book"),

If(varcurrentroom="TrolleyF",
    If(First(Filter(TrolleyF,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter(TrolleyF,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).name & Char(10) & First(Filter(TrolleyF,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter(TrolleyF,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book"),

If(varcurrentroom="KS3-laptop-1",
    If(First(Filter('KS3-laptop-1','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter('KS3-laptop-1','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),3),lesson="1")).name & Char(10) & First(Filter('KS3-laptop-1','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter('KS3-laptop-1','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book"),

If(varcurrentroom="KS3-laptop-2",
    If(First(Filter('KS3-laptop-2','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter('KS3-laptop-2','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).name & Char(10) & First(Filter('KS3-laptop-2','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter('KS3-laptop-2','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book"),

If(varcurrentroom="KS2-laptop-1",
    If(First(Filter('KS2-Laptop-1','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter('KS2-Laptop-1','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).name & Char(10) & First(Filter('KS2-Laptop-1','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter('KS2-Laptop-1','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book"),

If(varcurrentroom="Classwatch",
    If(First(Filter(Classwatch,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter(Classwatch,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).name & Char(10) & First(Filter(Classwatch,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter(Classwatch,'booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book"),

If(varcurrentroom="Sensory-room",
    If(First(Filter('Sensory-room','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter('Sensory-room','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).name & Char(10) & First(Filter('Sensory-room','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter('Sensory-room','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book"),

If(varcurrentroom="Outdoor-Gym",
    If(First(Filter('Outdoor-Gym','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).booked= true,First(Filter('Outdoor-Gym','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).name & Char(10) & First(Filter('Outdoor-Gym','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).class & Char(10) & First(Filter('Outdoor-Gym','booked from'= DateAdd(LookUp('week list',week=varCurrweek,'start date'),4),lesson="1")).subject,"Book")
)))))))))))))

Hi @daniels471 ,

I must admit I looked at the volume of your code and initially thought "good luck with that", but I had a bit of spare time and put the below together.

NOTE - this is free-typed and is only a guide to the syntax I believe you need to approach it. It is unlikely I have got all the commas and brackets exactly right.

With(
   {
      wBooked:
      DateAdd(
         LookUp(
            'week list',
            week=varCurrweek,
            'start date'
         ),
         4
      )
   },
   Switch(
      varcurrentroom,
      "MS-ICT",
      With(
         {
            wLesson:    
            LookUp(
               'MS-ICT',
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyA",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyA,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyB",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyB,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyC",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyC,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.Booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyD",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyD,
               ''booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyE",
       With(
         {
            wLesson:    
            LookUp(
               TrolleyE,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "TrolleyF",
      With(
         {
            wLesson:    
            LookUp(
               TrolleyF,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "KS3-laptop-1",
      With(
         {
            wLesson:    
            LookUp(
                KS3-laptop-1,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "KS3-laptop-2",
      With(
         {
            wLesson:    
            LookUp(
                KS3-laptop-2,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "KS2-laptop-1",
      With(
         {
            wLesson:    
            LookUp(
                KS2-laptop-1,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "Classwatch",
      With(
         {
            wLesson:    
            LookUp(
                Classwatch,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "Sensory-room",
      With(
         {
            wLesson:    
            LookUp(
                Sensory-room,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      ),
      "Outdoor-Gym",
      With(
         {
            wLesson:    
            LookUp(
                Outdoor-Gym,
               'booked from'= wBooked && lesson="1"
            )
         },
         If(
            wLesson.booked,
            wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
            "Book"
         )
      )
   )
)

 

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.

Hi @daniels471 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

daniels471
Helper II
Helper II

hi

 

so to give an update on this ,I have tried to use your formula which you provided .

With(
    {
        wBooked: DateAdd(
            LookUp(
                'week list',
                week = varCurrweek,
                'start date'
            ),
            0
        )
    },
    Switch(
        varcurrentroom,
        "MS-ICT",
        With(
            {
                wLesson: LookUp(
                    'MS-ICT',
                    'booked from' = wBooked && lesson = "1"
                )
            },
            If(
                wLesson.booked,
                wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
                "Book"
            )
        ),
        "TrolleyA",
        With(
           {
                wLesson: LookUp(
                    'TrolleyA',
                    'booked from' = wBooked && lesson = "1"
                )
            },
            If(
                wLesson.booked=true,
                wLesson.name & Char(10) & wLesson.class & Char(10) & wLesson.subject,
                "Book"
            )
        )
    )
)

the If statement always comes back as false

 

with some very basic troubleshooting it seems like the contents of wLesson is empty

 

I shall continue to see if I can figure anything out but figured i would post an update just incase there is something obvious stopping it from working 

daniels471
Helper II
Helper II

not to worry , I think that was down to user error there so I think your solution has done the trick 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

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