Building a flow that filters a sharepoint list for a value to input in a column in another sharepoint list

[Power_Aissam]

Hello,

I am building a task list app and I have two sharepoint lists: the first one stores all information related to the tasks and the second has a calendar.

In the first sharepoint list (task list), there are many columns including one that is called Workday (Choice column) and another called MonthEnd (Caluclated column with a formula to calculate the last friday of the month. For example,  last friday of august 2019 would be 2019-08-30).

In the second sharepoint list (WorkList_Calendar), there are four columns including Workday (Choice column), due date (Date column), and MonthEndC (Calculated column).

Note that the values for the Workday & Month End columns are the same in both sharepoint lists.

The goal is to filter the second sharepoint list (Calendar) based on the Workday and Month End to find the corresponding due date and input it in the first sharepoint list (task list) in a due date column (Date column).

Thanks in advance for your help!

Here are some screen shots of what I tried so far but doesn't work:

1. check if the frequency of the task is Monthly

2. Get my item data and the data from the Calendar list:

3. I try to check if the value of the month end is the same in both sharepoint lists (filtering by month end):

I think this is where I have a problem with the formula:

Formula for the task list share point list: 

formatdatetime(triggerBody()?['Month_x0020_End0'], 'yyyy-MM-dd') . here I use the Month End field from the trigger when an item is created or modified and not from the get item.

Formula for the calendar share point list:

formatdatetime(body('Get_items')?['MonthEnd2'], 'yyyy-MM-dd').

I keep getting an error when I run my flow that the value of the first formatdatetime is null and I am not sure why that happens because when I test with a compose the output is 'yyyy-MM-ddThh:mm:ssZ'

4. I check if the workday values are the same in both sharepoint lists (filtering by workday):

5. Then I update the item using the ID and Task from the trigger (when item is created of modified) and Due Date from the WorkList_Calendar sharepoint list:

[v-alzhan-msft]

Hi @Power_Aissam :

Have you renamed the MonthEnd and MonthEndC column when they are created?

I afraid that you have renamed the both column, you could delete the both column and create a new with the name MonthEnd and MonthEndC, then you could refer to my expression below in the Condition.

The expression for the MonthEnd column in the Condition should as below:

formatdatetime(triggerBody()?['MonthEnd'],'yyyy-MM-dd')

The expression for the MonthEndC column in the Condition should as below and please make sure therey is an apply to each in the expression:

formatdatetime(items('Apply_to_each')?['MonthEndC'],'yyyy-MM-dd')

please take a try and let me know if your problem could be solved.

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Power_Aissam]

@v-alzhan-msft  unfortuantely, the flow runs for very long time (30 mins+) and gets abrupted. 

My data set is not even big, it's 115 records. Flow doesn't seem to be able to filter through it easily nor rapidly. I am not sure if there is a more efficient way of doing this?

[v-alzhan-msft]

Hi @Power_Aissam ,

Please make sure your MonthEnd and MonthEndC column are data type without time, and refer to screenshot below to create the flow:

Please let me know if your issue still exists in the new flow.

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Power_Aissam]

Hi @v-alzhan-msft 

I have tried this solution and my flow runs for very long time. Then, it gets abrupted.

So I am not sure what to do in this case ?

Thanks,