Create CSV to use an attachment in an email

I'm working on a flow that will query database for records and compile using the FileAccess node to create a csv file to be attached in an email if there more than 1 record count. When I run the flow, I get data returned and a csv file is created, but when I try opening the csv file, it says "null". Am I missing a step to send the data to csv? What should the variable be in the Input Data section of the FileAccess node?

Find more posts tagged with

Process Automation (IPA) - General Discussions

Accepted answers

unknown

Here is a sample:

MsgBuilder: Note the Variable Name here.

FileAccess: Note the Input data is where you put the msgbox Variable Name from the MsgBuilder node.

All comments

unknown

The Message Builder Node contains ID, Name, and Variable Name. You need to pass the Variable Name from the Message Node as the Input Data on the File Access Node.

For example, if the Message Node Variable Name = msgNode, Input Data for the File Access Node should be <!msgNode>.

If that still does not work then check the variables you are using to pass data to your Message Node. Getting "null" in the file output means a variable is writing but contains no data. Could be that you are passing the correct variable from the Message Node but not actually passing data. Log files will be really helpful when checking that out.

unknown

Here is a sample:

MsgBuilder: Note the Variable Name here.

FileAccess: Note the Input data is where you put the msgbox Variable Name from the MsgBuilder node.

john-miller

If youre running the SQL Node to return a CSV file, there is a block of Landmark versions that has issues with joins and will not create the CSV, and can give you this Null in the SQL_CSV variable. This will force you to write each record to a file using 1301297's method or similar. If you follow the reply from 1301297, you may run into issues if your MsgBuilder variable holds to much data. (It'll return something like variable is over 1k length) when it goes to do the file write. Theres a sample lpd in the Best Practices KB that Russ From Infor produced that has a good file creation and append file every X amount of records. Then when you go to attach the csv to the email, just make sure you use the full file path and name in the Misc: section.

This can be accomplished with variables as well.

unknown

Thanks so much! I appreciate the screenshots. This works perfectly now!

russel-cornelson

Just to expand on what JD added..

Variables in java are immutable (cannot be changed); when you put a message builder in the middle of a SQL Query like that you can run into various problems that are memory related in the LPA node.

The LPA Grid node runs on java; when you add row #1 to the message builder it creates an object in memory. When you add row#2 it throws away the first object and creates a new one with 2 rows. When you add the 3rd row it throws away the object and creates a new one. Every object you create, gets bigger and bigger. Once the object gets big enough you start impacting the performance of the system.

The best practice when using a message builder is to use a counter to periodically write to the file, and clear out the contents of the message builder variable.

unknown

How would this be set up? Screenshots are great. This would be a flow that would only need to be run once every other week to check time records that meet a specific criteria.

Do you know how to schedule every other week starting at week to be determined?

russel-cornelson

An example flow exists on KB 1667935, on how to intermittently write to a file. Example flow is called:

IPABP-FileAccess-IntermittentWrite.lpd

Scheduling is actually handled by Async (Landmark Environment team) but I am fairly certain there is a few ways to schedule something to run bi-weekly. Under the advanced options there is a bi-weekly option, and there is "Even Numbered Weeks" and "Odd numbered Weeks".

john-miller

Creating the Schedule

Process Server Administrator -> Scheduling -> By Process Definition

Right Click the Trigger and select Schedule, or Highlight the Trigger, select Actions -> Schedule

Scheduled Action Name: Give the Schedule a descriptive name to identify it in “My Scheduled Actions”
Schedule Type = “By Week Number”
Week Number = Bi Weekly
Year: Blank Day: (The Day of the Week to run it) Hour: 24 hour clock on the start Hour Minute: Goes with Hour to declare the Start Time of the Schedule If you don’t Declare a Scheduled Action name, it’ll show up in your Scheduled actions as PfiTrigger.Schedule Please note, this schedule only displays in the User’s “My Scheduled Actions” that created the schedule.

unknown

Another option is to call SQLCMD from a System Command Node (Link to Syntax).

Something like this will generate a large delimited file much, much faster than the SQL Node to Message Builder. The example below generates a pipe-delimited file, while forcing the node to not move on until the file is finished writing. Useful if you have a file that you need to write a header or footer to, along with the body.

"START /b /wait SQLCMD -S <!ServerName> -d <!DBName> -U <!UserName> -P <!Password> -s"|" -h -1 -W -q "set nocount on; Select * From <!strBodyTable> Where <!strWhereClause>" | findstr /V /C:"-" /B >> <!strOriDir><!strFileName>"

Of course, not going to be fun if we ever switch to multi-tenant and this goes away.

Copyright © 2025 Infor. All rights reserved.