Understanding Document Sections in LBOX.ini
Distributing Documents via Email
Troubleshooting email functionality
Thank you for choosing LBOX from 32 Soft Inc!
LBOX is PDF and Excel document generator and distributor, designed specifically for QAD users to enhance their existing processes and lower operating cost.
LBOX easy-to-use reliable platform for the following applications:
QAD documents or reports immediately are converted to PDF or Excel and placed on the network share.
QAD month end reports automatically are generated and saved to a windows network share.
QAD reports are emailed in PDF or Excel formats to your team members or partners in personalized emails.
The following documents are good candidates to be automatically generated and distributed:
- Price lists updates
- Customer Statements
- Payment remittances
- Sales and Service Invoices
- AR Debit/Credit memos
- Purchase Orders
- Supplier schedules
- Sales Order acknowledgments
- Other not listed here
How it works
LBOX is running on a windows file server as Windows service.
LBOX accepts TCP/IP requests made by QAD server(s) in real time. It has a script which is placed to QAD server(s) and windows service itself which is running on a windows file server.
Installation
Installation is very simple, and takes literally 5 minutes. After obtaining a distribution package from the vendor, first, decide where LBOX home directory will be on the file server. Unzip the package content to that location, for example c:\LBOXSrv. Make sure the package is unzipped correctly, retaining sub-directory structure as shown below.
Find a configuration file called LBOXSrv.ini in $LBOX home directory.
[Server] port = 10089 LBOX Service port home = 32prn Leave as is bodyfiles = emailTemplates Email Body template Dir info = QAD_system Email Sender Name emlsrv = mail.company.com SMTP server name or IP emport = 25 SMTP server port sender = qadbatch@company.com Sender Email account emailUID = qadbatch@company.com Login As (optional) emailPWD = Password (optional) emlog = emOutbound.log Email log ModulePath = Leave as is ;emmaxsize = [defaults to 3Mb] Max Email file size [report] document section saveDir = c:\temp Save report to c:\temp directory [pdfLrpt] document section saveDir = c:\temp Save document to c:\temp directory template = 32prn\pdf\rptL132.pdf Create PDF by merging with PDF template pdfparams = -t 210 -l 30 -f 9 -s 10 -n 43 Text parameters [pdfPrpt] saveDir = c:\temp template = 32prn\pdf\rptP80.pdf pdfparams = -t 40 -l 20 -f 8 -s 12 -n 43 [form] document section saveDir = c:\temp Save document to c:\temp directory template = 32prn\pdf\company.pdf Create PDF by merging with PDF template pdfparams = -t 40 -l 63 -s 13 -n 43 Text parameters mergeBefore = append a PDF before page 1 (optional) mergeAfter = 32prn\pdf\tnc.pdf append a PDF after last page (optional) [FormEmail] saveDir = c:\temp template = c:\32prn\pdf\company.pdf pdfparams=-t 40 -l 63 -s 13 -n 43 [xls] Excel Output saveDir = c:\temp If section is called xls this dir is used ;inputGroupSeparator= [defaults to user locale] Optional ;inputDecSeparator= [defaults to user locale] Optional ;dateFormat= [defaults to "mm/dd/yy"] Optional ;excelDirective= [empty by default] Optional ;nextSheet= [empty by default] Optional
Review and edit [server] section of this file for now, save it when done.
Being an administrator on the server, click on the install.cmd script. The program will register LBOX as windows service and start it. Open Services from server control panel and find LBOX there.
By default, the service is started with local system credentials, but it can be changed at any point to a domain admin account. This will be required if LBOX should save documents on to network drives, which are not accessible with local system credentials from this file server. To do that, open service properties and submit valid domain admin credentials here as shown.
Please note: every time you make changes to LBOX control file, LBOX.ini, make sure you restart LBOX service after that, in order to changes to take effect.
Service Logging
As windows service, LBOX writes critical and important events into Windows Application Log, which can be browsed from Windows Event Viewer:
In addition to that, LBOX registers all outgoing email messages, whether they have been sent out successfully or not. The file name is defined in LBOX.ini file against “emlog” parameter, if parameter is omitted, LBOX does not write these events anywhere.
The format is simple, date – time stamp, file name, email recipient and status.
QAD Server
Copy all files from “nix_scripts” directory to QAD server, for example to /qad/lbox directory, and set permitions to 755 for all of the files. Except for LBOX2c.jar file, all the other files should be copied over as text files, please make sure, otherwise there might be errors executing lbox.sh script.
Edit LBOXc.ini file, “host” parameter should be pointing to windows file server IP address, “port” is port number.
host = 192.168.1.202 Should be pointing to windows server IP or name port = 10089 Port should be the same as in LBOX.ini
Review lbox.sh script, change variable JAVA_HOME to point to JRE or JDK.
JAVA_HOME = /qad/jre1.5.0_12
Please note: QAD software is bundled with Progress RDBMS software which has Java run-time (JRE) environment included. If your server has Java Development Kit (JDK) installed, it will be OK too. LBOX requires JRE or JDK version 1.4 or higher. If Java is not installed on your QAD server, please contact your server OS vendor or visit Java Download .
Setup in QAD
Create new printer outputs in QAD’s menu 36.13.2 as shown below.
Each LBOX output defines how PDF document should be generated and what should be done with it afterwards.
mgmgmt05.p b+ 36.13.2 Printer Setup Maintenance 08/15/07 +----------------------------- Printer Definition -----------------------------+ ¦ Output To: lbPDFFL Destination Type: Default ¦ ¦ Printer Type: ¦ ¦ Description: LBox PDF Landscape Lines / Page: 64 ¦ ¦ Max Pages: 0 Scroll Output: no ¦ ¦Device Pathname: cat >lb$PPID Spooler: yes ¦ +------------------------------ Printer Control -------------------------------+ ¦ Initialize Command: ¦ ¦ ¦ ¦ Initialize Ctrl: ¦ ¦ ¦ ¦ 80-Col Start Ctrl: ¦ ¦ ¦ ¦ 132-Col Start Ctrl: ¦ ¦ ¦ ¦ Reset Ctrl: ¦ ¦ ¦ ¦ Reset Command: /qad/lbox/lbox.sh lb$PPID pdfLrpt ¦ +------------------------------------------------------------------------------+
Create similar LBOX printer outputs as shown below.
| Output | Description | Device Pathname | Lines/Page | Script | Spooler | Section | Email List ID |
| lbPDFL | PDF 132 char Landscape | cat >lb$PPID | 64* | /qad/lbox/lbox.sh | yes | pdfLrpt | |
| lbPDFP | PDF 132 char Portrait | cat >lb$PPID | 64* | /qad/lbox/lbox.sh | yes | pdfPrpt | |
| lbPDFF | PDF 80 char Portrait form w logo | cat >lb$PPID | 64* | /qad/lbox/lbox.sh | yes | form | |
| lbPDFem | Email PDF form w logo | cat >lb$PPID | 64* | /qad/lbox/lbox.sh | yes | formEmail | posting |
| lbXL | Excel report | cat >lb$PPID | 70** | /qad/lbox/lbox.sh | yes | xls | |
| lbXLem | Email Excel report | cat >lb$PPID | 70** | /qad/lbox/lbox.sh | yes | xls | posting |
Notes:
* – adjustable parameter
** – any number, 70 is recommended
Understanding Document Sections in LBOX.ini
Each document section in LBOX.ini files defines how report or document should be processed. Currently, it can be saved as text file, or converted to PDF file saved and emailed to a recipient.
In order to save the report as a text file, simply define one parameter in the section “saveDir”, as shown here.
[report] saveDir = c:\temp
If PDF should be created, add a parameter “template” pointing to PDF background template. Also “pdfparams” parameter instructs LBOX how exactly merge text with the template.
[pdfLrpt] saveDir = c:\temp template = 32prn\pdf\rptL132.pdf pdfparams = -t 210 -l 30 -f 9 -s 10 -n 43
If static PDF page(s) should be appended before or after the body of the document, use “mergeBefore” or “mergeAfter” parameters as shown.
[form] saveDir = c:\temp template = 32prn\pdf\company.pdf pdfparams = -t 40 -l 63 -s 13 -n 43 mergeBefore = mergeAfter = 32prn\pdf\tnc.pdf
For more information regarding PDF page parameters please visit the following resources: PDF Page Parameter Reference
Please note: every time you make changes to LBOX control file, LBOX.ini, make sure you restart LBOX service after that, in order to changes to take effect.
Excel Ouput
In order to convert QAD report to excel, setup LBOX output in 36.13.2, which will be pointing to “xls” document section. In this case LBOX will generate excel files in directory shown across saveDir parameter in section [xls]. However, you may add many excel output directories for example: separate directories for finance, planning, customer service, operations, etc. Create sub keys under [xls] section and similar printers in QAD 36.13.2 pointing to these sub sections “xls.fin”, “xls.opr”, “xls.csr”, “xls.pln”.
[xls] Excel Output saveDir = c:\temp Output dir for section xls saveDir.fin = n:\qad\confidential\finance Output dir for section xls.fin saveDir.csr = t:\qad\csr Output dir for section xls.csr saveDir.opr = z:\reports\operations Output dir for section xls.opr saveDir.pln = o:\qad_reports\planning Output dir for section xls.pln
Other Excel parameters are optional and are global for all Excel outputs, here is how they can be used:
inputGroupSeparator = . QAD reports are printed in EU numeric format inputDecSeparator = , QAD reports are printed in EU numeric format dateFormat = dd/mm/yy QAD reports are printed in EU date format
Testing LBOX
Once QAD printers have been set up, go ahead and run any report to PDF 132 char Landscape. Make sure LBOX service is up and running. Once the report or document is sent from QAD, it momentarily appears in the specified directory on the file server. If problem, check IP address in LBOX2c.ini file, it should point to Windows server where LBOX service is running. The port number in LBOX2c.ini and LBOX.ini files should be the same. If still problem, give us a call we will help to troubleshoot.
ppptrp.p 2+ 1.5.1 Item Master Report 05/24/09 +------------------------------------------------------------------------------+ ¦ Prod Line: 1000 To: 1000 ¦ ¦ Item Number: To: ¦ ¦ Item Type: To: ¦ ¦ Group: To: ¦ ¦ Added: To: ¦ ¦ ¦ ¦ Print Comments: No ¦ ¦ Reporting Currency: USD Output: lbPDFL ¦ ¦ Batch ID: ¦ +------------------------------------------------------------------------------+
Test Excel output. Create a new LBOX printer output for excel. Run 1.5.1 report and select excel printer, find Excel file generated in in the specified directory on the file server. Please keep in mind that not all QAD reports can be parsed to Excel, more details.
Distributing Documents via Email
In order LBOX to send an outbound email to a recipient, insert the following command line to QAD output file. The command line is an instruction, with few elements:
ESCemail:mary.goltara@company.com^Invoice Print^Mary Allyn^posting.txt
ESC is ASCII code 002, non printable escape code email: literal constant mary.goltara@company.com recipient email address ^ delimiter Invoice Print email subject ^ delimiter Mary Allyn recipient first name, required by email template ^ delimiter posting.txt email template file name, should reside in [server]"bodyFiles"
Each command line triggers LBOX to send an email to the recipient. So if a document should be sent to 3 people, then 3 command lines should be added to the QAD output file.
ESCemail:support@company.com^Invoice Print^32 Soft Support^poprint.txt ESCemail:ryan.peters@company.com^Invoice Print^Ryan^poprint.txt ESCemail:mary.goltara@company.com^Invoice Print^Mary Allyn^poprint.txt Quality Products Inc. P U R C H A S E O R D E R One World Way Consolidated Business Plaza Order Number: 1000 Revision: 0 San Diego, CA 92130 Order Date: 06/10/92 Page: 1 United States of America Print Date: 05/07/07 Supplier: 5001000 Ship To: 10000 General Supply Corporation Quality Products Inc. 720 East College Avenue One World Way Building B-2 Consolidated Business Plaza Los Angeles, CA 90293 San Diego, CA 92130 United States of America United States of America ... ...
LBOX will exclude these instructions from the resulting document automatically.
Ways to Inject Email Commands
Option one: via shell script. This option is good when the list of recipients is static. For example: overnight GL postings should be always emailed to the same person(s) or list of Sales Orders on-hold always should be emailed to sales managers and credit department.
Create a list of recipients in lbox.email file, which is located in QAD server. The file format allows to maintain multiple lists, simply add a line for each recipient in the follwoing format:
[List ID]^[email address]^[Subject]^[Recipient's First Name]^[email content template]
So if we need to add a list “posting” with 2 names and list “invprint” with one name, the file will look like this:
posting^support@company.com^Overnight Night Postings^Doug^posting.txt posting^sdk@company.com^Overnight Night Postings^Nick^posting.txt invprint^contact@company.com^Invoice print^Peter^invoiceEmailBody.txt
Add another printer to 36.13.2 and reset command now will have one more parameter, a pointer to email list “posting”.
mgmgmt05.p b+ 36.13.2 Printer Setup Maintenance 08/15/07 +----------------------------- Printer Definition -----------------------------+ ¦ Output To: lbNtPost Destination Type: Default ¦ ¦ Printer Type: ¦ ¦ Description: LB Nite Post Email Lines / Page: 64 ¦ ¦ Max Pages: 0 Scroll Output: no ¦ ¦Device Pathname: cat >lb$PPID Spooler: yes ¦ +------------------------------ Printer Control -------------------------------+ ¦ Initialize Command: ¦ ¦ ¦ ¦ Initialize Ctrl: ¦ ¦ ¦ ¦ 80-Col Start Ctrl: ¦ ¦ ¦ ¦ 132-Col Start Ctrl: ¦ ¦ ¦ ¦ Reset Ctrl: ¦ ¦ ¦ ¦ Reset Command: /qad/lbox/lbox.sh lb$PPID pdfLrpt posting ¦ +------------------------------------------------------------------------------+
Now when you print any report to lbNtPost printer it will be converted to PDF report and emailed to people who are listed under “posting” in lbox.email file.
Option two: add email command to QAD printer setup. This option is the simplest one and very quick to do but limited to one recipient only. Go to menu 36.13.2 and add email instruction to the required LBOX printer output, to the “Initialize Ctrl” fields, for example:
mgmgmt05.p b+ 36.13.2 Printer Setup Maintenance 08/15/07 +----------------------------- Printer Definition -----------------------------+ ¦ Output To: lbNtPost Destination Type: Default ¦ ¦ Printer Type: ¦ ¦ Description: LBox PDF Landscape Lines / Page: 64 ¦ ¦ Max Pages: 0 Scroll Output: no ¦ ¦Device Pathname: cat >lb$PPID Spooler: yes ¦ +------------------------------ Printer Control -------------------------------+ ¦ Initialize Command: ¦ ¦ ¦ ¦ Initialize Ctrl: /002email:cvf@company.com^Postings^Cliff ¦ ¦ ^posting.txt ¦ ¦ 80-Col Start Ctrl: ¦ ¦ ¦ ¦ 132-Col Start Ctrl: ¦ ¦ ¦ ¦ Reset Ctrl: ¦ ¦ ¦ ¦ Reset Command: /qad/lbox/lbox.sh lb$PPID pdfLrpt ¦ +------------------------------------------------------------------------------+
Option three: via progress programming.
This is most flexible way and only way to generate and distribute documents automatically to a dynamic list of recipients. Example: auto emailing Supplier schedules or Customer Statements periodically.
This approach will require some technical skills, however the good news is that programing changes to be added QAD programs are very minor and can be easily added by IT staff even with little experience in Progress programming. Also this logic can be easily carried over to the new version of QAD during upgrade. As you can see, LBOX by its design requires minimum QAD changes and free of any maintenance.
Below we provide a sample code, which is used to automate PO document distribution, so newly created PO will be automatically printed overnight and emailed to suppliers. As “printed” flag is updated, the documents will be printed only once.
There are 2 blocks of progress logic that should be added to QAD’s poporp03.p program. This is the higher level PO Print program, which collects user input from “Selection Criteria” prompts. See below where additional logic is added, the similar manner the other QAD programs can be modified: 7.1.3 SO Confirmation Print, 7.13.3, 7.9.13, 27.15, 27.14 and so on.
In the original QAD’ program poporp03.p First find the code lines marked below with Red comments, near the end of the program.
/* poporp03.p - PURCHASE ORDER PRINT AND UPDATE */
…
…
…
/* RUN SELECTED FORMAT */
{gprfile.i}
{&POPORP03-P-TAG21}
if false then do:
{gprun.i ""porp0301.p"" "(input update_yn)"}
end.
{&POPORP03-P-TAG10} Additional code to be added before this line
{gprun.i """porp03"" + run_file + "".p""" "(input update_yn)"}
{&POPORP03-P-TAG11}
{&POPORP03-P-TAG22} Additional code to be added after this line
{mfreset.i}
if batchrun
then
l_runok = runok.
...
...
...
Add 2 new block of code there, green font.
/* poporp03.p - PURCHASE ORDER PRINT AND UPDATE */
…
…
…
{&POPORP03-P-TAG21}
if false then do:
{gprun.i ""porp0301.p"" "(input update_yn)"}
end.
/***** 32Soft Added Logic Starts Here *****/
def var vNbr like nbr no-undo.
def var vNbr1 like nbr1 no-undo.
def var vEmailTo as char no-undo.
def var vEmailSubj as char no-undo.
def var vEmailName as char no-undo.
def var vEmailBody as char no-undo.
assign vNbr = nbr vNbr1 = nbr1.
/*This for-each logic is taken as is from porp0301.p*/
for each po_mstr no-lock
where (po_nbr >= vNbr) and (po_nbr <= vNbr1)
and (po_vend >= vend) and (po_vend <= vend1)
and (po_buyer >= buyer and po_buyer <= buyer1)
and (po_print or not new_only)
and (po_ord_date >= ord_date) and (po_ord_date <= ord_date1)
and (po_lang >= lang and po_lang <= lang1)
and (po_stat = "" or not open_only)
and (not po_sched or include_sched)
and (not po_is_btb or incl_b2b_po)
and po_type <> "B",
first ad_mstr no-lock where ad_addr = po_vend by po_nbr:
assign nbr = po_nbr nbr1= po_nbr
vEmailTo = 'cdf@company.com' /*replace the value with field where email address is stored*/
vEmailSubj = 'PO print ' + po_nbr /*change if required*/
vEmailName = IF ad_attn <> '' THEN ad_attn ELSE 'Supplier' /*replace the value with field where first name is stored*/
vEmailBody = 'POEmailBody.txt'. /*change if required*/
{mfreset.i}
output through value(path) page-size value(printlength).
put control chr(2) 'email:' vEmailTo '^' vEmailSubj '^' vEmailName '^' vEmailBody chr(10).
{&POPORP03-P-TAG10}
{gprun.i """porp03"" + run_file + "".p""" "(input update_yn)"}
{&POPORP03-P-TAG11}
{&POPORP03-P-TAG22}
end. /*for each*/
assign nbr = vNbr nbr1= vNbr1.
unix silent sleep 1.
/***** Added logic ends here *****/
{mfreset.i}
...
...
...
It does make sense to maintain customer and other partner’s email addresses along with their names in QAD database. You can store it anywhere of your choice: ad_mstr, vd_mstr, ls_mstr, code_mstr, usrw_wkfl, custom table. Then you can pull this information and assign to variables.
Once the change is made and poporp03.p is compiled and installed, you can run PO Print either interactively or submit as a job to daily batch like shown below.
poporp03.p 2+ 5.10 Purchase Order Print 05/25/09 +------------------------------------------------------------------------------+ ¦Purchase Order: To: ¦ ¦ Supplier: To: ¦ ¦ Buyer: To: ¦ ¦ Order Date: To: ¦ ¦ Language ID: To: ¦ ¦ ¦ ¦ Open PO's Only: Yes Print Features and Options: No ¦ ¦ Unprinted PO's Only: Yes Include Retained Taxes: Yes ¦ ¦ Include Scheduled Order: No Sort PO By: Site ¦ ¦ Print Bill-To Address: Yes Form Code: 1 ¦ ¦ Include EMT Orders: No Update: Yes ¦ ¦ ¦ ¦ Message: ¦ ¦ ¦ ¦ Output: lbPOprt ¦ ¦ Batch ID: DAILY ¦ +------------------------------------------------------------------------------+
Please note that selected lbox printer output “lbPOprt” does not need to point to email list, since email command lines will be added by print program.
Email Templates
One of the server’s parameters in LBOX.ini file is “bodyfiles”, it is pointing to a directory where email templates are located. Email templates are plain text files, where you can add variable $name. At the run time, the variable will be replaced with recipient first name.
Below is the content of an email template, “POEmailBody.txt”.
Dear $name, Please find attached PO from 32Soft. Have a Great Day! 32 Soft Team.
Troubleshooting email functionality
In the case email functionality is not working properly,
here is what you can do to troubleshoot:
1) Login to the File Server console where LBOX service is installed.
Use the same user account as LBOX is running as. This is important.
2) Testing SMTP service with the telnet command.
Go to Start->run->cmd.exe
From a command prompt run:
telnet SMTP.Server.or.IP.Address.com
25
The server should respond with a 220 message if it is working correctly (type QUIT to end, you should get a 221
message).
,—– [ UC = You type, LC= SMTP Service response ]
| C:\>TELNET SMTP.MYDOMAIN.COM 25
| 220 mydomain.com ESMTP
| QUIT
| 221 mydomain.com
|
| Connection to host lost.
`—–
Note: SMTP.MYDOMAIN.COM and port (i.e. 25 above) should be set the same way to LBOX.ini file.
emlsrv = SMTP.MYDOMAIN.COM
emport = 25
sender = qadbatch@company.com
emailUID = qadbatch@company.com
emailPWD =
Parameters emailUID and emailPWD are email user ID and password respectively, required parameters in some cases.
Try sending any report. LBOX Email log file should have a record with the result of email attempt.
LBOX Email log file name is set in LBOX.ini as emlog parameter.
emlog = emOutbound.log
Troubleshooting LBOX
Sometimes, LBOX does not produce output files in expected folders.
1) First, check to see whom you run LBOX service as. Go to control panel, Administrative Tools, Services, find LBOX, Properties, Log On. If it’s running as Local system account and if you are exporting the files to local hard drives, then you are good. Otherwise, you need to run LBOX as a valid global domain user, which has access to required network directories.
2) Check to see if there is a firewall between QAD server and Windows file server, blocking ports. This is quite often happens in QAD on-demand installations or when QAD app is hosted in third party sites. In this case, make sure LBOX port is open, it’s listed in LBOX.ini file. You can do a quick test to validate the port:
Under unix/linux: go to QAD server, from command line type
telnet LBOX-IP LBOX-port
where LBOX-IP is IP address of the file server where LBOX is running.
where LBOX-port is port listed in LBOX.ini file.
You should be able to see the responce form LBOX. If telnet command replies that no connection can be established, the port is closed. Check with your network administrators.
Under Windows: go to QAD server console and launch putty.exe (free telnet ssh client which you can look up and download from the internet). Add a new connection to the file server where LBOX is running,
connection type=Raw
Port=10189 (port listed in LBOX.ini file)
Try to connect. You should be able to see the responce form LBOX. If the command replies that no connection can be established, the port is closed. Check with your network administrators.