apply to each item Condition doesn't work properly

[MahanMehrvarz]

Hi I am trying to make a room booking service with Flow. I made a list and there is a column named "Room". I intend the flow to be trigered when an item is created then check the room value with previous values then if the room is booked on that specific time update the another column called "status" to Conflict and else update it to Approved.

However in the very first step which is checking the room name; whatever the room value is my flow basically changed all new status to "conflict" it seems that the condition doesn't work properly. I am not sure what I am doing wrong!

I appreciate any thoughts on this...

[Willemijn]

Hi,

What you are doing now, is with each already existing item updating the original item. So when you have 10 items already in your list you will update the item 10 times and the last check will define if it's conflict or not. 

If I understand correct you want the Flow to do the following:
1. When an new item in the room booking list is created
2. It needs to check in the other items if there is already another entry fpr the same room
3. If there is, your status needs to be updated to conflict, if not it's approved.

What I would do is the following:
For the get items I would add an Odata filter query, to check if there are items with that same room. When I get at least one item there is another item for that room so I can update my status according to that. I assume you need to still expand that with the check for date and time but this is the beginning.

See screenshots for some extra explination, I put a variable in there just to show and check but you can skip that and directly put it in your condition if you like. I assumed your room field is of type choice so therefore we need the Room Value

Odataquery: Room eq '@{triggerBody()?['Room']?['Value']}'
Variable is filled with: length(body('Get_items')?['value'])

Hope this helps!

[MahanMehrvarz]

Thanks Willemijn,

I am trying your direction however I don't understand something. woulld you please explain what is that "Room eq" doing before the Room Value item? I keep getting error No. 400 saying Room eq myRoomValue is not defined...

[Willemijn]

Hi, 

That is the filter query to get only the items in the list where the field 'room'  is equal to the value of the room field of the item that triggered the Flow. 

So it needs to be exaclty the name of the field. Is that the case? In your reply you get the value of myRoom, so is your field called 'myRoom'? then it shoudl be myRoom -eq '@{triggerBody()?['myRoom']?['Value']}'

hope this helps.

[MahanMehrvarz]

thanks.

let me try and will let you know

[v-bacao-msft]

Hi @MahanMehrvarz ,

The approach @Willemijn offers seems to be very close to your needs.

But I think it may be necessary to take the time frame into consideration here.

The List should contain Book date, start time and end time.

Filter the date first, then check to see if there is a conflict in the time range, and finally set Status value.

Hope it helps.

Best Regards,

[MahanMehrvarz]

Update:

for now my flow can distinguish duplicated room values and mark status as conflicted.  However I have no idea how to create several filters (start, end, and room) in a same get item. I have to create two intertwined conditions the first one checks for repetetive start and end then if it finds a duplicated start and end it checks for room.

any thoughts on this?

[Willemijn · Accepted solution]

Hi,

So in my testlist I have the following columns: 

• Room -> choicecolumn
• StartTIme -> Date/Time column
• EndTime -> Date/Time columen

note: time includes date in the following
For each item of that corresponding room we need to check the following scenario's:

Lets say we have a new booking from 10:00 - 11:00
Times that conflict are:
10:00 - 12:00: Starttime of the new booking is the same as an existing booking
9:00 - 11:00:  EndTime of the new booking is the same as an existing booking

10:30 - 11:30: Starttime of the new booking is less then the startTime of an existing booking, but the EndTime of the new booking is less than startTime of an existing booking
9:00 - 10:30: Starttime of the new booking is greater than an existing booking, but less than the EndTime of an existing booking

To check this, we need to alter the get items a bit; because otherwise it will get all the items of the same room, including the created item. Which will always resolve in a conflict with itself. So the condition in the get items will be the following: 
Room eq '@{triggerBody()?['Room']?['Value']}' and ID ne '@{triggerBody()?['ID']}'

Then you need to put the conditions in an apply to each to check whether one of those conditions is true:
Where the left values are all from 'when an item is created' and the right values are from 'get items'

Hope this helps! 

[MahanMehrvarz]

Hi

Thanks so much for your help and support . in finally figured out the problem and now have the room booking flow up and running appropriately.

I have used your solution however there was a tiny flaw in what your suggesting, i have fixed it and went forward.

the problem is I shouldn't filter the data based on Room value of the triggered item. because if there is an item which is not reserved sofar it wont bring any data to the condition and the result would be none of the Yes/No branch of the condition.

anyways, i removed the Room value filter and kept only the id filter then in all my conditions added the Room eq Room (as attached). it workes beautifully but is a little bit slow.

let me know if you have any other thoughts on that.

[Willemijn]

Great I was able to help you! The time it takes could be because in your filter you get all the items that are already in the list. Maybe you could use the filter on the room as wel, only put a condition in that like I did in the first reply. Check if you have any results, if not; it's approved. If you have results for the room, put the check on timeframe.