Filter a Sharepoint multi choice lookup by date and time from another list

[dplaza_westgate]

Hi, first time post so hope I'm in the right place.

OK, I've made a SharePoint list as a school trip form for staff to fill out to request approval to go on trips, we have 3 minibuses and they are regularly double-booked, therefore I'm trying to customise the form in Power Apps to filter which buses are available during the date and time entered into the form. I have another list called 'Bus List' with the info of each bus and another list that just holds the bookings of each bus called 'Bus bookings'. 

I can get a gallery to show me which buses are available, but when it comes to selecting them to be written back to the form I get circular reference. I've tried so many different ways to do this and I've completely lost my mind..

This is from the trip list ('Trips 2023'), I've create a lookup column feeding from the 'Bus list', the other 'Minibus CHOICE' column is a simple multi-choice column, I've just been trying everything I can think of.

Here is my 'Bus bookings' list, again, the 'Bus column is a lookup to the 'Bus list'

and here's my simple 'Bus list'.

What would be ideal is when going through the form, depending on the dates and times you've selected, the bus selection would filter out the ones that aren't available, only leaving you with the available ones for you to choose.

So far, I've been able to filter a separate gallery using this formula:

Filter('Bus list',Not(Title in Filter('Bus bookings','Booked out from' >= DateTimeValue(StartDateTimeText) && 'Booked out until' <= DateTimeValue(FinishDateTimeText) || 'Booked out from' <= DateTimeValue(FinishDateTimeText) && 'Booked out until' >= DateTimeValue(FinishDateTimeText)).BusName))

This formula references text labels on another screen in the app just for the purpose of comparing the dates from the 'Bus bookings' list and the form you're filling out.

I can't for the life of me figure out how to implement that filter into the datacard of the form. I've tried to write the Whenever I seem to get close, I get the circular reference error.

Any help would be immensely appreciated!

Thank you

[turnerj3]

@dplaza_westgate ,

I have gotten around circular references in the past by doing filters outside of the target and setting variables, then calling the variable in the control where it is needed. Variables come in handy and are easy to monitor while troubleshooting the code.

[dplaza_westgate]

Ok, so is that similar to a collection? I've tried creating a collection called 'busesSelected' from the selected items in the filter and calling that in the update part of the data card but it gives me 'expected a record'. I'll give variables a go. Im a bit of a noob on powerapps so I'll need to read up on how they work before I try it! Thanks for the suggestion!

[turnerj3]

Kind of similar, it is in memory, but can store just about anything. You can add a label to your screen and make text=varwhatever and see your output immediately. 
The circular reference usually comes in play when you are using a current value and trying to calculate a new value in the same control. Instead Set(varformula, your calculation) then set the drop-down box items varformula.

[dplaza_westgate]

Oh! So put the filter calculation as a variable. Ok, I'll be messing with those first thing Monday when I'm back at work. Thanks a lot for your help, I'll update once I've tried them out.

[turnerj3]

It sounds like the data card wants 1 row or a string. You could use the Concat function to turn your collection values into 1 line. 
Concat(busesSelected,busescolumn,",")

This will separate the values in the buses column with a comma.

You could also use Distinct(busesSelected,busescolumn)

In the items of a drop-down box.

Sounds like you are trying to remove buses from a drop-down if they are booked? You may have to create some variables/collections and restring them together to get a new drop-down items list without the booked buses.

Here are some samples of variables, and Concatenates I used the other day.

Set(varValues,Concat(ComboBox2.SelectedItems,Value,","));
Set(varValues2,Concat(ComboBox2_1.SelectedItems,Value,","));
Set(varValues3,Concatenate(varValues,",",varValues2));
ForAll(Split(varValues3,","),Collect(ComboCol,Split(varValues3,",")));
//Set(varDistinct,Concat(Distinct(ComboCol,Value),Value,","))

Popup Combobox Items = Distinct(ComboCol,Value)

[dplaza_westgate]

OK, so a bit of an update, sorry for the delay, other work has had to be done as well so I've worked on this when I can get time.

I've got to be honest, I don't quite get your examples, I tried them but they just confused me, but pointing me in the direction of variables has moved me closer to getting this working.

I can now get to filter the drop down, however, it doesn't write back to the form since I'm not picking from the "Choices", I'm picking from an entirely different list, it seems anyway. 

I haven't messed with "Distinct" much so that'll probably be what I try next.

To answer your last post, yes, I am trying to remove buses that are booked from the list of choices, whether it be a lookup or just a choices column, I'd like it to be a lookup ideally, but I'll take whatever works!

[turnerj3]

Can you apply your filter using your choices as the source?

[dplaza_westgate]

When I try to apply my filter it gives me the circular reference error

[dplaza_westgate]

This is the error message I get, it seems only have 1 thing I need to fix, unless I'm being naïve!

This error also appears on my Start and Finish date and time labels