Unanswered

Filter excel file, create a html table and send it by email

(0) Share

Report

Posted on by Lucas_Goncalves

Hello, good morning from Brazil 🙂

I´m suffering on the last 3 weeks on this flow demmand. I checked google, Reddit, Medium, even here... can´t solve... I can´t sleep, can´t eat, cant be happy.... (Ok, Im joking...)

What I´m trying to do:

Trigger: When an item is created in a sharepoint list ( This list has a form where I will imput vendor code, say, 12345)(OK!)

1) List rows present in a table (Properly formated as a table) that has been saved on Sharepoint (file name is FBL1N.xlsx)(OK!)

2) Filter this excel with the trigger information (Vendor colum = form imput)(not OK!)

3) Create an html table(OK!)

4) Style Html table (OK!)

[replace(body('Create_HTML_table'), '<table>', '<table border="3" bgcolor="ffffff">')]

5) Send e-mail with the Styled HTML on it (OK!)

My actual flow:

What I´m stuck:

I cant create the filtered html table. I mean, the e-mail that has been sent on the above flow, is not filtered with the result from the trigger form information.

On step 2 (List row in a table) I know that there are a "Filter Query". But when I add the form information, it immediately changes to an "apply to each" and then I cant create html table anymore.

Are you there, Obi-wan Kenobi? Can you help me? What I am doing wrong? How can I escape the dark side and go to the correct Jedi Flow Pathway?

Categories:

Building flows

I have the same question (0)

All responses (28)

Answers (0)

Sort by

fchopo 8,003 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hello @Lucas_Goncalves

Before creating the html table, you could use a "filter array" action, filtering the results by vendor and then creating the table. Have a look at this example:

In this case, I'm filtering by the "expense" value column (greater than 10).

Hope it helps!

Ferran

Was this reply helpful? Yes No
Lucas_Goncalves 85 on at

Like (0)

Report
Copy link

Link copied!

Hello @fchopo , thanks for your support!

When I apply the filter as per your suggestion:
Filter array from value of the Lists Rows present in a table, using colum name "Vendor" is equal to the information present on the form "Title".

It forces the "apply to each" and then the same problem persists:

It don´t shows colums informations on Dynamic content to allow me to create the html table:

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hello @Lucas_Goncalves

Just realized that it creates an "apply to each" action when you use the "title" field that you get from the "Get Items" SharePoint action. One question: Why do you use the "Get Items" action? Or what is the purpose of it? As I understand, you want to:

1) When an item is created in SharePoint.

2) Open an excel file and filter the rows according to the vendor value entered in step 1.

3) Export the excel to the HTML Table.

Am I right?

Ferran

Was this reply helpful? Yes No
Lucas_Goncalves 85 on at

Like (0)

Report
Copy link

Link copied!

@fchopo Amazing, Apply each solved!

But...

Filter seems to not finding information as it sends me empty email:
This is how it is:

This is how it appears in the result: Filter output is empty

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hello @Lucas_Goncalves

Looking at the screenshots you shared, the vendor code is stored in a column name "Code" and not in the "Title" one. Could you check it? Therefore, you should filter by this column, and not by Title.

Hope it helps!

Ferran

Was this reply helpful? Yes No
Lucas_Goncalves 85 on at

Like (0)

Report
Copy link

Link copied!

SP did not updated the Renaming of the standart column name "title" as "Codigo" on Flow... they are the same.

I figured out that there might be empty row problem, as I got to the excel file, selected the vendor code that appears on first line and the filter worked as it should be.

When I check on the second, after the empty rows, it broke:

Did a workaround to a condition: if empty, do nothing. If no, do that what you teached me. Appeared to work! Will do a few more test before giving you the big winning solution:

Was this reply helpful? Yes No
Lucas_Goncalves 85 on at

Like (0)

Report
Copy link

Link copied!

@fchopo upon testing here i just noticed that HTML table is comming with dates in wrong format ("Data de Pagamento" and "Emissão" are date columns:

How can I fix that?

I´m still testing the filter.

Was this reply helpful? Yes No
Lucas_Goncalves 85 on at

Like (0)

Report
Copy link

Link copied!

@fchopo Good night!

Still with filter error 😞 made 20 different attemps.

It sends me an empty e-mail with no HTML table rows (only the columns name). It seems that the error is on the filter array that can´t output the information.

Can you check if the below picture can help you to help me?

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hello @Lucas_Goncalves

Let's try to narrow your problem. Could you change your filter array expression and try it with simple values? For example, try to use an expression like:

@equals(item()?['vendor'],'127476)

You should try with different values or expressions until you find out what's happening.

On the other hand, if you want to format the dates, in the create html table you could you use an expression like:

formatDatetime(output('data de pagamento','dd/MM/yyyy')

Hope it helps!

Ferran

Was this reply helpful? Yes No
Lucas_Goncalves 85 on at

Like (0)

Report
Copy link

Link copied!

Hi @fchopo , good morning!

About the problem: It seems that the filter array is limited to 256 lines on the excel file (it has more than 10k lines).

I found this as I checked the test result and found last line it could narrow. That is why all vendor code that is above this line number is suceeded.

How can I overpass this limitation? I know it is not pagination as it is for "Sharepoint´s get itens" and it is limited to 5k.

Will test your suggestion to specify the vendor code number as you suggested: @equals(item()?['vendor'],'127476)

Was this reply helpful? Yes No