Quandis PDF Builder
We recommend reading through the entire document before starting the process.
Throughout this process, you will be required to use Microsoft Excel 2007 or later and Microsoft Word 2007 or later.
A PDF construction tool is available in the Quandis Version 3.0 and higher platform. The tool allows business users to utilize Microsoft Word to construct the layout and map the appropriate data elements to their appropriate location using Microsoft’s Mail Merge feature.
This guide will walk you through the construction of the word document, which includes best practices and data mapping. Once the Word document has been assembled, a few simple front end configuration steps are required by a Quandis PM/BA to complete the process.
Section 1: Layout
Having the proper layout of the PDF is important, especially when this PDF is being delivered to a client, or any other entity, such as a court.
Open a new Microsoft 2007 (or later) Word Document. If you have an existing document (Word, PDF, email, etc) with the desired text and layout, open it and copy and paste the entire contents into your new Word Document.
You will want to keep the existing document open to use as a reference point while you continue building your new document. If you do not have an existing document, you can simply begin typing and formatting your new document starting with the blank page.
When only simple blurbs of text such as paragraphs are being used in the PDF, simply type out the paragraphs and/or sentences within the Word document, leaving placeholders for mapping of the data elements which is outlined in Section 3. Here is an example:
Dear <<First Name, Last Name>>,
We have received your letter requesting a Short Payoff for the property located at <<Property Address>>, in the amount of <<Payoff Amount>>.
However, in more complex PDFs, where multiple data elements will be mapped in various locations throughout the PDF and need to be properly aligned, the following instructions should be used.
It is recommended that you break the PDF out into sections, which will assist with positioning text and fields appropriately. For example, the following screenshot should be broken out into three sections; The header information should be broken out into its own section, with sections I & II in separate sections as well.
To ensure text and data placement consistency when building these sections, utilize Word’s table feature and configure each cell appropriately. We will need to calculate the proper size of the table.
For the first section in the example provided above, we will need 8 rows and 9 columns (9×8). This is calculated by counting the number of rows in the section, then finding the highest number of elements found in any given row of the section, as shown using red numbers in the next two images:
You may find that elements that will remain static in size can be combined, such as columns 4 & 5, as well columns 6 & 7. However, we will keep them separated for this example. In the Word Ribbon (Toolbar for Office versions prior to 2007), select “Insert”, then “Table”, and select the count of rows and columns that were just calculated (9 X 8). The maximum size table that Word allows to be inserted is 10 X 9, however, additional rows and columns can be added afterward.
We can now begin configuring the table by resizing rows and columns, as well as splitting and merging cells.
To Resize Rows and Columns
- Place your cursor over of the edge of the Cell Box you would like to resize (the mouse arrow icon will change to the resize icon),
- Click and hold while dragging to create the desired size.
To Split Cells
- Right-click on the cell that needs to be split.
- Click “Split Cells”.
- Enter the number of columns and/or rows that should be contained within that cell.
To Merge Cells
- Highlight by clicking on a cell that should be merged.
- While holding down the left mouse button, drag in the direction of the cells you would like to be me merged together.
- Right-click on the highlighted cells, and select “Merge Cells”.
In the example below, the first row was doubled in height, and all cells were merged together, then the cells in the remaining rows were resized to fit the text for the first section of the sample PDF.
Logos and Static Images
Now we can begin entering the static text in the appropriate cells. Use Word’s text formatting (size, font, alignment, etc) to match the sample PDF.
Static images can also be embedded. For example, if a logo was needed in the top-left area of the page, the first row could be split into two cells, and the image simply copied and pasted, then re-sized to fit appropriately.
Now we will remove all borders from the table, and underline the appropriate cells.
We will now remove all borders from the table, and underline the appropriate cells.
Underlining can be handled two ways:
1) If the width of the underlining should match the width of the text, then highlight the cell and select the underline icon (“U” in 2007 and later versions of Word).
2) If the width of the underlining should be static, right-click on the cell and select “Borders and Shading”, then select the bottom underline option.
Even though the borders are no longer displayed, re-sizing rows, columns and cells, as well as splitting and merging cells can still be conducted.
In some scenarios, such as comparable grids in BPOs, borders will be required. An example of this is provided below.
Mastering the process of laying out the Word document to match the PDF sample may appear to be challenging, but with practice, creating new PDF templates and managing existing ones will become routine.
Section 2: Create the QBO Document Template
As soon as the Word document has been assembled, a Document Template must be created in the QBO application. The Document Template will be tied to a database query that returns the data elements which are then mapped to their appropriate locations throughout the PDF, which is detailed in the next section. If the database query already exists, this process will take only a few minutes. However, if there is no query readily available, assembling one to meet the needs of the data requirements can take anywhere from minutes to hours, depending on the data needed.
Within the QBO application, navigate to Design >> Documents:
In the “Document Templates” Panel, select “+ New Template” from the Options Dropdown menu:
For the purpose of this document, only the Document Template, Applies To, Method, Plugin and Transform data entry fields will be used. All other fields can be ignored. Enter the name of the Template and select what object this Document Template will apply to. Business logic can be applied to determine what your Template will apply to.
Examples:
•If the document will be a Foreclosure Referral package, then “Applies To” will be set to “Foreclosure”.
•If the document will be a PDF of BPO results, then “Applies To” will be set to “Valuation”
Select “AsposeWord” from the “Plugin” dropdown list. A field for “Transform” will be presented. This is where the completed Mail Merge Word document will be uploaded.
The values in the “Method” dropdown will generally match the value in the “Applies To” dropdown. Once a Method is selected, an “Operation” dropdown will be presented. “Select” will be the appropriate selection from this dropdown, except when a custom query is needed, as described above. Once an Operation selection is made, a “Parameters” text box will be presented, this will be left blank.
Click “Save”
Creating the QBO Document Template is a process that is currently handled by a Quandis Project Manager/Business Analyst.
Section 3: Data Mapping
The mapping of the data will utilize the Mail Merge functions of Microsoft Word, which obtains its Merge Fields from an Excel spreadsheet produced by the QBO application.
In order to produce the Excel spreadsheet that will be referenced by the Word document, you will need to obtain an ID for a valid test case.
There are a couple of ways to obtain this information:
1. Contact your Quandis Representative
2. Follow these steps:
a. Navigate to the Dashboard of the object you are working with, in this example, you would navigate to Home >> Foreclosure
b. Search for a file that exists in the system using the “Search” box in the top right of the screen, or click on any value in the “Summary” Panel that returns valid data.
c. Click on the hyperlink to access the case.
d. In the URL, the numbers following “ID=” represent the ID for this case. Copy these numbers, as they will be used to generate test data for your Document Template.
Navigate back to the Document Templates page (Design >> Documents). In the “Summary” Panel click on the link for the object to which your new Template applies. In our example above, it is “Foreclosure”.
The “Document Templates” Panel will refresh and display all Templates that apply to that object. Click on the link for the new Template that you created when following Section 2. In our example above, we created “Foreclosure Referral Package”.
In the Options menu of the “Summary” Panel, select “Create Test Data”.
In the dialogue window that opens, paste the case ID obtained in the prior steps and click “Generate”.
This will produce the Excel spreadsheet required for the Mail Merge function.
Open the spreadsheet and select File >> Save As, then select either “Excel Workbook” or “Excel 97-2003 Workbook”, and save it to a location you will easily remember (for example, “Desktop”).
Open the Word document that was assembled in the first section (Layout). Within the “Mailings” menu, click on “Select Recipients”, then “Use Existing List…”.
In the dialogue window that opens, browse to the location where you saved the Excel Spreadsheet file (our example used “Desktop”), click on the file and click “Open” (or you can simply double-click on the file).
In the next dialogue window that opens, ensure that the checkbox for “First row of data contains column headers” is selected, then click on “OK”.
In the Word Document, Locate the first section that was reserved for the Merge Field and delete the text that was entered to reserve that space.
Dear <<First Name, Last Name>>,
Dear|,
Leaving the cursor at its current location (in the example above, it would be prior to the comma following “Dear “), select “Insert Merge Field” from the “Mailings” menu, and locate the appropriate field to be populated. Note: Determining the appropriate field may require referencing the Excel spreadsheet and locating sample data within it. Your Quandis Representative will also be able to provide appropriate field mapping.
The text will now contain the Merge Field you selected, wrapped in “<<>>”.
Dear <<SoldToName>>,
Continue working through the document, updating all reserved sections with their appropriate Merge Fields. When working with tables, as described in Section 1, you will simply insert the Merge Field into the appropriate cell. Additional formatting may be needed, as inserting the Merge Field may resize the cell/row/column. A few examples of cell formatting have been provided below.
Once all Merge Fields have been inserted, and all formatting applied, save the final version as a “Word 97-2003 Document” (file extension “.doc”, NOT “.docx”) and upload it to the Document Template in the QBO application. As with the Excel spreadsheet, be sure to save it to a location that is easy for you to remember, for example, “Desktop”.
Navigate to the Document Template that was previously created, and in the “Summary” Panel, select Options >> Edit
Select the Upload icon in the Transform field.
Browse to the Word Document’s location (our example used “Desktop”), click on the file, then click “Open” (or simply double-click on the file).
When the file finishes uploading, click “Save”.
A test can be run by selecting “Generate” from the “Options” menu in the “Summary” Panel.
This concludes the Quandis Aspose Word PDF Builder instructional guide. Should you have any questions, please contact your Quandis Representative.