Answered

Look up within a list between 2 dates

(0) Share

Report

Posted on by padavison

My scenario is a simple SP list A which contains 2 dates/ time (start and finish) and a person, who is "on call" for that period (usually whole weeks but can vary). When a user creates an item in another list B they give the date of that event and I want to lookup up the on-call staff member based on the event's finish date/ time and add their details to list B. If the event is too far in the future, the on-call staff may not be selected

Any pointers on how to achieve this? TIA

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

v-yetonggu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @padavison ，

Is your personnel column a single-select or multiple-select personnel column?

Could you please post a screenshot of both lists?

Best Regards,

Sunshine Gu

Was this reply helpful? Yes No
padavison 19 on at

Like (0)

Report
Copy link

Link copied!

Thank you. This is List A. The SRO is a single-select of person and will be maintained every month for 2 or 3 months ahead.
This is list B which records events. I want to populate the SRO column from List A based on date in End Date of List B. A flow triggers when an event is created which sends an ICS attachment of the event to the creator and their "buddy" and I want that to include relevant SRO info for that particular day.

Was this reply helpful? Yes No
Verified answer

v-yetonggu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @padavison ,

Please check this test:

My SharePoint List A:

My SharePoint List B:

My Flow Overall Preview:

My Flow Configuration Parameters:

Enddateandtime ge '@{items('Apply_to_each')?['StartDate']}' and Enddateandtime le '@{items('Apply_to_each')?['EndDate']}' length(outputs('Get_items_2')?['body/value'])

The Result:

Best Regards,

Sunshine Gu

Was this reply helpful? Yes No
padavison 19 on at

Like (0)

Report
Copy link

Link copied!

Thank you Mr Gu (or can I call you Sunshine?) for your excellent insights. Works perfectly (so far).

Was this reply helpful? Yes No
padavison 19 on at

Like (0)

Report
Copy link

Link copied!

A little follow-up. I have this added to the end of a flow the triggers when a new item is created in List B. This updates the new item of course with the SRO but also updates all existing items in List B. This adjusts the Modified field of all the items which is not ideal and could also get quite extensive in number.

Is there a simple way of restricting the update to SRO in list B only where this field is currently empty (i.e. a new item)? There are so few changes to SRO that an update to all items is not justified. Thanks again

Was this reply helpful? Yes No