Tutorial 3

In this example we’ll use the Northwind database to generate invoices. The database contains several orders that have not been shipped yet. We'll make an invoice for each order and gather all the invoices in a separate document for each customer.
We'll use a prepared PDF Form, named invoice.pdf, to generate our invoices. Open the form in Acrobat. It’s located in the samples\Tutorial 3 folder.
Notice that all fields have been outlined and labeled with their name. At the top of the page, we'll enter the name and address of the supplier and the customer, below that the invoice properties(such as the order number and invoice date), with the actual contents of the order listed in the bottom table. Each row in the table has a field for quantity, description, price, discount and subtotal. Every field in a row has a name that is post fixed with the index of the row, starting at 0. This will help us to fill the fields with data later on.
Step 1: Gathering data
Now that we know what data we need to make an invoice we can start setting up the merge project. We want to create a separate document for each customer, so first we insert a data source(customers with open orders) and then an output document.
Create a new project and select Data in the Insert toolbar.
Name it Customers with orders.
Select Access database as the data source type and click  and browse for the Northwind database. (Samples\Tutorial 3\Nwind.mdb)
Select Specify SQL statement and type in the following statement:
SELECT * FROM Customers WHERE CustomerID IN (SELECT Cus­tomerID FROM Orders WHERE [ShippedDate] IS NULL)
We will not go into the details of this statement here. It's function is to select all customers that have orders that haven’t been shipped (ShippedDate is not set).
Your variaDoc window will look something like this:
Step 2: Setup an output document
Add an output document below the data source.
Set the export method to Save to disk.
Enter a base folder. This is the folder that will hold the generated PDF documents. We'll use the following expression:
$[PROJECTPATH]\Out
For the file name we'll use an expression to give each generated PDF file a unique name.
In the database each customer has a unique id, we can use that to name the files.
Enter the following expression:
$[Customers with orders:CustomerID].pdf
Finally, check the Overwrite existing files box.
The screen should look like the following figure:
Step 3: Setting document information
Let’s add some descriptive information to the document.
Enter the following expression in the Subject field in the Document Information pane.
Invoice for $[Customers with orders:CompanyName]
Click to see the result.
Step 4: Getting orders for the customer
The merge project is now setup to generate a document for each customer. The next step is to add an invoice for each order. This means we'll first have to get the orders from the database.
Add a new Data node below the output document and name it Orders.
Set up the connection string as outlined earlier in Step 1.
Select Specify SQL statement.
Enter the following SQL statement
SELECT * FROM ORDERS WHERE ShippedDate IS NULL and CustomerID="$[Customers with orders:CustomerID]"
This statement will fetch all unshipped orders for the current customer.
Step 5: Add the invoice template
We now have a merge project that selects a customer and all their open orders. We want to create an invoice for each order. Let’s add the invoice template presented earlier and start filling it in.
Click Page(s) in the Insert toolbar and use the Add Page(s) Wizard to add the invoice.pdf form in Samples/Tutorial 3. Expand the page by clicking on the + icon.
You'll see a number of fields, some of which we have data for now, and some that we will handle later.
Enter the following expressions for the listed fields:
Supplier
The company's name and address. We'll enter some example text:
variaDoc
P.O.BOX 1121
5900 BC Venlo
The Netherlands
Customer
The customer's address, currently stored with the order in the database:
$[Orders:ShipName]
$[Orders:ShipAddress]
$[Orders:ShipPostalCode] $[Orders:ShipCity]
$[Orders:ShipCountry]
OrderID
The order's unique number.
$[Orders:OrderID]
InvoiceDate
To use today’s date as the Invoice Date, we’ll use some VB.NET code.
{ DateTime.Today.ToLongDateString() }
DateDue
The date the customer is supposed to have paid for the order. Let’s assume this is 14 days after the invoice date and use use VB.NET to calculate the date.
{ DateTime.Today.AddDays( 14 ).ToLongDateString() }
Shipment
So far we’ve only used text fields. The Shipment field is a drop down menu. Drop down lists have a fixed set of options. In this sample the options match the list of shippers in the database, allowing quick selection of the statement.
Click Specify an expression.
Enter the following expression:
$[Orders:ShipVia]
Remarks
Each invoice can have a remark. Let’s tell the customer when the order was placed.
This order was placed on { String.Format("{0:d MMMM yyyy}",$[Orders:OrderDate]) }
Step 6: Master/Detail
An invoice is a classic example of a master/detail document. The master, order, contains items, the details. To show this kind of relation in a single page, use a Field array.
A Field array allows you to dynamically index fields and set their value. Let’s do this for our invoice.
First we need to get the items for this order form the data base.
Insert a new Data node.
Name it Order Details.
Set it up as shown in Step 1.
     
If you use the same database file or connection string for more than one data source, consider using a merge parameter (e.g. $[myData]) instead of configuring each data source manually. This stores the connection string or file name in one place, which is easier, helps improve maintainability of your projects and simplify deployment to a server.
Enter the following SQL statement:
SELECT [Order Details].*, Products.ProductName FROM [Order Details] 
INNER JOIN [Products] ON Products.ProductID=[Order Details].ProductID WHERE OrderID=$[Orders:OrderID]
We won’t delve into the details of this SQL statement here, suffice it to say that this statement provides the order details for a specific order.
Add a Field array below the data source and name it Quantity.
     
A field array has two expressions: The first must resolve to a field name, and the second is the value assigned to the field.
In our invoice template we've named the fields in the quantity column qty0 through qty9. qty0 is the field that will hold the quantity for the first item, accomplished by entering the following expression as the field name:
qty$[Order Details:#]
     
This expression uses the special field #, present in every data source. It resolves to the index of the current record, starting at 0.
The first record in Order Details has index 0 and therefore the field name will resolve to qty0 for the first row. For the next row qty1 is used and so on.
Assign a value to the field with the following expression:
$[Order Details:Quantity]
For description, price, discount and subtotal we have to do something similar.
Description
Add a new field array below the Order Details data source.
Name it Description.
Set the field name expression to:
description$[Order Details:#]
Set the field value expression to:
$[Order Details:ProductName]
Price
Add a new field array below the Order Details data source and name it Price.
Set the field name expression to:
price$[Order Details:#]
The price is in US dollars. Use the VB.NET function String.Format to apply the correct formatting to the value.
Set the field value expression to:
{String.Format("${0:#,##0.00}", $[Order Details:Unit­Price])}
Discount
Add a new field array below the Order Details data source and name it Discount.
Set the field name expression to:
discount$[Order Details:#]
The discount is a floating point number that we want to format as a percentage using a different format string.
Set the field value expression to:
{ String.Format("{0:#0%}", $[Order Details:Discount]) }
Row total
At the end of each row a subtotal will be calculated as follows:
Quantitiy * Price * (1-Discount)
The result of this calculation is in US dollars so we'll need to re-apply formatting.
Insert a new field array and name it Row total.
Set the field name expression to:
subtotal$[Order Details:#]
Set the field value expression to:
{String.Format("${0:#,##0.00}",  $[Order Details:Quan­tity] * $[Order Details:UnitPrice] * (1 - $[Order Details:Discount]) ) }
Step 7: Calculating totals
We've filled in the order details. Now we need to calculate the order total. The Orders data source does not contain all the required data to calculate this for every page, so wd add another data source to the page to do so.
Add a new data source to the page and name it Totals.
Set it up to connect to the northwind database as shown in the step ‘Gathering data’.
Enter the following SQL expression:
SELECT SUM(Quantity * UnitPrice * ( 1 - Discount ) ) AS RowTotal FROM [Order Details] WHERE Orde­rID=$[Orders:OrderID]
This statement calculates the total for each item and summarizes all the items in a single record with a single value named RowTotal.
Subtotal
Add a new field array to the Totals data source and name it Subtotal.
Set the field name expression to:
subtotal
Set the field value expression to:
$[Totals:SubTotal|$#,##0.00]
Freight
Add a new field array to the Totals data source name it Freight.
Set the field name expression to:
freight
Set the field value expression to:
$[Orders:Freight|$#,##0.00]
Tax
On the invoice we'll specify the amount of value added tax (VAT) due for this order.VAT is calculated as a percentage of the subtotal and the freight costs and is subject to change, so we’ll declare it as a parameter.
Select the project in the project tree (the top most item)
Create a new parameter and name it Tax.
Set its value to 0.19. This represents a Tax rate of 19%.
Make sure the Constant checkbox is checked.
Switch back to the Totals data source.
Add a new field array and name it Tax.
Set the field name expression to:
tax
The tax is calculated as follows:
(SubTotal + Freight) * Tax
Set the field value expression to:
{ String.Format("${0:#,##0.00}", (CDec($[Totals:SubTotal]) + CDec($[Orders:Freight]) * CDec($[Tax])) ) }
Grand total
Finally we'll calculate the grand total.
Add a new field array to the Totals data source and name it Grand Total.
Set the field name expression to:
total
The tax is calculated as follows:
(SubTotal + Freight) * (1 + Tax)
Set the field value expression to:
{String.Format( "${0:#,##0.00}", CDbl($[Totals:SubTotal]) + CDbl($[Orders:Freight]) * (1+CDbl($[Tax])) ) }
Step 8: Clean up fields
The project is almost complete. The final step is to clean up the fields in the page. Since we are using the fields named qty, description, price, subtotal, freight, tax, and total in field arrays we can safely remove them from the page. Select each of the fields and hit the Del key or select Delete from the toolbar.
Step 9: Run the merge
Start the merge by clicking Merge on the toolbar. It should complete without errors in a couple of seconds. If an error occurred resolve it using the error items in the merge log at the bottom of the screen. Double-click on an error to jump to the item that caused the problem. If the merge completed successfully browse to the folder you choose for the output folder. You'll see a set of documents. Open some of them to see the result.
Accept

variaDoc uses cookies improve the website experience and to personalize our communications. By continuing to use this site you agree to these cookies. Read more