How to add conditional filters using items from different SharePoint Lists?

[Australia]

Hi fellow PA veterans,

I have been trying to create a flow where I like to send out overdue reminders for unpaid invoices.

How do I achieve the following condition:

If ( invoice due date (SP List 1) = calculated due date (initialise variable)

AND 'invoice status' (SP List 2)  = 'incomplete',

Send email out,

do nothing )

More details:

In SharePoint List 1, I have 'invoice numbers', 'invoice due dates' and 'email address' saved

In SharePoint List 2, I have 'invoice numbers' and 'invoice status' saved  (where 'invoice status' = Complete/Incomplete)

I used 'Get Items' action to get invoice due dates from List 1 to calculate the date.  I also want to get 'invoice status' from List 2 and use it as a condition. I also need to link/look up the invoice number so that Power Automate knows Invoice #12345 in List 1 = Invoice # 12345 in List 2.

Visualising my flow in the image below:

Thank you for taking your time reading my post.

[annajhaveri]

@Australia  in the List 2, the invoice number column, is that a lookup type of column?

[Australia]

@annajhaveri thanks for the reply.

The invoice number in both List 1 and 2 is a single line string. E.g. 2021/01 in both lists

[annajhaveri]

@Australia  okay in that case you will also need to Get Items from the list 2. There are two options one is to get items from list 2 and then compare output of both get items action to determine incomplete items or add Get Items inside the apply to each action and use the Invoice Number from list 1 item in as filter query on Get Items of list 2.

[Australia]

Hi @annajhaveri 

Thanks for the suggestion. I've tried option 1 by adding 'get items from list 2' but it seems I have messed up my flow. I can see two 'Apply to each' actions now and I am not sure if I've done the right thing here.

I am now trying Option 2 .

"add Get Items inside the apply to each action and use the Invoice Number from list 1 item in as filter query on Get Items of list 2"

Is this what you meant I should put for the filter query?

invoice_x0020_number eq 'Dynamic Content (Invoice Number List 2)'

[annajhaveri]

@Australia  for the option 1 do following:

1. Add apply to each action, select  value (list of items) of the List1 Get Items

2. Inside apply to each action, add Filter Array action, and in From parameter, select value (list of items) of the List2 Get Items

3. On left side of value select Invoice Number from Get Items List1, and on right side select Invoice Number from Get Items List2

Now Filter array will give you item from List 2 that matches with invoice number of List1 of each iteration of apply to each item. So e.g. there are 4 invoices in returned by Get Items of List 1, then Apply to each will run for 4 times, and for each iteration inside apply  to each you will get matching item from List 2 from the Filter Array output. So you can add Send Email action after the filter array action.

Use below expression to get the status value from filter array

first(body('Filter_array_2')['InvoiceStatus'])

For option 2 yes the filter query is correct, see below for steps

1. Add apply to each action and select the value (list of Items) from Get Items of List1

2. Inside apply to each add Get Items and configure it to get items from List2 with filter query as shown below.

3. Now you can add Send Email action and to get value of Invoice Status use below expressions

first(body('Get_items__-_Invoice_List_2')?['InvoiceStatus'])

[Australia]

Hi @annajhaveri , hope you had a good Easter break.

I am following your Option 2, is the below what you meant?

I'd like to note that a second 'Apply to each' has automatically been as soon as I added filter query invoice_x0020_number eq 'Dynamic Content (Invoice Number List 2)' to Get Items 2.

Also in the Control Filter, is this the place where you put in the expression?

first(body('Get_items__-_Invoice_List_2')?['InvoiceStatus'])

where body('Get_items__-_Invoice_List_2') is a dynamic content block and you type in ?['InvoiceStatus']

 manually?

Sorry I am still learning, please be patient with me. 

[annajhaveri]

@Australia  yes the apply to each will get automatically added, if you haven't added it, there should be one apply to each action inside which you will have Get Items action with filter query.

The expression in your flow will be as follows, you need to provide expression using Dynamic Content -> expression tab as shown in below screenshot

first(body('Get_items_2')?['InvoiceStatus'])

[Australia]

Hi Anna,  sorry for not getting back to you sooner. I went on an extended medical leave and have now picked up this project again.

This is where I am with option 2. I think I am completely lost...