cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MahanMehrvarz
Level: Powered On

apply to each item Condition doesn't work properly

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...

01.PNG2.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Willemijn
Level 8

Re: apply to each item Condition doesn't work properly

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'

2019-07-15 10_49_48-Edit your flow _ Microsoft Flow.png

 

Hope this helps! 

View solution in original post

9 REPLIES 9
Willemijn
Level 8

Re: apply to each item Condition doesn't work properly

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'])

 

Flow Room Booking.png

 

Hope this helps!

MahanMehrvarz
Level: Powered On

Re: apply to each item Condition doesn't work properly

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
Level 8

Re: apply to each item Condition doesn't work properly

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.

Highlighted
MahanMehrvarz
Level: Powered On

Re: apply to each item Condition doesn't work properly

thanks.

let me try and will let you know

Community Support Team
Community Support Team

Re: apply to each item Condition doesn't work properly

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,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MahanMehrvarz
Level: Powered On

Re: apply to each item Condition doesn't work properly

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
Level 8

Re: apply to each item Condition doesn't work properly

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'

2019-07-15 10_49_48-Edit your flow _ Microsoft Flow.png

 

Hope this helps! 

View solution in original post

MahanMehrvarz
Level: Powered On

Re: apply to each item Condition doesn't work properly

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.

 

series2-1.PNG

 

series2-2.PNG

 

Willemijn
Level 8

Re: apply to each item Condition doesn't work properly

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. 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 407 members 6,005 guests
Please welcome our newest community members: