Mail-merge file contents for bills and order acknowledgements

There are four possible mail-merge output formats for bills and acknowledgements, named MMERG1, 2, 3 and 4. MMERG4 is the most recently designed format and we recommend that new QuickFill users choose this format. The MMERG1, 2 and 3 formats are increasingly expansive: the MMERG2 format contains everything that the MMERG1 format has plus additional fields that make it possible to do a postal presort on the file. The MMERG3 format contains everything that the MMERG1 and MMERG2 formats have plus it has all of the address fields split out into their individual components.

MMERG1 format

We supply a sample Microsoft Word mail-merge template that uses the MMERG1, 2 or 3 format. It is named Bill123.dot for bills and Ack123.dot for acknowledgements; you will find them in your QuickFill program directory. Backup read-only copies of the templates are located in the Templates directory within the QuickFill program directory, just in case you want to revert to the original layout.

This field contains a sequential record number.

The entries in these fields depend on whether you selected the 'Print company name on bills' field on the 'Companies' definition screen. If you left the 'Print company name on bills' field blank, these first five fields will be blank. If you checked this field, these fields will contain the data you entered in the company Name field; the two-line Address field; and the city, state, zip, and country fields

This field always contains the phone number you entered in the 'Phone' field on the 'Companies' definition screen.

Whether you get entries in the next three fields depends on whether you selected the 'Print credit card info block on bill' field on the 'Billing Packages' definition screen. If you left the credit card block field blank, these three fields will be blank. If you selected the 'Print credit card info block on bill field', you'll see the following in these three fields:

The CCBLOCK2 may not contain exactly the same card names shown above. The ones that are displayed depend on which merchant ID numbers you filled in on the Companies definition screen.

All three CCBLOCK fields are blank for order acknowledgments.

What you see in these fields depends on the specific order you're billing, of course. It also depends on your entry in the Send to ship-to? field on the Billing series definition screen.

These six fields match the mailing label image you see when you use 'Lookup' to get details on a customer.

If you leave the 'Send to ship-to' field blank, QuickFill produces the address of the "bill-to" customer for the order you're billing. If you select the 'Send to ship-to', QuickFill produces the address of the "ship-to" customer for a two-party subscription. (For a group subscription, QuickFill always addresses bills to the bill-to customer, regardless of whether you selected the Send to ship-to field, and Agency orders are always billed to the agency address you filled in on the 'Agencies' definition screen.)

This field contains the code you entered on the 'General' tab of the 'Publications' screen.

The entries in these fields come from the name you entered in the 'Publication name' field of the'General' tab of the 'Publications' screen. If you do not want the publication name in these fields, clear the Print publication name fields on the 'Billing series' screen.

This field contains the date in the Date the bills field that was on the update screen at the time you ran the update. The format of the date is specified by your entry in the 'Date' format field on the 'Preferences' screen.

This field contains the order number of the order you're billing.

In this field is the number from 1 through 8 of your billing effort. (The first bill you send is effort 1, the second effort 2, and so forth.) For cancel bills, this field will contain "0."

If the order you're billing has a purchase order number, you'll see "P.O. Number :" in this field. If it doesn't have a purchase order number, this field is blank.

This field contains the purchase order number if there is one for the order you're billing. If there's no number, this field is also blank.

This field contains the number of issues followed by the number of copies for example "12 issues, 2 copies." If either quantity is 1, then it is omitted. If both are 1, then only the words "1 copy" are printed.

The wording of the term is changed slightly if you indicated that the publication is something other than a periodical on the 'General' tab of the 'Publications' screen. For services and memberships, the number of issues is never printed. For conferences the word "issues" is replaced with "sessions" and the word "copies" is replaced with "persons." For memberships the word "copies" is replaced with "members." For online services the word "copies" is replaced with "users."

The price comes from the offer for the order you're billing. This price, which is determined by the term and quantity, does not include shipping charges, sales tax, or credits. The entry in this field is, of course, a dollar amount. All dollar amounts are right justified with a field length of 11 for negative numbers (11th position is "-") and a field length of 10 for positive numbers (with no "+"). All dollar amounts also have a decimal point in the 8th position.

In most cases, the entry in this field comes from the shipping code descriptions on the 'Shipping' tab of the 'Publications' screen.

The exceptions:

• The order you're billing is a group order and you've used more than one shipping code with a nonzero shipping charge. In this case, this field will contain the entry "Shipping." (That's because there's no room to specify more than one type of shipping for example, "airmail" and "first-class.")

• The order you're billing uses a shipping code for which you've provided no description. Again, in this case, this field will contain the entry "Shipping."

This field contains the shipping charges (formatted as a dollar amount). If there are no shipping charges, the field contains six blank characters, followed by N/C (that is, " N/C").

If you're charging sales tax for the order you're billing, this field contains "Tax." If not, the field is blank.

If you're charging sales tax for this order, this field contains the tax, formatted as a dollar amount. If there's no sales tax, this field is blank.

This field contains the subtotal of the price, shipping charges, and sales tax, formatted as a dollar amount.

If there's a credit, or partial payment, for the order you're billing, this field contains "Credit." If not, the field is blank.

If there's a credit for this order, this field contains the credit, formatted as a dollar amount. If not, this field is blank.

This field contains the subtotal amount minus the credit amount. The total amount is formatted as a dollar amount with a leading dollar sign ($).

The entries in these four fields depend on what you entered in the 1st message field on the Billing series definition screen. Each of the four fields can be 40 characters long. (And each line can be used as a separate message.)

The entries in these four fields depend on what you entered in the 2nd message field on the Billing series definition screen. Each of the four fields can be 40 characters long. (And each line can be used as a separate message.)

This field contains the "expire issue" date of the order you're billing.

This field contains the number of copies this order receives.

For group subscriptions these fields contain the names of the ship-to customers. If there are more than six names then only five names are listed and the words "and others" are in the last field. For all other subscriptions these six fields contain the complete address of the ship-to customer.

If you entered data in the 'Checks payable to' field on the 'General' tab of the 'Publications' screen, this field contains "Check payable to:". If not, this field is blank. (The spelling of "check" is changed to "cheque" if the bill is addressed to Canada, England, Scotland, Wales, United Kingdom, or Great Britain.) For order acknowledgement files this field will be blank.

This field contains the entry you made— if any— in the 'Checks payable to' field on the 'General' tab of the 'Publications' screen. For order acknowledgement files this field will be blank.

If you made an entry in the 'Federal tax ID number' field on the 'Companies' definition screen, this field contains "Fed tax id # :"; if not, this field is blank.

This field contains the entry you made— if any— in the 'Federal tax ID number' field on the 'Companies' definition screen.

This field contains whatever phone number you entered for the customer to whom you're mailing the bill. This field is useful if you use telephone collections for one of your billing efforts. You can create a mail-merge document with the telephone number. Then you can give this document to your telephone collection people as a lead.

If you entered a salutation in the 'Salutation' field of the customers record, you'll see that salutation in this field.

If you left the salutation field blank and entered a last name for the customer, QuickFill enters in this field the prefix you entered in the Prefix field of the customers record, plus a space and the last name for example, "Mr. Smith".

If you left the salutation and the prefix fields blank, QuickFill enters "Subscriber" in this field.

If you left the salutation, prefix, and last name fields blank, QuickFill enters "Subscriber" in this field.

*Indicates customer and addressee fields that are either blank or contain zero when a bill is for an agency subscription.

MMERG2 format

If you select the MMERG2 format code in your billing package definitions, each record in your mail-merge file will include all of the MMERG1 fields plus the following ones:

This field contains the information entered on the first line of the address for the customer to whom the bill is being sent.

This field contains the city in which the customer to whom the bill is being sent lives.

This field contains the state in which the customer to whom the bill is being sent lives.

This field contains the zip or postal code for the customer to whom the bill is being sent.

This field contains the delivery point code for the customer to whom the bill is being sent.

This field contains the carrier route for the customer to whom the bill is being sent.

This field contains the country code for the country in which the customer to whom the bill is being sent lives.

This field contains the CASS certification date if there is one for the address of the customer you're billing. If the customers CASS certification date is not in QuickFill, this field is blank.

This field contains whatever cell phone number you entered for the customer to whom you're sending the bill.

This field contains whatever fax number you entered for the customer to whom you're sending the bill. This field is useful if you fax bills for one of your billing efforts.

These fields contain whatever e-mail addresses you entered for the customer to whom you're sending the bill. These fields are useful if you use e-mail to send bills for one of your billing efforts.

This field contains the customer number of the customer to whom the bill is being sent. If a bill is for an agency subscription, this field contains the agency code for the agency from which you received the subscription instead of a customer number.

This field contains the billing series code for the billing series to which the order belongs.

This field contains the billing package code associated with the billing series and effort number of the bill being sent to the customer.

*Indicates customer and addressee fields that are either blank or contain zero when a bill is for an agency subscription.

MMERG3 format

If you select the MMERG3 format code in your billing package definitions, each record in your mail-merge file will include all of the MMERG1 and MMERG2 fields plus the following ones:

This field contains the company for the customer to whom the bill is being sent.

This field contains the department for the customer to whom the bill is being sent.

This field contains the prefix for the customer to whom the bill is being sent.

This field contains the first name of the customer to whom the bill is being sent.

This field contains the last name of the customer to whom the bill is being sent.

This field contains the suffix for the customer to whom the bill is being sent.

This field contains the title for the customer to whom the bill is being sent.

This field contains the information entered on the second line of the address for the customer to whom the bill is being sent.

This field contains the name of the country in which the customer to whom the bill is being sent lives.

This field contains L or R to indicate whether the postal code should go to the left or to the right of the city name for the country in which the customer to whom the bill is being sent lives. (Please note, this field will contain "R" for the United States of America and Canada. For agency subscriptions, this field will be blank.)

This field contains the entry in the Sex field for the customer to whom the bill is being sent.

These fields contain the entries in the demographic code fields (A, B, C, D, 1, and 2) for the customer to whom the bill is being sent.

This field contains up to eight digits, each of which represents one of the selection flag check boxes on the customer address screen.

This field contains the external customer ID, if any, of the customer to whom the bill is being sent.

This field contains the entry in the user ID field for the customer to whom the bill is being sent.

This field contains the entry in the password field for the customer to whom the bill is being sent.

*Indicates customer and addressee fields that are either blank or contain zero when a bill is for an agency subscription.

MMERG4 format

The MMERG4 format arranges the billing information into a series of 9 lines, with a type field, a description field and an amount field for each line. Unlike the MMERG1-3 formats there is no specifically identified field for the price, the tax, and the shipping. Instead there is simply an array of descriptions and amounts, which if arranged in columnar fashion will present all of the details. You do not need to worry about shifting fields up to avoid blank lines when the tax or shipping is not present.

The contents of each line is not fully predictable— it will depend on whether tax or shipping is included, on whether the order is partially paid, and on whether the subscription is for a single copy or multiple copies. Because of all of these variations you must include all 9 offer lines in your  document or risk cutting off information and making the bill incomprehensible.

Each line of the price block contains a type code that indicates what is on that particular line. This may be useful if you need to programmatically pick out the lines containing the price, tax or shipping amounts from the other lines.

We supply a sample Microsoft Word mail-merge template that uses the MMERG4 format. It is named Bill.dot for bills and Ack.dot for acknowledgements; you will find them in your QuickFill program directory. Backup read-only copies of the templates are located in the Templates directory within the QuickFill program directory, just in case you want to revert to the original layout.

This field contains a sequential record number.

This field contains the code you entered on the publication definition general tab.

This field contains the billing series code for the billing series to which the order belongs.

This field contains the billing package code associated with the billing series and effort number of the bill being sent to the customer.

What you see in these fields depends on the specific order you're billing, of course. It also depends on your entry in the 'Send to ship-to' field on the 'Billing Series' definition screen.

These six fields match the mailing label image you see when you use Lookup to get details on a customer.

If you leave the 'Send to ship-to' field blank, QuickFill produces the address of the "bill-to" customer for the order you're billing. If you select the 'Send to ship-to field' QuickFill produces the address of the "ship-to" customer for a two-party subscription. (For a group subscription, QuickFill always addresses bills to the bill-to customer, regardless of whether you selected the Send to ship-to field, and Agency orders are always billed to the agency address you filled in on the Agencies definition screen.)

Each of these fields can have a maximum of thirty characters.

The entries in these fields come from the name you entered in the 'Publication name' field of the 'General' tab of the 'Publications' definition screen. If you do not want the publication name in these fields, clear the Print publication name fields on the 'Billing Series' screen.

This field contains the date in the 'Date the bills field' that was on the update screen at the time you ran the update. The format of the date is specified by your entry in the 'Date' format field on the 'Preferences' screen.

This field contains the order number of the order you're billing.

In this field is the number from 1 through 8 of your billing effort. (The first bill you send is effort 1, the second effort 2, and so forth.) For cancel bills, this field will contain "0."

In these fields are codes that indicate the contents of the corresponding description and amount fields. The codes are as follows: 1 = term description, 2 = shipping, 3 = tax, 4 = dotted line, 5 = subtotal, 6 = credit, and 7 = amount due.

In these fields are the descriptive text for the corresponding type code field.

In these fields are the dollar amount for the corresponding description field.

This field contains the subtotal amount minus the credit amount. The amount due is formatted as a dollar amount with a leading dollar sign ($). The decimal point is in the eighth position.

This field contains the number of issues for this order.

This field contains the number of copies this order receives.

In most cases, the entry in this field comes from the shipping code descriptions on the 'Shipping' tab of the 'Publications' screen.

The exceptions:

• The order you're billing is a group order and you've used more than one shipping code with a nonzero shipping charge. In this case, this field will contain the entry "Shipping." (That's because there's no room to specify more than one type of shipping for example, "airmail" and "first-class.")

• The order you're billing uses a shipping code for which you've provided no description. Again, in this case, this field will contain the entry "Shipping."

This field contains S for single subscriptions, 2 for two-party subscriptions, and G for group subscriptions.

This field contains the amount paid— if any— for this order.

This field contains the date of the most recent payment for this order.

This field contains the type of payment (e.g., cash or check, Visa, etc.) for this order.

If the order was paid by credit card, this field contains the credit card number used.

If the order was paid by credit card, this field contains the last four digits of the credit card number used.

If the order was paid by credit card, this field contains the expiration date for the credit card number used.

The entries in these two fields depend on what you entered on the first line of the 'Document descriptors' field on the Format tab of the 'Billing Series' definition screen.

The entries in these four fields depend on what you entered in the 1st message field on the 'Billing Series' definition screen. Each of the four fields can be 40 characters long. (And each line can be used as a separate message.)

The entries in these four fields depend on what you entered in the 2nd message field on the 'Billing Series' definition screen. Each of the four fields can be 40 characters long. (And each line can be used as a separate message.)

This field contains the "expire issue" date of the order you're billing.

For group subscriptions these fields contain the names of the ship-to customers. If there are more than six names then only five names are listed and the words "and others" are in the last field. For all other subscriptions these six fields contain the complete address of the ship-to customer.

The entries in these fields depend on whether you selected the 'Print company name on bills' field in the 'Companies' definition screen. If you left the 'Print company name on bills field' blank, these first five fields will be blank. If you checked this field, these fields will contain the data you entered in the company 'Name' field; the two-line Address field; and the city, state, zip, and country fields.

This field always contains the phone number you entered in the 'Phone' field on the 'Companies' definition screen.

This field contains the entry you made— if any— in the 'Checks payable to' field on the 'General' tab of the 'Publications' screen. For order acknowledgement files this field will be blank.

Whether you get an entry in this field depends on whether you selected the 'Print credit card info block on bill' field on the Billing packages definition screen. If you left the credit card block field blank, this field will be blank. If you selected the 'Print credit card block on bill field', you'll see abbreviations for the credit cards you accept in this field. For example, if you accept Mastercard and Visa, this field will contain "MC/VISA Number." (QuickFill determines which credit cards to display based on which merchant ID numbers you filled in on the 'Companies' definition screen.) For order acknowledgement files this field will be blank.

This field contains the entry you made— if any— in the 'Federal tax ID' number field on the 'Companies' definition screen.

This field contains the purchase order number if there is one for the order you're billing. If there's no number, this field is blank.

This field contains the customer number for the person to whom the bill is addressed.

This field contains the customer number of the bill-to customer for the order.

This field contains the customer number for the customer receiving the publication; it is blank when the ship-to customer is the same as the addressee and the bill-to customer.

This field contains the agency code when the order being billed is from an agency.

If you entered a salutation in the 'Salutation' field of the customer's record, you'll see that salutation in this field.

If you left the salutation field blank and entered a last name for the customer, QuickFill enters in this field the prefix you entered in the Prefix field of the customers record, plus a space and the last name for example, "Mr. Smith".

If you left the salutation and the prefix fields blank, QuickFill enters "Subscriber" in this field.

If you left the salutation, prefix, and last name fields blank, QuickFill enters "Subscriber" in this field.

This field contains the prefix for the customer to whom the bill is being sent.

This field contains the first name of the customer to whom the bill is being sent.

This field contains the last name of the customer to whom the bill is being sent.

This field contains the suffix for the customer to whom the bill is being sent.

This field contains the title for the customer to whom the bill is being sent.

This field contains the company for the customer to whom the bill is being sent.

This field contains the department for the customer to whom the bill is being sent.

This field contains the information entered on the first line of the address for the customer to whom the bill is being sent.

This field contains the information entered on the second line of the address for the customer to whom the bill is being sent.

This field contains the city in which the customer to whom the bill is being sent lives.

This field contains the state in which the customer to whom the bill is being sent lives.

This field contains the zip or postal code for the customer to whom the bill is being sent.

This field contains the delivery point code for the customer to whom the bill is being sent.

This field contains the country code for the country in which the customer to whom the bill is being sent lives.

This field contains the name of the country in which the customer to whom the bill is being sent lives.

This field contains the carrier route for the customer to whom the bill is being sent.

This field contains the CASS certification date if there is one for the address of the customer you're billing. If the customers CASS certification date is not in QuickFill, this field is blank.

This field contains L or R to indicate whether the postal code should go to the left or to the right of the city name for the country in which the customer to whom the bill is being sent lives. (Please note, this field will contain "R" for the United States of America and Canada. For agency subscriptions, this field will be blank.)

This field contains the entry in the Sex field for the customer to whom the bill is being sent.

These fields contain the entries in the demographic code fields (A, B, C, D, 1, and 2) for the customer to whom the bill is being sent.

This field contains up to eight digits, each of which represents one of the selection flag check boxes on the customer address screen.

This field contains the external customer ID, if any, of the customer to whom you're mailing the bill.

This field contains whatever phone number you entered for the customer to whom you're mailing the bill. This field is useful if you use telephone collections for one of your billing efforts. You can create a mail-merge document with the telephone number. Then you can give this document to your telephone collection people as a lead.

This field contains whatever cell phone number you entered for the customer to whom you're sending the bill.

This field contains whatever fax number you entered for the customer to whom you're sending the bill. This field is useful if you fax bills for one of your billing efforts.

This field contains whatever e-mail addresses you entered for the customer to whom you're sending the bill. These fields are useful if you use e-mail to send bills for one of your billing efforts.

This field contains the entry in the user ID field for the customer to whom the bill is being sent.

This field contains the entry in the password field for the customer to whom the bill is being sent.

*Indicates customer and addressee fields that are either blank or contain zero when a bill is for an agency subscription.