Searching for Fields from Spreadsheet

[chrisingham]

Hi,

I've created a flow which does the following;

1. Lists the files in a Folder
2. For each file;
   1. Convert the document to text
   2. Add a line to a spreadsheet where there is a column for each search term within the text. At present, each column has the formula below which searches for the term and returns Yes if it finds the term in that file
      1. if(contains(string(body('Convert_Word_DOCX_Document_to_Text_(txt)')),'[Search Term, also the column name]'),'Yes','')

The issue is, I currently have about 20 search terms and columns in the output spreadsheet, for each one I paste in the above formula and then manually type in the search term. In future this number of terms will increase and I dont want to be manually writing the formula. Is there a way I refer to the output column name in the formula as the search term, or an array or something else that would do this? Obviously the search term needs to match the output column name;

[v-litu-msft · Accepted solution]

Hi @chrisingham,

You could initialize an array variable, then List all rows of the terms Excel table. Then use the Apply to each action to loop through these rows, append each item into the variable:

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

[chrisingham]

Hello,

Thank you for this suggestion, I see that this would allow me to have an array with each of the search terms listed in the array, however I then need to somehow run the if statements and add more values to the array which I'm not sure how to do. For example, I think the steps would be as follows;

1. List items in the spreadsheet which contains the search functions
2. Create an array from the search functions
3. Apply to each document in the folder, then apply to each item in the array, if document contains array item name then add Yes to the array otherwise no,  
4. Repeat for each document and keep adding rows to the array
5. Then finally write all data from the array into a spreadsheet

Or

1. List items in the spreadsheet which contains the search functions
2. Create an array from the search functions
3. Apply to each document in the folder, then apply to each item in the array, if document contains array item name then add Yes to the array otherwise no,  
4. write the data from the array into a spreadsheet
5. Reset the array
6. Repeat for each item

I'm not sure how to do the later steps listed above, and I'm not that familiar with arrays, I see them as a matrix of effectively columns and rows which represent values added to the array but I'm not sure if it works like that in flow. 

Any suggestions or examples would be great

Thanks

Chris

[v-litu-msft · Accepted solution]

Hi @chrisingham,

After you get all term and put them into an array, you could use the contains condition or contains() function to determine the value from Excel whether exist in the array, for example:

If Yes, append these pieces of information into the Rows array variable:

Then loop the Rows array, input these pieces of information into the Excel table:

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