Printing and MailMerging in Ebase

[ghale]

Using Javascript, I can get a MailMerge to trigger in Word successfully. However this only works if both the Main Document and Datasource are located on either the C:\ drive or on our LAN.

The aim is to take a set of records shown on screen in Ebase and produce an Identical letter for each. To achieve this, the desirable solution would involve the user clicking on a single button to trigger the MailMerge, and then another button to actually print the file (either in Ebase, Word or another tool).

It appears as though the Write2CSV function in Ebase, will only write to the application server from where Word is unable to access it. By referring to the CSV on the application server as a URL, it is possible to open the CSV in a viewer.

Rather than opening the CSV with the link, it is also possible to right-click on the link and select the “save target as” option. The CSV can then be saved to any path and filename on the C:\ drive or LAN.

It would not be desirable to ask the users to select path and type in filename, as this would lead to a good chance of errors - the Mail merge would then not work correctly.

If anyone has had similar requirements, whether or not a solution was found, I would appreciate your comments.

Questions:
1.If this is not possible in Word, can I create a MailMerge using PDFs?
2.If so, can I automate the process of getting the resultant pdf(s) on the
application server to local machine for printing?

[Vircos]

You can do something similar with the Velocity template engine which is integraded in Ebase version 3.3 and later.

Velocity uses HTML for the layout.

Imagine a Ebase form containing an table named TBL.
The table contains the following fields: name, street, housenumber.

Create a Velocity template that looks like the following code.

Code: Select all

#foreach ( $row in $TBL )
   <p>Dear $TBL-name,</p>
   <p>Your new adres is&#58; $TBL-street $TBL-housenumber</p>
   <p>
         With kind regards,<br />
         The house movement
   </p>
   <div style="page-break-after&#58; always;">&nbps;</div> 
#end
 

Save this file in the velocityTemplates directory.

In Ebase create a HTML-field and use a FPL-script to fill this field with the content generated by the velocity template (see next code).

Code: Select all

set HTML_FIELD = formattemplate&#40;'<%TEMPLATE%>'&#41;;

Where <%TEMPLATE%> is the Velocity template file you just have created.

The result we be a page in your browser containing all the letters underneath eachother. The <div style="page-break-after: always;">&nbps;</div> entry ensures that every letter will be printed on a new page.

Please see the Ebase documentation for more information about creating and using Velocity templates.

[nftfi]

I'd like to try and use the velocity templates you mention, but I can't seem to find anything in the ebase documentation about them.

We're running ebase version 3.3 and tomcat, but I can't even find the velocityTemplates directory or any instructions on where to put it.

Can anybody be more specific about where the ebase documentation for these are?

Thanks

[Vircos]

I am sorry but I think I was wrong about the Ebase version.
I am 100% sure that Ebase version 3.3.1 supports Velocity.

I never used version 3.3.0 and I can not find anything in the change-log about Velocity for this version.

About the velocity templates: You have to create the templates, they are not standard. But with the Ebase documentation and the documentation at http://velocity.apache.org/ it is quite simple to create a template.

[Steve]

Velocity was introduced into Ebase in Version 3.3.1 fixpack 13022007.

[nftfi]

Well, I upgraded to version 3.4.0 and I have followed the instructions for using a velocity template but I think I must still be doing something wrong as all I get is a text field with the html inside it.

Do I need to save the field as a file and display it in some way?

Thanks

N

[Vircos]

You have to make your textfield display only and at the properties you have select the html property.

Now you should have a field that renders HTML instead of displaying it.

[Jignesh]

You can easily do this in PDF using Ebase.

I have created a sample Ebase form called MERGE_PDF_EXAMPLE which creates a new PDF document (a standard letter) for each customer record in a table, merges all the newly created PDFS into one single file (custom function merge_pdf) and displays it to the user so that it be printed/saved to produce different letter for each customer.

I have also included the source code for the custom functions so that you can extend the functionality as per your requirements.

You will need to be on Ebase 3.4.0 or above and will need the following

• 1) MERGE_PDF_EXAMPLE.xml
  • a. This contains a sample form which demonstrates how to achieve a PDF merge in Ebase.
  2) merge_pdf.java
  • a. Source code for the merge_pdf custom function.
  3) merge_pdf.class
  • a. class file for the merge_pdf custom function to be placed in <UFS>\UfsServer\tomcat\webapps\ufs\WEB-INF\classes\com\ebasetech\ufs\customfunctions\
  4) instring_last_position.java
  • a. Source code for the instring_last_position custom function
  5) instring_last_position.class
  • a. class file for the instring_last_position custom function to be placed in <UFS>\UfsServer\tomcat\webapps\ufs\WEB-INF\classes\com\ebasetech\ufs\customfunctions\

All the above files are available at http://www.ebaseftp.com/forum/forumfiles/merge_pdf.zip
Following are the steps:

• 1) Copy the merge_pdf.class file to <UFS>\UfsServer\tomcat\webapps\ufs\WEB-INF\classes\com\ebasetech\ufs\customfunctions
  • Note: If you do not have "com\ebasetech\ufs\customfunctions" folder structure then please create it
  2) Copy the instring_last_position.class file to <UFS>\UfsServer\tomcat\webapps\ufs\WEB-INF\classes\com\ebasetech\ufs\customfunctions
  
  4) Navigate to UFSSetup.properties file in <UFS>\UfsServer\tomcat\webapps\ufs\WEB-INF\classes\ and change the property for Ufs.pdfDirectoryName to Ufs.pdfDirectoryName=<UFS>/UfsServer/tomcat/webapps/ufs/generatedpdfs .
  • Note: You might have to create the generatedpdfs folder. This is because if you want a browser to display a file from the server, it has to be inside your webapp folder (ufs).
  5) Import the MERGE_PDF_EXAMPLE.xml from the Designer.
  
  6) Open the script GENERATED_PDFS in the PDF_MERGE folder and change 'C:/Ebase_versions/Ebase_3_4_0_Beta_3/UfsServer/tomcat/webapps/ufs_s/generatedpdfs' to the full path on the server for the generatedpdfs folder.
  
  7) Run the "MERGE_PDF_TEST" form from the "PDF_MERGE" project.

If you have any more questions do not hesitate to ask us

[nftfi]

I followed the instructions for the PDF Merge example and it worked perfectly, but when I ty it myself it creates one PDF and then throws an unexpected error.

The Catalina.out file shows this error:

Fri Oct 05 08:45:50: Unexpected error occurred - see server log
ExceptionConverter: java.io.IOException: The document has no pages.
at com.lowagie.text.pdf.PdfPages.writePageTree(PdfPages.java:118)
at com.lowagie.text.pdf.PdfWriter.close(PdfWriter.java:1303)
at com.lowagie.text.pdf.PdfDocument.close(PdfDocument.java:957)
at com.lowagie.text.Document.close(Unknown Source)
at com.ebasetech.ufs.printing.PdfDrawer.write(PdfDrawer.java:95)
at com.ebasetech.ufs.printing.model.PrintForm.writeToPDF(PrintForm.java:346)
at com.ebasetech.ufs.kernel.Form.printToPDF(Form.java:4426)
at com.ebasetech.ufs.validation.PDFPrintCommand.execute(PDFPrintCommand.java:120)
at com.ebasetech.ufs.validation.CommandProcessor.process(CommandProcessor.java:3932)
at com.ebasetech.ufs.validation.LoopNode.execute(LoopNode.java:237)
at com.ebasetech.ufs.validation.IfNode.execute(IfNode.java:171)
at com.ebasetech.ufs.validation.Node.execute(Node.java:173)
at com.ebasetech.ufs.validation.ActionScript.execute(ActionScript.java:148)
at com.ebasetech.ufs.validation.Event.execute(Event.java:474)
at com.ebasetech.ufs.validation.Event.execute(Event.java:416)
at com.ebasetech.ufs.kernel.FieldOnPage.runImmedFieldEvent(FieldOnPage.java:1792)
at com.ebasetech.ufs.kernel.FormsProcessor.continuePage(FormsProcessor.java:376)
at com.ebasetech.ufs.kernel.FormsProcessor.runForm(FormsProcessor.java:1232)
at formservlets.FormClient.runForm(FormClient.java:832)
at formservlets.FormClient.process(FormClient.java:566)
at formservlets.FormClient.doPost(FormClient.java:428)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
at formservlets.EbaseFilter.doFilter(EbaseFilter.java:207)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:233)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:257)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)


Any suggestions where I should look for the problem?

Thanks

[Jignesh]

Hmm...

Are you trying it in the same Ebase environment on which "MERGE_PDF_EXAMPLE" form worked?

[nftfi]

Yes, exactly the same environment.

[Jignesh]

Looks like it is having trouble reading the generated PDF(s).

Comment out the call to the custom function in your script, run your form and check if Ebase is generating the required number of PDF files at the correct location.

//set STATUS = merge_pdf(...);
//display 'generatedpdfs/NEW_FILE.pdf';

If the files are being generated properly open all the PDFs files and check to see if they have atleast one page.

[nftfi]

I don't think it even gets to the end of the loop thorugh the table, it creates the first pdf in the correct location and it looks exactly as I would expect, its a one page letter with all fields in the correct place.

Then the second time around the loop it does everything up until the pdfprint command, at which point the error is thrown and the second pdf is not generated.

[Jignesh]

Can you please paste the script that you are running here, so that i can have a look.

[nftfi]

//-----------------------------------------------------
// Script: GENERATE_PDFs
// Description: Used to initalise generate PDF for each
// table record, merge all of them and then
// show the merged pdf in the browser so that
// the user can print it.
// Author: Jignesh Vaducha
// Date: 1st OCT 2007
//-----------------------------------------------------

//settting up the print fields
set PRINT_DATE = formatdate($SYSTEM_DATE,'dd MMMMMMMMMM YYYY');
set PRINT_ASK_FOR = 'Goffynnwch am / Please ask for: '+tostring(NAME);
set PRINT_ASK_FOR_NUM = 'ffôn / tel '+tostring( TEL_NO )+' ffacs / fax '+tostring(FAX);
set PRINT_EMAIL ='E-bost / E-mail: '+tostring(EMAIL);
set PRINT_OUR_REF ='Ein Cyf / Our Ref.';
set PRINT_YOUR_REF = 'Eich Cyf / Your Ref.';

loop at table MOORINGS
exec MOORINGS_GET_LICENCE_FEE;
endloop

//generating the PDF
set FILE_NAMES = '';
//fetchtable MOORINGS;
if [$FETCH_COUNT >0]
loop at table MOORINGS
//exec MOORINGS_GET_LICENCE_FEE;
//set PRINT_TITLE_ENG = 'RE : MOORING '+tostring( MOORINGS-REF);
//set PRINT_TITLE_CYM = 'PARTH : ANGORFA '+tostring( MOORINGS-REF );
//if [substring(tostring(MOORINGS-FEE),instring('.',tostring(MOORINGS-FEE))+2,1) = '']
//set PRINT_FEE = '£'+tostring(MOORINGS-FEE)+'0';
//else
//set PRINT_FEE = '£'+tostring(MOORINGS-FEE);
//endif
//exec MOORINGS_RENEW_LICENCE;
//if [OUT_SUCCESS = 1]
pdfprint RENEWALS_LETTER save nodisplay;
//set FILE_NAMES = FILE_NAMES + $FILE_NAME;
set POS = instring_last_position('/',$FILE_NAME);
set FILE_NAMES = FILE_NAMES + '#' + substring($FILE_NAME, POS + 2 ,length($FILE_NAME));
//endif
endloop

set STATUS = merge_pdf(FILE_NAMES,'#','/usr/lib/ufs_dev/UfsServer/tomcat/webapps/ufs/generatedpdfs','NEW_FILE');
display 'generatedpdfs/NEW_FILE.pdf';
// exec MOORINGS_UPDATE_CONST_YEARS;

//set FILE_NAMES = '../../generatedpdfs/MERGE_PDF_EXAMPLE-SAMPLE_LETTER1-0000000002.pdf';
//else
//show RENEWALS_LABEL;
//hide SURE_BUTTON;
//hide NOT_SURE_BUTTON;
//show RENEWALS_BUTTON;
endif


This is the script I'm using, there are a few things commented out for testing purposes.

Thanks

Edited to correct a wrong line commented out during testing

[nftfi]

I don't think this is a problem with my script as if I add a normal print button to the form, the first time I click it it works, but the second time it throws the same error given above.

Can you suggest anything which might be affecting the print settings in a form.

I have other print buttons on similar forms and the same thing is now happening on them too.

I'm at a loss where to go from here, shall I raise a problem log on the ebase customer portal?

[Jignesh]

If clicking the default print button twice is causing the error then there is something else that is causing this error.

I think it would be advisable to log this on the Ebase customer portal along with an export of the form and the execution log.

[nftfi]

Thanks for your help, it wasn't the merge_pdf script causing the problem, I think it was a image in the pdf. For the moment I have removed the image and everything works fine.

Having got this working, I am trying to tidy up the entire process by making it delete the individual files once the merged pdf has been created.

I can see two possible ways of doing this, either by writing another function in java which loops through and delete's each file listed in the file_names variable.

Or I can keep a count of how many files are created and put the deletefile function in a loop.

Have you done anything like this already?

[Jignesh]

Yes!

There are a few custom function like "deletefile" which are shipped with the standard Ebase product, but the functions have not been added to the functions table (Tools -> Maintain Functions). They can be added as required. The functions are then immediately available for use.

Search for "deletefile" in the Ebase documentation and then click on the "Additional supplied custom functions" link which will explain how to use this custom function!

If you need any more information we are always more that happy to help

[dvanhussel]

I don't think this is a problem with my script as if I add a normal print button to the form, the first time I click it it works, but the second time it throws the same error given above.

Can you suggest anything which might be affecting the print settings in a form.

I have other print buttons on similar forms and the same thing is now happening on them too.

I'm at a loss where to go from here, shall I raise a problem log on the Ebase customer portal?

Just for your information, I think this is the result of the Ebase bug that is filed under reference number 331605. So it is not a bug in your script.