How to Automate a PDF File Output Name for a Microsoft Dynamics GP Posting Report
Many of you automating posting jobs have come across an issue with these schedulers: you are only allowed to output reports to the same physical file setup under your posting options. If you attempt to do this via Named Printers, the MDI writer will use a generic and all too cryptic 'NoteXXXX' file name that cannot be associated to anything coming from GP.
So, I figured you could probably use a SQL Server job that would run, say 20 minutes before your habitual posting schedule to change the file name. The following example will use the General Ledger Posting Journal report as an example, however, this can be accomplished for any report that need to be output to a file. In this example, it is all too important to keep in mind that the output of all transactions posting is stored in the 'Posting Journal Destinations' table (SY02200) in the company database.
1) Open Dynamics GP posting options. Go to Microsoft Dynamics GP > Tools > Setup > Posting > Posting. Choose Series:Financial, Origin:General Entry. Set the output of the report to file and enter a dummy file including the path as follows (use UNC for files that will be sent to a shared location on a server):
2) Setup a SQL Job that runs 20 minutes before your scheduled posting to execute the following statement:
NOTE: Replace //ServerName/ShareFolder/ with your path. If storing locally, the format is :drive_letter:RootFolder/Subfolder/ This simple example will allow you to automate a filename based on the date (which I converted to numeric for the convenience of the example, but you could extend according to your needs).
Until next post!
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
So, I figured you could probably use a SQL Server job that would run, say 20 minutes before your habitual posting schedule to change the file name. The following example will use the General Ledger Posting Journal report as an example, however, this can be accomplished for any report that need to be output to a file. In this example, it is all too important to keep in mind that the output of all transactions posting is stored in the 'Posting Journal Destinations' table (SY02200) in the company database.
1) Open Dynamics GP posting options. Go to Microsoft Dynamics GP > Tools > Setup > Posting > Posting. Choose Series:Financial, Origin:General Entry. Set the output of the report to file and enter a dummy file including the path as follows (use UNC for files that will be sent to a shared location on a server):
2) Setup a SQL Job that runs 20 minutes before your scheduled posting to execute the following statement:
declare @path varchar(255), @filename varchar(255)
set @path = '//ServerName/SharedFolder/'
set @filename = 'GLPJ' + convert(char(5), convert(int, getdate())) + '.PDF'
-- perform the update on the Posting Journals Destination table
update sy02200 set filexpnm = rtrim(@path) + rtrim(@filename)
where series = 2 and trxsourc = 'General Entry'
NOTE: Replace //ServerName/ShareFolder/ with your path. If storing locally, the format is :drive_letter:RootFolder/Subfolder/ This simple example will allow you to automate a filename based on the date (which I converted to numeric for the convenience of the example, but you could extend according to your needs).
Until next post!
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Comments