Filter/Collection Issues and questions

[robbiebobbie92]

Hello All, 

I have taken the MS BookARoom Template and turned it into my own for my company. I have a gallery(based off a collection (AvailableRoomEmailName)) that has all my rooms from Office365. I have also created a sharepoint list (DeskList) that has all of the desks and rooms to be booked with all of their equipment. I created a column called CleanStatus (COVID...YAY) so we know which desks are clean and dirty. I am trying to filter the gallery with the follwing command:

Items=
If(LookUp(DeskListCollect,Title="DAL-Desk1",CleanStatus)="Dirty",
Filter(AvailableRoomEmailName, Name="DAL-Desk2"||Name="DAL-Desk3"),
LookUp(DeskListCollect,Title="DAL-Desk2",CleanStatus)="Dirty",
Filter(AvailableRoomEmailName, Name="DAL-Desk1"||Name="DAL-Desk3"),
LookUp(DeskListCollect,Title="DAL-Desk3",CleanStatus)="Dirty",
Filter(AvailableRoomEmailName, Name="DAL-Desk1"||Name="DAL-Desk2"))

In this case, inside of the SP list. Desk2 and Desk 3 are marked as dirty. But when the gallery is filtered with the command above, it spits out Desk 1 and Desk 3. It seems that the If statements stop at the second lookup command since its true and skips the 3rd. How do I go about have all of them run at the same time. 

This might not be the best way to go about it, I was thinking of adding a column into the AvailableRoomEmailName collection with the CleanStatus column from the SP list since they all have the same names. But im not sure if thats possible from what research I have done. 

If you can think of a better way to go about this im all ears!

Let me know if you need any more information to help me this question!

[RandyHayes · Accepted solution]

@robbiebobbie92 

Your formula is working properly.  When you provide multiple conditions for an If, the first one to evaluate to true is evaluated and then it is done.

I am not entirely sure what it is that you are trying to determine in your formula.  If it to find the rooms available that are "clean" (i.e. not dirty), then you might consider this instead:

Filter(AvailableRoomEmailName, Name in Filter(DeskListCollect, CleanStatus="Clean").Title)

I hope this is helpful for you.

[Drrickryp]

Hi @robbiebobbie92 

There may be a more elegant way to do this but you it seems you need to create a filter for each condition and then merge them.  I did not get into the logic of your formula so assuming that you can filter each condition correctly, I would create a new collection and append the results of each filter. 

With({DLC:DeskListCollect,
      AREN:AvailableRoomEmailName
     },
        Collect(
                 Desks, LookUp(
                              DLC,Title="DAL-Desk1",CleanStatus
                       )="Dirty", Filter(
                                          AREN, Name="DAL-Desk2"|| Name="DAL-Desk3"
                                  )
         )
 );
With({DLC:DeskListCollect,
      AREN:AvailableRoomEmailName
     },
        Collect(
                 Desks, LookUp(
                               DLC,Title="DAL-Desk2",CleanStatus
                        )="Dirty", Filter(
                                           AREN, Name="DAL-Desk1"||Name="DAL-Desk3"
                                     )
        )
);
With({DLC:DeskListCollect,
      AREN:AvailableRoomEmailName
     },
        Collect(
                Desks, LookUp(
                              DLC,Title="DAL-Desk3",CleanStatus
                        )="Dirty", Filter(
                                          AREN, Name="DAL-Desk1"||Name="DAL-Desk2"
                                   )
         )
)

You could then have the Items property of a gallery or datatable be desks.  To make it work faster, you could use Concurrent() to wrap the formulas and separate them with "," instead of ";".

[robbiebobbie92]

Randy, 

That command was exactly what i was looking for. The only issue im having with it, is it pulls all of the rooms. So I tried to do this:
Filter(AvailableRoomEmailName, Name="DAL-Desk1" || Name="DAL-Desk2" || Name="DAL-Desk3" in Filter(DeskListCollect, CleanStatus="Clean").Title)

As im only needing to pull 3 desks, for this section im working on. But, when that command runs, it only pulls the first two desks. If i swap desk 2 and 3, the results show desk 1 and 3. Should I create a collection just of those desks for easier filtering?

[robbiebobbie92]

Thats what I needed to do.  On the button that selects the sector of the floor plan they want to sit at. It creates the collection with the desks in that section. So then im able to use the following command from Randy to filter correctly.

Filter('1Floor1SectorCollect', Name in Filter(DeskListCollect, CleanStatus="Clean").Title)