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:
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?
Send an email with the xlsx attachment using the email address in the xlsx file. Not sure how to do that either.
Loop (repeat) until all rows have been sent to clients
-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)
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.
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?
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
*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.
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:
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
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
