Get files from SharePoint, filter by array and sen...

Anonymous · ‎07-15-2022

Dear friends.

Question: How to search files from SharePoint folder (including subfolder) if their filename contains values listed in Excel file (see column Invoice in table below)? Then I need to check if such files exist, compose email and attach all such files to this email.

CASE DESCRIPTION:

I have an Excel table on SharePoint with "orders" as shown in table below

Order Date	Order Number	Customer	Invoice	Sendmail Status
01 Jan 2022	Order 1	Customer 1	INV-01
01 Jan 2022	Order 2	Customer 1	INV-01
02 Jan 2022	Order 3	Customer 2	INV-02
03 Jan 2022	Order 4	Customer 3	INV-03

Notice that: each customer may have several orders. Several orders may be grouped under one invoice.

Task:

Step 1. On regular basis check Excel file. Filter rows if:

Invoice column is not empty and
Sendmail Status is empty

Step 2. Check if there are any rows in Excel file satisfying conditions mentioned in Step No. 1.

if no - terminate the flow
if yes, then go to Step 3

Step 3. Select data from Excel file for rows filtered in Step 1.

Step 4. Create HTML Table based on data from Step 3.

Step 5. Go to SharePoint folder and search for files in particular folder that contains Invoice value (like "INV-01" or "INV-03") in filename. These can be files of any type (pdf, excel, word).

Step 6. Take files that match criteria in Step 5. If Excel table contains several rows with the same Invoice value (e.g. Order 1 and Order 2 in Excel file are related to INV-01) take this file only once (there is no need to take one file several times).

Step 7. Compose email that contains HTML table from Step 4 and attach files to email from Step 6. Then send email to users.

Problem:

Step 1 - I use "List Rows Present in Table" and "Filter Array" with multiple conditions. Everything is working fine

Step 2. I use "SELECT" from "Filter Array" to prepare a list of Invoice values as array. Then I use "Compose" to sort array only for unique values. And then I use "Condition" to check if there are any rows in Excel file satisfying conditions mentioned in Step No. 1.

Step 3 and Step 4 are also no problem. I know how to select data from Excel and how to prepare HTML table.

STEP 5. Here is the problem.

I use "Get Files (properties only)" to list all files located in particular folder (including its subfolders) on SharePoint. The problem is that I don't know how to query files where filename contains a list of values defined in array in my "Compose" function?

As I can see ODATA filter in Get Files action may accept only string values, but not an array.

I also don't know how to attach all queried files in STEP 5 to email.

Your ideas and help will be appreciated.