Related Information Examples & Tutorials

How To Create A Data Template

This document discusses creating a data template report to generate a data source for an existing Word Template. This is useful if you have Word Templates that you have been using to merge data from other databases or software programs. When you move to Collect!, you can create a report to output data in the correct format to continue merging data into your existing Word Templates.

This is a rather advanced topic. You should be familiar with merging data to Word templates. Also, there should be data entered in your Collect! database into the fields that you want to use in your report and you need to be familiar with creating reports in Collect!.

We will:

  • Create a data template report
  • Print and merge data
  • Make corrections as needed

Create A Data Template Report

To create a data template report, we need to know precisely what fields exist in the Word template so that we can create an accurate data source.

1. Copy the Word template into Collect!'s STYLES folder.

2. Open it with MS Word and make note of the exact name of the data source document that the template is designed to use. This may be a *.csv or a *.dat file, for instance. Make sure you note the correct extension!

tip.gif This may be found in the Mail Merge section, under Data Source.

3. In MS Word, view the template so that you can see the names of the merged fields. These are the Field Identifiers. We will use them to create the data template and determine the fields we need to put in our report body.

4. Start a new report named Legal Template that Starts On DEBTOR. In the Report Body, list the fields EXACTLY as they are spelled in the Word template, placing each identifier on a new line in the report, surrounded by quotes and followed by a comma. The entire list is enclosed in the @no line feed and @line feed commands.

Example:

//Identifiers
@no line feed

"Judge_Clerk",
"Judge_Address",
"Judge_Address2",
"Judge_City",
"Judge_State",
"Judge_Zip",
"Plaintiff",
"D1_ First_Name",
"D1_ Last_Name",
"D2_First_Name",
"D2_Last_Name",
"Account_Number",
"Prin_Balance",
"K_Interest_Rate",
"Case_Number"
@line feed

Next, we need to determine where this information is stored in Collect!. Some of the information is from the Debtor form, the Cosigner form and, for court details, the Attachment form works well. Information is also taken from the Interest Detail form.

List the Fields in the same order as above, using printable information codes. For the Cosigner, we will use variables to store the information needed. When we build the report, these lines should be at the top, before the list of identifiers.

So now, our report looks like this:

@tvarStr1* = ""
@tvarStr2* = ""

@de.cos no total max=1
@tvarStr1 = @co.na<fn>
@tvarStr2 = @co.na<ln>
@de.cos
//Identifiers
@no line feed

"Judge_Clerk",
"Judge_Address",
"Judge_Address2",
"Judge_City",
"Judge_State",
"Judge_Zip",
"Plaintiff",
"D1_First_Name",
"D1_Last_Name",
"D2_First_Name",
"D2_Last_Name",
"Account_Number",
"Prin_Balance",
"K_Interest_Rate",
"Case_Number"
@line feed

tip.gif There must be NO BLANK lines between the cosigner loop and the beginning of the identifiers. There MUST BE a BLANK LINE following the @line feed command.

For the Attachment fields, we loop through the Court attachment. Fields are trimmed and enclosed in quotes, followed by a comma.

@de.at no total where (@at.ty = court)
"@at.u15<",
"@at.u8<",
"@at.u9<",
"@at.u1<",
"@at.u11<",
"@at.u12<",
"@at.u17<",
"@at.i1<",
"@at.d1<",
@de.at

Next fields are taken from the Debtor form. Notice that the variables holding the Cosigner information have been inserted at the correct location.

tip.gif The fields must be in the same order as the identifiers so that each identifier points to the correct information.

"@de.cl",
"@de.na<fn>",
"@de.na<ln>",
"@varStr1<",
"@varStr2<",
"@de.ac<",
"@de.pr",

And finally, a field is included from the Interest Detail form.

"@id.air"

The entire list of field codes is enclosed in the @no line feed and @line feed commands.

So, the entire report body looks like this:

@tvarStr1* = ""
@tvarStr2* = ""

@de.cos no total max=1
@tvarStr1 = @co.na<fn>
@tvarStr2 = @co.na<ln>
@de.cos
//Identifiers
@no line feed

"Judge_Clerk",
"Judge_Address",
"Judge_Address2",
"Judge_City",
"Judge_State",
"Judge_Zip",
"Plaintiff",
"D1_First_Name",
"D1_Last_Name",
"D2_First_Name",
"D2_Last_Name",
"Account_Number",
"Prin_Balance",
"K_Interest_Rate",
"Case_Number"
@line feed

@no line feed

@de.at no total where (@at.ty = court) "@at.u15<",
"@at.u8<",
"@at.u9<",
"@at.u1<",
"@at.u11<",
"@at.u12<",
"@at.u17<",
"@at.i1<",
"@at.d1<",
@de.at
"@de.cl",
"@de.na<fn>",
"@de.na<ln>",
"@varStr1<",
"@varStr2<",
"@de.ac<",
"@de.pr",
"@id.air"
@line feed

Press F8 to save the report and select OPTIONS to configure the output.

Top of page.

Configuring Report Output

There are a few settings in the Report Options that are used to redirect data to an external template. We will set these to create the data in a data source file named exactly as the Word Template expects.

1. For Destination, select OTHER.

2. For File Name, enter the name exactly as you recorded it in the first step. The template has been copied to the STYLES folder. So this is where we would create the data source file. The path would be as follows.

.\styles\worddata.dat

3. In the Redirect To field, enter the exact name of your Word template. The path would be as follows.

.\styles\legaltemplate.doc

tip.gif If your Word template has a very long file name, please rename it. The Redirect To field will accept names and spaces up to 27 characters.

Top of page.

Print And Merge Data

Open a Debtor with information properly entered and print your legal template report. If everything is as expected, MS Word will open to display the template. Select the Merge button to view your data merged into the template.

At this point, you may find that there are "hidden" fields that the template is looking for. Make a list of the exact names of these fields.

Close the template and add the fields to your report body. You can add them to the end of the list of identifiers and to the end of the list of field codes as null spaces.

Now your report might look like this:

@tvarStr1* = ""
@tvarStr2* = ""

@de.cos no total max=1
@tvarStr1 = @co.na<fn>
@tvarStr2 = @co.na<ln>
@de.cos
//Identifiers
@no line feed
"Judge_Clerk",
"Judge_Address",
"Judge_Address2",
"Judge_City",
"Judge_State",
"Judge_Zip",
"Plaintiff",
"D1_First_Name",
"D1_Last_Name",
"D2_First_Name",
"D2_Last_Name",
"Account_Number",
"Prin_Balance",
"K_Interest_Rate",
"Case_Number",
"Other_Data",
"Other_Amt"
@line feed

@no line feed

@de.at no total where (@at.ty = court) "@at.u15<",
"@at.u8<",
"@at.u9<",
"@at.u1<",
"@at.u11<",
"@at.u12<",
"@at.u17<",
"@at.i1<",
"@at.d1<",
@de.at
"@de.cl",
"@de.na<fn>",
"@de.na<ln>",
"@varStr1<",
"@varStr2<",
"@de.ac<",
"@de.pr",
"@id.air",
" ",
" "
@line feed

The point here is to make sure that each identifier in the template is in your list of identifiers and in your list of field codes.

Now, print your report again and you should be able to view the template with the merged data.

Examine the output carefully to make sure that all of your information is present and is correct. You may need to pull your information from other fields rather than the ones you have chosen or to enter some of your data differently. This can only be determined when you can successfully view your template with the merged data from Collect!.

Top of page.

Summary

This document discussed creating a data template report to generate a data source for an existing Word Template. If you have Word Templates that you have been using, you can create a report to output data from Collect! in the correct format and continue merging data into your existing Word Templates.

The important point is to make sure that each identifier that you reference in your template is listed in your report in the identifier section and in the field code section, as shown in the examples above.

Top of page.

Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.

Please click this link to send us your comments: helpinfo@collect.org