Sending Results of Query to a csv file

Hi Brad,
I use the "append to file" in a loop. You will need to include a CR/LF in each record. So, create a variable in the start node, call it CRLF, and use javascript to assign carriage return+line feed (off top of my head it's something like chr(13)+chr(10), but look it up to make sure. Then in your File Access node add <!CRLF> to the input string. =

Hi Brad,
To add a carriage return+ line feed to the end of every record you can create a variable with "\r\n".
But just pushing the enter button at the end of the record also seems to work.

John and Erik,

Thanks for the assitance. I ended up creating a variable in the start node.

String vCRLF="\n"

And then I used the variable in my FileAccess node.

Seems to be working.

Thanks!
Brad

Brad and friends,

Hey I am doing the same thing I have the data being written out to a csv file. But now I am trying to attach the csv file I just created in the flow and then send. Do you have any ideas on how to attach? I attached the _returnMessage of my FileAccess node that I created, but all I get is the following message in my email:

These attachments would be sent in run mode:
<!CreateCSVFile_returnMessage>

I am running this locally on my machine to test, is there something I am missing?

If the filename will always be the same what you can do is take a copy of the file and upload it into ProcessFlow to be used as an email attachment and then have your output overwrite that file everytime you run your job. The flow will pick up the latest version to send in the email as an attached file.

[quote]
Posted By ConsultDavidW on 08/19/2010 10:14 AM
If the filename will always be the same what you can do is take a copy of the file and upload it into ProcessFlow to be used as an email attachment and then have your output overwrite that file everytime you run your job. The flow will pick up the latest version to send in the email as an attached file.
[/quote]

What type of node do I use to upload the file into the flow? The FileAccess. Some history for you, the file will change 12 times of year (so monthly) and the name will have the month numbe rin it, but it can be replaced many times a month on demand by users.

Ward,
If you have PFI, you can use the File Access node to write the file. Specify the file path and name (path should be %LAWDIR%\bpm\emailattachments). Then, use an email node to attach the file, by specifying that same directory from which to retrieve the deisred attachment file.

Within ProcessFlow Designer, select Tools and then Upload Document to Server. Select the file you want and it will then be available to "attach" within your email node. I don't remember off hand where the file is stored in Lawson but once you know the path you can make that your output and overwrite the existing file monthly.

I wasn't sure if the Email attachment would recognize the file unless you uploaded it first, but give it a try.

Thanks folks, that might be my problem - that I am not running on the server. Our admin cannot seem to turn the RMI services on so that I can upload / download from server, so I am stuck and the present moment and wont be able to figure this out. Let me see if I can get this opened up so I can try to figure this out with the FileAccess node.

ConsultDavidW: In my email node I have in my flow, I already am pointed to the FileAccess node; but I have not uploaded said file to server for the flow to detect. So I am wondering the same thing.

On second thought though on this as well, the only reason for attaching the file would be for folks to download the file. I can just as easy write to the location already and be done with it and not email it. This might be a better way to go about it. I am still going to work with our admin and figure out the RMI issue though. So I think I might call it good.

Thank you all for your prompt help today with this!!!!!

Since the suggestions here seem to involve overwriting data to the same file by every Work Unit for a process, isn't there danger that another Work Unit for the same process running concurrently could pickup the file that was written by, and therefore intended to be picked up by, a different concurrent Work Unit? Or vice versa, that another Work Unit for the same process running concurrently could write to the file before the other process had picked up the file it had written to, resulting in data being overwritten before it was picked up? Shouldn't there be some safeguard to ensure that the file written by the FileAccess node for that Work Unit was the same file picked up by only that same Work Unit? If so, how can this be done when the Email Node > Browse only allows you to specify the location of the attached file.

In other words, process A has Work Unit A and writes to file A, process A has Work Unit B writes to file A. Both Work Units get kicked off at the same time and try to write to file A, then try to pick up file A.

I am similarly trying to attach a CSV file (of records currently collected by a MsgBuilder node) to an email.

Thanks very much!
Lyle

Hi Lyle - that is certainly a concern in cases where the flow isn't scheduled or otherwise managed. When I have situations like this, I will typically append a datestamp, datetimestamp, or workunit number to the end of the filename that is written. This ensures that every file is unique. If the filename and path is stored in a variable, then you can use that variable as the attachment name in the email node.

Good Luck!

Perfect. I think I'll use both datetime and work unit. Thanks very much, Woozy!

Now I just need to figure out what the email node does when there is no file to pick up (the file contains errors and often there are none).

Hi Lyle - It will either send anyway (without the attachment) or it will error. I can't remember which.

In order to avoid this I usually do one of two things - either I add a counter that increments with each "OK" record, or I determine if the final file length is greater than zero and set a flag. Then I evaluate the counter or flag and branch to the email if appropriate. Good Luck!

It errors. So I've done as you suggested, which actually involves two email nodes, one for a file that had no errors (and therefore no attachment) and one for a file that had errors (and therefore picks up the error file attachment). Once again, thanks very much for your help!

 

 

Hi Lyle - what exactly errors? Can you attach your log file?

Oh - wait. I guess it's doing what you meant it to do. I thought you meant it was throwing errors.

Sorry for the confusion.

I've got it working now. I was responding to you being unsure whether an email node would error or not when a file that the email node was expecting to attach did not exist. I've confirmed the node errors however the process completes. But most importantly for me, the email node does not generate an email.

Here is the error:

Email Email8310: Email failed with error javax.mail.SendFailedException: Attachment: \\win1362\data\General_Ledger\ErrorEmails\HCHB_GL_Errors_DateTime20165117125147_WorkUnit366.csv does not exist.
at com.lawson.bpm.processflow.workFlow.flowGraph.FgaEmail.sendMailDirect(FgaEmail.java:681)
at com.lawson.bpm.processflow.workFlow.flowGraph.FgaEmail.startActivity(FgaEmail.java:518)
at com.lawson.bpm.processflow.workFlow.flowGraph.FgActivity.execute(FgActivity.java:845)
at com.lawson.bpm.processflow.workFlow.flowGraph.FgProcess.run(FgProcess.java:2182)
at com.lawson.bpm.eprocessserver.grid.ExecuteFlowImpl.executeFlow(ExecuteFlowImpl.java:374)
at com.lawson.bpm.eprocessserver.grid.ExecuteFlowImpl.executeFlow(ExecuteFlowImpl.java:137)
at com.lawson.bpm.eprocessserver.grid.ExecuteFlowImpl.executeFlow(ExecuteFlowImpl.java:123)
at com.lawson.bpm.eprocessserver.ProcessFlowEngine.execute(ProcessFlowEngine.java:150)
at com.lawson.bpm.eprocessserver.ProcessFlowEngine.startProcessAt(ProcessFlowEngine.java:101)
at com.lawson.bpm.eprocessserver.KBConnection.startProcess(KBConnection.java:125)
at com.lawson.bpm.eprocessserver.KBConnection.execute(KBConnection.java:95)
at com.lawson.bpm.eprocessserver.KBConnection.run(KBConnection.java:75)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

I am constructing the error file name regardless of whether it is needed or not, which is why the email node references a specific file name.

Thanks for checking in, Kelly!

Thanks all, this thread was helpful!