Merge Data with a Template Using LC Output within a Process

Prerequisites:
- A database and tables to query with a valid datasource (named DEMO_DS below).
- LC Turnkey Install 8.2
- LC Workbench
- An XDP template to merge with the data.

Overview
Here is a screen shot of what the whole process looks like:  View image

The name and location of the XDP template and the data to merge is kept in the database so I can use this as an example of pulling from a database. When the process starts, the user will have to enter a formId and a customerId. The formId is used to look up the form name and location form a table and the customerId is used to look up the customer specific data from the database.

Here are two screen shots of my basic tables that you will have to copy unless you want to hard code a bunch of things.

Steps

1. Create the following global variables:
1. customerData as xml
2. customerId as String (Input & Required)
3. formId as String (Input & Required)
4. formLocation as String
5. formName as String
6. pdfOutput as Document
   
   View image
2. We need to extract the form name a location from the database. This next step is what you need to do to extract a single result from a database.
1. Insert the component "Foundation | JDBC | Query Single Row" as your start component.
2. Under "Input | Datasource Name" enter a "literal value" of your datasource name. I called mine "java:DEMO_DS".
3. Under "SQL Statement", click on the "..." button and enter a query that will extract the form location. Mine happens to be "SELECT formLocation, formName FROM demo.tblforms WHERE formId = ?".
4. Check the box, "Use Parameterized Query".
5. Enter the variable that you want to substitute for the question mark, which in my example is formId.
   
     View image
   
6. Click "OK".
7. Under "Data Mapping" click on the "..." button to map the results to useable variables.
8. Since our query stated that we want "formLocation" and "formName" we need to map the result to variables. Map "formLocation" to your "formLocation" global variable and do the same for formName (as per screen shot).
   
   View image
   
9. Click "OK" and now you have the XDP name and location from a table. You could hard code the name and value if need be.
3. Now you need to extract the customer data that will be merged with the XDP template form.
1. Insert the component "Foundation | JDBC | Query for Multiple Rows as XML" and attach to the last component.
2. Under "Input | Datasource Name" enter a "literal value" of your datasource name. I called mine "java:DEMO_DS".
3. Under "SQL Statement", click on the "..." button and enter a query that will extract the customer data. Mine happens to be "SELECT * FROM demo.tblcustomerdata WHERE customerId = ?
4. Check the box, "Use Parameterized Query".
5. Enter the variable that you want to substitute for the question mark, which in my example is customerId.
   
     View image
   
6. Finish by clicking "OK" and now you have a query that will obtain the XDP name and location from a table. You could hard code the name and value if need be.
7. Under "XML information" click on the "..." button to map the results to an xml structure.
8. The next dialog is a module used for creating XML from your query. The XML that you define here should match the schema that you created in the XDP form. This module can only create very basic and flat XML structure. Follow my screenshot below to create your basic XML structure that will be merged to the template.
   
   View image
   
   What you will end up with is an XML document with a root node called "main", a repeating node called "customer" and 9 customer related fields in the repeating node that is bound to the XDP template once we merge.
9. Click "OK" to close the "XML Document Info Editor" dialog.
10. Under "Output | XML document" select the XML "variable" that you created called "customerData".
11. Now you have all the data from the customer that we will merge into the form.
4. Next I added a default logger just to verify all my information.
1. Add the "Foundation | Variable Logger | log" component and attach it to the last component.
5. Next we need to create the PDF with the "Output" component that is only available if you installed "Output" with LiveCycle.
1. Drop the "Output | Output | generatePDFOutput" component and attach it to the last component.
2. Under "Input | Tranformation Format" select a "literal value" of PDF.
3. Under "Form" select the "variable" that you filled earlier called "formName".
4. In the "Context Root" select the "variable" that you filled earlier called "formLocation".
6. In the "Input Data" select the "variable" that you filled earlier called "customerData". 
   
   View image
   
1. In the "Output | PDF Output" section select the "variable" called "pdfOutput". This will put the resulting PDF into this variable.
7. Now you have everything you need in memory but you need to see the resulting PDF so we'll save it to disk so we can view it.
1. Drop the component "Foundation | File Utilities | Write Document" and attach it to the last component.
2. Under "Input | Pathname Pattern" enter a "literal value" of the file location where you want to save the PDF. In my case I used "C:\\BlogDemo\\HeyThere.pdf".
3. Under "Document" select the "variable" that you want to save to disk, "pdfOutput".
8. Select "Make Unique" so that you don't overwrite anything and you can see your results every time the process is run. It will create a new file every time.
   
   View image
   
9.  You're done! Run the process by right clicking on the process map (after you Activate it) and enter the two input parameters that correspond to the values that you're looking up in the database. In my example I would do:
   
   View image
   
10. And then I would have a PDF in my folder "C:\BlogDemo\HeyThere.pdf".