Email file based on variable row count

Hello I need to send an email to my clients with their previous days BOL information. I have a pretty simple table configured to show the BOL (bill of lading) details for the previous day including client name and email addresses. I have only 5-10 clients but they don’t all have BOLs everyday and one client might have i.e. 4 rows of data and another might have 1 row.

I’ve never programmed a loop and I think this should be a good example of where to use a loop. Which Loop Start (and end) node should I use? I need to:

  1. Create xlsx output showing one clients data (regardless of row count). This is where I struggle - How can I program that into a loop? To filter all rows for one client?
  2. Send an email with the xlsx attachment using the email address in the xlsx file. Not sure how to do that either.
  3. Loop (repeat) until all rows have been sent to clients

I appreciate any help! thank you

Mona

Hi @MJRIOUX,

here a small example how to do it

-have a Table with how you want to split your data e.g. the filter criteria for the clients (could also be more variable with rule engines)
-Then loop though this table e.g. chunch loop for row to variable loop
-use the defined filter criteria to filter your input data
-write data to output file e.g. Excel/CSV
-use the send Email Node to send the Mail (the attachment via flow variable set to the correct output file)

workflow3.knar (21.2 KB)

Client Config
grafik

Email-Node


Excel-Node

2 Likes

Hello there!

Like this approach @AnotherFraudUser! Additionally it would be good to have email address in Client Config table and Empty Table Switch node after Row Filter in case there is no data for this client so not to send email with empty Excel file. Also would use Create Temp Dir node to write newly created files there in case those are not needed after being sent to client.

@MJRIOUX you can also go with Group Loop Start node on your base data to get data for one client in each iteration.

Br,
Ivan

2 Likes

Hi @MJRIOUX,

also Knime just posted a few video guides for the different loop options.
Maybe that will also help you to see what you can do with loops :slight_smile:
e. g.




1 Like

Awesome! Thank you so much to both @AnotherFraudUser and @ipazin. I built the loop as shown below but not sure if it will work yet because I have an error on the Excel Writer Node 73 (after the Emtpy Table Switch) that says:
“Execute failed: Illegal character in path at index 0: \hq\heidfs\data\lg_group\Everyone\Supply\Ethanol%20Trading\Automatic%20BOL%20email%20project%202020\KNIME\Co_Op%20Liftings.xlsx”

In the node, the url is reading from an Excel file where the url is written exactly like this:
'\hq\heidfs\data\lg_group\Everyone\Supply\Ethanol Trading\Automatic BOL email project 2020\KNIME\No Liftings.xlsx

I strongly suspect that it doesn’t like the in front of the url in Excel. No matter what I try, that stays there. Any suggestions on how to fix it?

Thank you,

Mona

1 Like

Hi @MJRIOUX,

can you post your excel writer configuration?
Have you set the Excel writer configuration to local path?
Or maybe can you post your workflow here? Then’ll fix it for you :slight_smile:
*you can exclude your source data file if there is confidential information

Thank you but we did finally fix that bug by changing to “LocalFileSystem” in the Excel Writer Node 73.
image

Now I have another bug! The loop works for the first 2 clients, sends the emails properly but then stops and has this error on the Variable Loop End:

" Active Scope End node in inactive branch not allowed."

This seems to be because the next client has no BOLs to report and at the Empty Table Switch, it automatically goes to the empty table. Any ideas how to fix this?

I apologize but I can’t figure out how to actually paste my workflow here? Here’s a picture again:

Hi @MJRIOUX,

great! - that is what i meant with local path - just couldn’t remember the exact description :see_no_evil:

For your inactive error - put an endif or endswitch after the two excel writers and combine the workflow paths before the end loop :+1:
https://hub.knime.com/knime/extensions/org.knime.features.base/latest/org.knime.base.node.switches.caseswitch.variable.CaseEndVariableNodeFactory

To post your workflow you have to export the workflow to the desktop (right-click on the workflow in knime - export) and then copy&paste that file here in the forum

2 Likes

Thank you again @AnotherFraudUser! I have solved it in a different manner but will keep your solution in mind for other workflows. I eliminated the need for the Empty Table Switch by ensuring the data entering the loop is already listing only clients with data to email. See below. And thank you for the directions on exporting a workflow. Again I appreciate all your help tremendously!
Mona

3 Likes

Great, thx for sharing your solution :slight_smile:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.