How to import subscriptions

This section describes the methods you can use to import the subscriber database of an existing publication into the QuickFill system. It also provides technical documentation for QuickFill's subscription import found on the Other menu. The section, "The Accounting Impact of Importing Subscribers", covers an area that you may be somewhat unfamiliar with. We suggest that you discuss the accounting considerations with your accountant before actually importing your file.

To import your subscribers into the QuickFill system you put as much data as possible from your existing system in a computer readable format and then write a program to convert that data into the format required by QuickFill.

To simplify this process QuickFill contains a subscription import that loads the QuickFill database from a file with a specified structure. We call this file a "flat file" because it collapses the complex data structure of the QuickFill database into a single data record.

There are two approaches to converting your data into QuickFill, the first involves rekeying your data and the second requires some programming. These methods are explored below.

• Importing Prospects

QuickFill also contains a separate prospect import. You should use this utility if you are importing prospect records into your QuickFill database. The prospect import uses basically the same flat file structure as the subscription import but allows you to omit the subscription related fields. Click here for information on importing prospects.

QUALITY CONTROL

Whenever we perform a conversion at CWC there are certain reports that we use to double check our conversion programming. If you do your own conversion you should use the following procedure:

• From the original data immediately before the conversion you should produce an Expire inventory report, an Accounts receivable report and a Deferred income report. If your old system does not have these reports you will have to write reports to run against your original data.

• Immediately after completing the import run the following QuickFill reports: Expire inventory report (found under the 'Expire & Statistics' option of the reports menu), Accounts receivable report (found under the 'Accounting' option of the reports menu), General ledger report (also found under 'Accounting') and the Renewal inventory report (found under the 'Renewal reports' option of the reports menu).

• Compare the results from the first and second step above. Make sure that you can explain any significant differences. The conversion program and Subscription import is not complete until the differences have been explained (or eliminated).

If you are planning to use QuickFill's accounting then you should post correcting entries to your General Ledger accounts receivable and deferred income balances to make them match the QuickFill results. (See the section titled "The accounting impact of importing subscribers.")

• Use the Renewal inventory report created in the second step above to verify that you have properly set the value for the last renewal effort sent to the subscription.

• If you are converting an audited publication, you should check that the audit statistics in your QuickFill database agree with your source data. That is the number of subscriptions with each subclass and request type should match and the number of responses to each audit question should match.

• Once you are satisfied that the conversion and import is complete make a backup of your QuickFill database. Save this backupquestions about what was true "at the time of conversion" have a way of coming up months after the conversion has been completed.

The first time you run the Subscription import you will probably be confronted by a large number of error messages. Many of these messages are warnings that the import has found fields that are either empty or contain erroneous data. You should be sure to correct the input file and rerun the import, rather than rely on the default values supplied by the Subscription import. There are two reasons for this. The first is that if you have thousands of error messages relating to an error that you think you can ignore, it is going to be very difficult for you to be sure that there aren't other error messages that you shouldn't be ignoring. The second reason is that, although we have tried to make the default assumptions as reasonable as possible, it is much safer for you to look at the fields that are missing data and then to insert your own default assumptions. That way you will know exactly what is going into your QuickFill database.

REKEYING YOUR DATA

If you have a small list of subscribers and a good supply of patience then you can create the flat file by keying in all of the names, addresses and other data using a standard database program. Suitable programs include Microsoft Access, Microsoft FoxPro, Microsoft Excel and White Town Software's DBF Editor and Viewer.

The following procedure shows the steps required to convert a publication using this method:

1. Use any database program that uses or can export a dBASE (DBF), comma-delimited (CSV) or tab-delimited file (TXT) to key in your data. You can create an 'empty' database file with the appropriate fields by doing the following:

a. Use the subscription export facility in QuickFill to export a dBASE or delimited file. If your publications are audited also export the audit fields. Set the maximum number of customers desired to one in order to export only one record.

b. Using your database program delete the single record that QuickFill created. This will result in a file with no records in it.

2. Use the QuickFill system to define the publications and all of the associated codes (see the section titled "Pre-conversion requirements" below).

3. Make a backup copy of the QuickFill database.

4. Run the subscription import to import the data from the file you keyed into QuickFill.

5. Review the error messages produced by the subscription import. If the errors are few and/or minor you can probably correct them using QuickFill transactions. Otherwise restore the database from the backup copy made prior to running the import, then return either to the first or second step.

6. Optional step:
   Process the rejected duplicate customers file. This will only be necessary if you have opted to use the "de-duping" capabilities of the subscription import (see below).

WRITING A CONVERSION PROGRAM

If your subscriber list is too large to re-key then you will need a program to reformat your existing data into the QuickFill flat file format. You can either contract with a local programmer to write this program, or you can contact CWC Software for a quote. We encourage you to consider having CWC Software do your conversion because it is likely that we will be able to perform the conversion faster and more accurately than someone who is starting from scratch.

Included with QuickFill is a separate application program named QFConvert that can help you reformat your data. QFConvert will accept a wide variety of input file formats including Excel, Access, comma delimited files and convert them into the format that QuickFill needs for importing. QFConvert contains a powerful set of address-processing functions that can split full names into separate first and last names, and identify address lines as company names, titles or departments. These functions allow you to accept files containing name and address data in almost any format and generate clean output files with each part of the name and address in its own field. QFConvert can also perform address certification in conjunction with ZP4.

Click here for more information on QFConvert.

Even with QFConvert custom programming will be required particularly with regard to the subscription related data.

The following procedure shows the steps required to convert a publication using this method:

1. Extract the data from your existing system into one or more files. Most systems have an "export" feature that can perform this step.

2. Write a program to reformat the data to conform to QuickFill's flat file specification (described below in the section titled "Field description of the fields that are used by the subscription import.) QFConvert may be used here as a preliminary step.

3. Optional step:
   Review the data using Microsoft Excel or a dBASE database program and make any necessary minor corrections. If major changes are required go back to the second step.

4. Use the QuickFill system to define your publication(s). This entails defining the publication(s) and all of the associated codes that will be used by your conversion (see the section titled "Pre-conversion requirements" below).

5. Make a backup copy of the QuickFill database.

6. Run the subscription import to import the data from the file you created into QuickFill.

7. Review the error messages produced by the subscription import. If necessary go back to the first step, third step, or fourth step after restoring the QuickFill database from the backup copy made prior to running the import.

8. Optional step:
   Process the rejected duplicate customers file. This will only be necessary if you have opted to use the "de-duping" capabilities of the subscription import (see below).

PRE-CONVERSION REQUIREMENTS

Once you have created a flat file the next step is to use the QuickFill system to define the publication that you are converting. Click here for information on setting up a publication.

In addition to defining the publications themselves you should also make sure that you have defined the following in the QuickFill database prior to running the Subscription import:

• All of the issues that have been published. If your publication has a long history you may wish to only define enough issues to "cover" the oldest order that you are importing. Also include sufficient future issues to cover your longest subscription.

• Audited publications only

Be sure the current issue pointer for each publication is set to the last issue served. If you fail to set this pointer, QuickFill will produce an erroneous add/drop report the first time you run the issue label update.

• Billing series for all codes referenced by the BILLSER fields.

• Renewal series for all codes referenced by the RENSER fields.

• Subscription agencies for all of the codes referenced by the AGENCY field.

• Foreign country code definitions for all of the foreign countries referenced by the CNTRYCODE field.

Once you have defined all of these codes, make a backup copy of the QuickFill database. It is important that you have a backup copy of the database made prior to the import because it is quite likely that running the import will uncover some problems that you will want to correct by fixing the flat file and then re-running the import.

DUPLICATES

You probably have duplicate customers in your existing data. This may be because you have multiple publications, a single publication with separate expire files, or simply because your current system does not recognize duplicates.

The QuickFill subscription import recognizes possible duplicate customers as falling into two categories -- near and exact matches. The import utilizes the same duplicate detection strategy as the QuickFill transactions and the Merge duplicates update. Click here for a complete explanation of 'Address matching' and the duplicate detection strategy.

When you run the subscription import you specify the scoring cutoffs to be used for determining near and exact matches. You also specify how the import is to process each incoming record once it has been identified as being an exact match, a near match or a non-match. Click here for a complete explanation of the 'Import subscriptions' screen.

THE SUBSCRIPTION IMPORT

The Subscription import is on the Other menu. You specify the input file and a reject file on the Import filter. The import creates the reject file in the flat file format so that it can be used as an input file in a subsequent run of the import.

The import may be run in either the "Run now" or "Run later" mode. You can stop the import at anytime (by pressing Escape). If you do so simply note the last record processed, it will be displayed on the screen and in the Import report, so that you will be able to restart the import at the next record. Please note that if you use the "Ask me" option for duplicate processing (see below) and "Run later" the import will pause and wait for your input thus halting your run later queue.

The Import filter also allows you to specify the treatment for exact matches, near matches and non-matches. For both exact and near matches you have five choices:

• Merge, retaining the existing address

• Merge, replace the existing data with the incoming data

• Copy the incoming data to the reject file

• Create a new customer record

• Ask me

If you select "Ask me" the import will display matches as it locates them and will allow you to decide what you would like to do with the possible duplicates on a case-by-case basis. This option requires you to be present while the import is running. If you have a large file and/or suspect that there will be a considerable pool of possible duplicates consider using the "Copy to the reject file" option instead of "Ask me". Then you will know how many possible duplicates there are and can set aside the appropriate amount of time to run the import using the reject file as the input file.

For non-matches you have two options:

• Create a new customer record

• Copy the incoming record to the reject file.

The import produces a report when it has completed processing your input file. The summary should look like this:

Of course the numbers on your summary will be very different.

The "Skipped deleted" category show the number of records in your input file that were marked as deleted. These records were not imported.

The "Skipped bad pub code" category show the number of records in your input file whose publication code was not found in your QuickFill database. These records were not imported.

The "Rejected" category shows the number of records rejected as dupes either automatically by the import (if you have used the "Copy to the reject file" option) or by you (if you have used the "Ask me" option).

The "Merged" category shows the number of records in your input file for customers who already existed in your database (or in a prior imported record in the input file). "Merged" customer records are created if you use either of the "merge" options.

In the sample above the input file contained twelve records. One was rejected because it had been marked as deleted in the original input file. One was rejected because of an invalid publication code. Two were rejected because of duplicate addresses. Of the remaining eight, there was one record for a customer already in your database.

The seven new customers are the result of:

The eight new subscriptions are the result of:

Remember the merged customers still have new subscriptions.

The conditions which cause two or three orders to be created for some subscriptions are discussed in detail below.

The final line "x errors found" is a count of the number of individual error messages printed in the Import summary report.

The fields listed below are in the flat file used by the subscription import. These fields are presented in the order they appear in files created by QuickFill's subscription export. Fields that are only required for audited publications will be listed in the appropriate place but their descriptions will be presented at the end of this section.

Your file does not need to have all the fields listed below. If a field is missing, then it is treated as if it were blank. The fields do not have to be in the order listed below. You can have additional fields in your file. The fields do not have to have the same lengths as the fields in the flat file. Leading spaces will be stripped. If the field is still too long then it will be truncated.

Be very careful that the fields you want imported have the correct field name or they will be ignored.

Field types

There are five basic field types: strings, codes, numbers, dollar amounts, and dates.

• Strings

The import will left justify and space fill strings.

• Codes

The import will left justify and space fill code fields like string fields, and also convert them to upper case. Generally, you should define code fields in the QuickFill database before running the import. If the import finds a code that you have not defined in the database it will produce an error message. The import will define the code and then import the record containing it. Subsequent occurrences of the code will use the definition created by the import. It is unlikely that the definition created for you by the import will contain the data you want. Be sure that you check and correct all codes created by the import.

• Numeric

Numeric fields are expressed using ASCII digits. Numeric fields may contain leading or trailing spaces that will be ignored by the import. If the import finds an error in a numeric field it produces an error message and treats it as though it were blank (blank numeric fields are treated as though they contained '0').

• Dollar

Dollar value fields are a special case of numeric fields in that they may contain an embedded decimal point to separate the dollars from the cents. If the decimal point is missing then the value is considered to be whole dollars. If the import finds an error in a dollar amount field it produces an error message and treats it as though it were blank (blank dollar amounts are treated as though they contained '0').

• Dates

Dates can be entered either in YYYYMMDD or MM/DD/YY format. The latter actually matches the standard format as specified on the QuickFill Preferences screen which is typically MM/DD/YY, but can be changed to DD/MM/YY or DD.MM.YY to suit your local custom.

Date fields in dBASE files (type code 'D') are always stored in YYYYMMDD format although you may see them in various other formats on the screen depending on which application you use to view the dBASE file. Character fields in dBASE files (type code 'C') can also be used to enter dates using either the YYYYMMDD or MM/DD/YY format. For character fields the format you see on the screen will always match what is in the file.

When using the MM/DD/YY format the date must be exactly eight characters long and must include leading zeroes. With the MM/DD/YY format, a year less than 50 will be treated as being in the twenty-first century. Years greater than or equal to 50 are assumed to be in the twentieth century.

Field description of the fields that are used by the subscription import

The following is a field-by-field description of the fields in the flat file that are used by the subscription import.

The import will include these two fields when it creates an error message. The ID1 field is a good place for a sequential record number. In case you ever sort the file you can restore it to its original order by resorting on this field. The ID2 field is a good place to put your previous fulfillment id number. The contents of these fields are NOT imported into your QuickFill database.

You use these two fields to control the creation of single party, two-party and group subscriptions.

To create a normal single-party subscription set the subtype to 'S' (single) and leave the shipto field blank.

Two records are required to create a two-party subscription. Both records should have a '2' (two-party) in the subtype field. The first record should contain the billing address and all of the other subscription data. This "first" record should contain a blank in the Shipto field. The record that immediately follows it should contain a 'Y' in the Shipto field.

To create a group subscription use the same sequence as a two-party subscription just include extra records for each shipping address with a 'Y' in the Shipto field of each one. The subtype in all the group records should be set to 'G' (group). If the bill-to customer is also to receive a copy then that customer will need both a bill-to and a ship-to record.

If the subtype is blank then a value of 'S' is assumed.

• Warning:  Do not sort the file. Once you have created the flat file you may be tempted to sort. It is important that you leave the file in its original sequence before running the subscription import. If you sort the file then the multi record sequences for two-party and group subscriptions will become split up and you will end up with the shipping addresses attached to the wrong billing addresses. In most dBASE programs the index option does not affect the actual order of the records in the DBF file therefore creating a new index causes no problems.

If the shipto field contains a 'Y' then only the address data, qualification data, ship copies and ship class fields are used.

The prefix for the subscriber, such as Mr., Mrs., Ms. or Dr.

The subscriber's first name or names.

The subscriber's last name.

The suffix for the subscriber's name, such as Jr., Sr. or Esq.

The salutation with which the subscriber should be addressed on any correspondence. The name that you place in this field will appear on any form letters that you create using the Customer export command on the Other menu. If you leave the salutation blank then the prefix and last name will be used instead.

Enter the person's sex, if you wish. QuickFill does not restrict you to the codes 'M' or 'F' so you can actually use this field to encode additional information about the subscriber that may be useful for special selections. If your old system coded "company only" addresses use this field to capture that data. We suggest that you use 'N' for company only addresses and 'U' where the sex is unknown.

The subscriber's title or position.

The subscriber's company name. If both the subscriber's last name and this field are left blank the import will produce a warning message when it imports the record.

The department of the company to which the subscription is to be delivered.

Two lines are available for entering street address data. If only one line of data exists use STREET1.

The city for the subscriber's address.

Enter the post office code for the state or Canadian province. For foreign countries other than Canada enter a code of "ZZ".

For U.S. addresses enter the zip code using either the common 5-digit format or the newer Zip+4 format. For Zip+4 you must include the "dash" (nnnnn-nnnn).

For Canadian addresses enter the postal code in the form "ANA NAN" where the A's are letters and the N's are numbers (the space in the middle is optional).

U.S. and Canadian zip codes are checked against the state/province code that you entered. If they do not match the import will display an error message.

For other foreign addresses enter the postal code.

The last two digits of the Zip code in 5+4+2 format. These digits are used to create Delivery Point Barcodes.

The carrier route code.

These fields should be filled in for foreign (non-USA non-Canadian) addresses only. For USA and Canadian addresses (as identified by the STATE field above) data contained in these fields will be ignored.

The CNTRYCODE should be defined in the "Foreign countries" table in QuickFill.

If you fill in the COUNTRY field, it should match the Country found in the Foreign country table listed under the CNTRYCODE.

For all foreign addresses (STATE="ZZ"):

If you leave the CNTRYCODE blank the import will produce an error message.

If the CNTRYCODE is entered the import will look it up in the QuickFill Foreign country table. If it is not found the import will produce an error message. If it is found but the COUNTRY name in the table does not match the one entered in the flat file the import will produce an error message.

In any of the above cases the data entered in these fields will be imported into the QuickFill database.

Tax jurisdiction. If your publications are not taxable in any state or province, or taxable only in states/provinces that have a single tax rate and a single reporting requirement, then you may leave this field blank. If you have defined a Tax jurisdictions table in QuickFill, the import will use that table to assign the jurisdiction that corresponds to the subscribers country and postal code. If you have not defined a Tax jurisdictions table, the import will assign the subscribers state/province code as the jurisdiction.

If the subscriber is located in a city or county that has a tax rate different from the state/province-wide rate and you have not defined a tax jurisdictions table, or if the subscriber is located in a taxable foreign country, then you should enter the jurisdiction code for that city, county, or country. You define the jurisdiction codes in your QuickFill database on the "Tax jurisdictions" screen under Definitions on the main menu.

Enter the subscriber's telephone number, with area code if available. If the phone number is not in QuickFill's conventional (999)999-9999 format, the import will attempt to convert it to that format. For example 123-456-7890 will be converted to (123)456-7890.

Enter the subscriber's fax number, with area code if available. If the phone number is not in QuickFill's conventional (999)999-9999 format, the import will attempt to convert it to that format. For example 123-456-7890 will be converted to (123)456-7890.

Enter the subscriber's E-Mail address.

Enter the subscriber's user ID. The user ID should be unique for each subscriber.

Enter the subscribers password.

Enter 'N' if the subscriber is a tax-exempt organization. If the field is blank a value of 'Y' is assumed.

• Important: do not make the mistake of assuming that you should enter an 'N' if the subscriber lives in a state for which you do not collect sales tax. If the subscriber subsequently moves to a taxable state then you want to start charging sales tax on future orders. QuickFill will handle this automatically when you change the state code providing you have not marked the subscriber as tax-exempt.

These fields control how you use the subscriber's name and address for postal, E-mail, telephone, and fax correspondence.  Enter one of these four codes:

These codes are used in the selection of promotional labels.  If a field is blank, a value of 'Y' is assumed.

This field is used to specify whether or not a customer is a bad debt customer or a seed.  Enter "B" for a bad debt customer, "S" for a seed, or a space for a normal customer.  Youll use these codes when you create promotional labels.

Marking a name as a "Bad debt", causes QuickFill to show you that name whenever you enter a subscription with the same zip code and street address. This is useful for handling persons that repeatedly order subscriptions under assumed names but never pay for them.

Seed names are used for policing usage of any names that you may rent to other publishers. If a name is marked as a seed it will be included in any selection of promotional labels regardless of whether it meets the selection criteria or not (there is an override to exclude seed names if you so desire).

There are four numeric fields and two alphanumeric fields you use to encode special information about the customer. If any of the four numeric are blank, then they will be set to zero. You can use these fields for whatever purpose you wish. The Promotional labels, Customer directory, Subscription statistics, Blanket renewals, Customer export, Prospect export, and the Subscription export filters provide a means of selecting customers based on these six codes.

• Note: these demographic fields pertain to the customer, not to the subscription.

You may use this field to specify the most recent date on which your subscribers' addresses were CASS certified. Although this field is not a required one, if you know the CASS certification date for your subscribers' addresses you should include it in your file. A number of the report filters and all of the export filters in QuickFill provide the ability to select customers based upon their CASS certification date.

These date fields are used to specify the date of creation of the customer record and the dates of the most recent change to the postal address, e-mail address, telephone number or fax number. If you leave the CREATEDATE field blank, QuickFill will set it to the date when the record was imported.

This field is to be used only for ship-to records (see SHIPTO, field 5, above). This is the shipping class for this particular ship-to address. Do not use this field for single subscriptions it is used only for the ship-to of a group or two-party subscription. The valid shipping classes are listed in the description of SHIPCLASS1

This field is to be used only for ship-to records. This is the number of copies to be shipped to this particular ship-to address. If this field is blank then the import will use a value of '1'.

This field is required. This must be the publication code for the subscription. You must already have defined the publication in the QuickFill database. If this field is blank or the publication code is not found in the database, then the subscription will not be imported. All subscriptions to the same publication do not need to be together in the file.

The date of the subscription's original order. If this field is blank, the imported subscription date will also be blank.

The number of times that the subscription has been renewed. If this field is blank then a value will be computed based on the number of years between the original subscription date and the current date and the number of orders in the input file record.

The tracking code that identifies the means by which the subscription was obtained. You may know this as a key code, promotional code or source code. If this field is blank then the import will use the publication code. Generally you should define all tracking codes in the database prior to running the import. If a tracking code is encountered that was not previously defined then the import will define the code for you. This definition will be incomplete -- no offer will be connected to any tracking code created by the import.

The channel used to obtain the original order. If this field is blank then the import will use the channel code linked to the tracking code. You should define all channel codes in the database prior to running the import. If a channel code is encountered that was not previously defined then the import will define the code for you.

For paid audited subscriptions the channel definition contains data required for reporting sales in Paragraph 7 of your Publisher's Statement. Therefore if you are converting paid subscriptions for an audited publication make sure that you either supply this field or that each tracking code used is defined in your database and that as part of its definition it is connected to a channel.

Enter a 'N' if the subscriber has asked that the subscription not be renewed. Enter a 'E' if the subscriber has received the complete renewal series. These values will prevent unwanted renewal notices from being generated. Leave this field blank if the subscriber is still eligible for renewal notices. If the field has any other value, it is treated as if it were blank.

Enter a value of 'C' if this is a complimentary subscription. Enter a value of 'G' if this is a gift subscription. All orders created for this subscription will have the gift/comp flag set based on this value.

Enter a cancel reason code if this is a canceled subscription and you use cancel reasons in your database. (Click here for more information on cancel reasons.) The cancel reason code is applied to the current order; it is ignored if the status of the current order is not CC, CX, or A with a renewflag (field 50) set to "N."

The contents of these fields will appear in the subscription history of the newly imported subscription. This data can only be "viewed," that is, it cannot be used for selections or reported on. This is a good place to store data from your prior system (like an old customer id) that you may want to see during a customer service call. When the text is stored in the database it will be broken up into lines that are no longer than 47 characters. The line breaks will occur on word boundaries if possible. You can force a line break to occur by inserting a vertical bar character (|) into the text.

Warning:  If the first character of HISTORY1 is an '!', then the data for the HISTORY1 field is not imported into the subscription history (only data in the HISTORY2 and HISTORY3 fields is imported). CWC Software reserves the '!' to indicate the values for certain internal fields. You should not place an '!' in the first position of HISTORY1.

The one exception to this rule is if the file you are working with was created by QuickFills 'Subscription export'.  Click here for further details on the HISTORY1 field in a subscription export file.

This field is present in the input file but is not directly used by the subscription import. It is however used as a cross check. If the expire date is present and it does not match the issue date field of the expire issue calculated from the term, served and nextiss fields of the three orders, an error message will be displayed. (The issue date field is found in your QuickFill database on the 'Issues' definition screen.)

These two fields are not required. They are documented here because they are in the file created by QuickFills Subscription export.

These two fields only apply to the current order and only if it is also a new (not renewal) order. (See the section below titled "Fields that apply to orders in the subscription chain" for a definition of the current order.) They are used to establish the billing policy for the new order. You need only enter a value in these fields if the first bill for a new order has not yet been sent.

For bill method enter a 'W' if the first bill is to be sent with the first issue, a 'I' if the first bill is to be sent immediately or leave blank if the first bill depends on the values in the next two fields.

If the first bill is not to be sent out until so many days after the first issue, enter that number in the bill delay days field.

• It is important to set the bill delay days field to all blanks if you wish to use the bill delay date (see immediately below). If you enter a zero, the import will assume that you want the first bill to be sent zero days after the first issue.

This field only applies to the current order. (See the section below titled "Fields that apply to orders in the subscription chain" for a definition of the current order.) Unlike the two previous fields, the current order can be either a new or a renewal order for this field to apply. If the first bill is not to be sent until a specific date then enter that date in the bill delay date field. You need only enter this field if the first bill for the current order has not yet been sent.

This field only applies to the current order. (See the section below titled "Fields that apply to orders in the subscription chain" for a definition of the current order.) If the cancel bill has already been sent for the current order, enter 'Y' in this field. Otherwise leave it blank.

The next four fields only apply to the most recent order. If a future order exists it applies to that order, otherwise it applies to the current order. (See the section below titled "Fields that apply to orders in the subscription chain" for a definition of the different orders.)

Enter the credit card number for the most recent order. You may enter other data here if the payment type is not a credit card.

Some users capture the check number or wire transfer number here.

Enter the credit card expiration date as MM/YY.

Enter the credit card authorization code.

The condition under which the premium for the most recent order will be sent. Enter a 'W' if this is the original order for the subscription and the premium is to be sent with the first issue. Enter a 'P' if the premium is not to be served until the order is paid. Enter 'I' if the premium is to be served immediately (i.e. with the next run of the issue label update). Leave this field blank if the premium has already been served.

Fields that apply to orders in the subscription chain

The next set of fields in the record apply to the orders in the subscription chain. The subscription import can import data for at most three orders. These orders are the current order (the one out of which issues are currently being served), a history order (the order just prior to the current order) and a future order (the order just after the current order).

The same set of fields is used for each order. The first set of fields applies to the history order. The second set applies to the current order. The third set applies to the future order. The first two sets have 38 fields each. The third set (the future order) only has the first 30 of those 38 fields. (The other 8 fields are not applicable to the future order.)

The current order fields (set two) must always be completed. The other two are optional. The import determines whether or not to create an order based on the value of the status field. If the status field is non-blank for that order, then the order will be created. The current order (set two) will always be created even if the status field is blank.

For audited publications it is important to complete the history order fields (set one) since the most recent issue for a subscriber may have been served out the history order. If you don't then the on/off reports and the audit issue statement may be incorrect.

Each of the 36 fields is described below. The last digit of the field name identifies the order. Orddate1 is the order date of the history order. Orddate2 is the order date of the current order. Orddate3 is the order date of the future order.

The date of the order. If this field is blank and this is the history order and the number of renewals is zero or one, then the import will use the subscription date.

If this field is blank and this is the current order (Order2) and the number of renewals is zero then the import will use the subscription date. In all other cases, the imported order date will remain blank.

The number of the batch in which the order was included. Since your old system probably does not use batch numbers in the same manner as QuickFill we suggest that you place your old batch number in one of the Subscription history fields (see fields 53 through 55) and leave this field blank.

You specify the status of the order using one of the codes from the following table:

Not all codes can be used with each order. Status1, the status of the history order cannot be SN, SI, ST, A or F. Status2, the status of the current order cannot be F. Status3, the status of the future order can only be F or blank.

The shipping method that QuickFill should use to serve issues. The code should be selected from the following table:

If this field is blank then the publication default domestic shipping class is assumed. For this reason you should not leave the ship class blank on Canadian or foreign subscriptions.

The number of copies of each issue ordered. If this is the 'bill-to' record in a group subscription, then the copies should be the sum of all copies being sent to the group members (see field 5 for a description of the 'bill-to' record). If this field is blank then the import will use a value of '1'.

The term (number of issues) of the order. If this field is blank then the import will use a value equal to the number of issues published per year (from the Publication definition). Do not include free issues here. (See next field.)

The number of free issues in the order.

If you specified any free issues above, then indicate here whether the free issues should be served first by entering an 'F' or last by entering an 'L'. Both the calculation of deferred income and refund due after a cancellation are affected by the value in this field. If this field is blank then the import will use a value of 'F' (first).

The code for the premium selected by the subscriber, if any. On audited publications, it is important to enter the premium even if it has already been served.

The service code for the order, if any.

The price of the order, not including tax and/or shipping. The price should reflect any multi-copy, extended term or agency discounts. If this is the 'bill-to' record in a group subscription, then you should enter the total price for all group members (see field 5 for a description of the 'bill-to' record). If this field is blank then the price will be set to zero.

The amount of the sales tax for the order.

The amount of the shipping charges for the order.

The total amount paid for this order. If more than one payment has been made this field should contain the sum of the payments.

The date when the most recent payment was made. If this field is blank, the import will leave it blank.

The type of the most recent payment for this order. The code should be chosen from the following table:

If any other code is used then an error message will be produced and a value of 'O' will be used. If the field is blank and the amount paid is not zero, a value of 'O' is assumed.

The total amount refunded for this order. If more than one refund has been made this field should contain the sum of the refunds.

The customer purchase order number for the order. Do not include the characters "PO #" in the field. This field will appear on all bills so you should not use it to store miscellaneous information.

• The 3 fields below apply only to audited publications. Their descriptions may be found at the end of this section.

The code for the billing series that was used (or will be used) to bill the order. If this field is blank then the import will first check the QuickFill Tracking code definition for a billing series and if none is defined it will use the default billing series for the publication. Generally you should define all billing series in the database prior to running the import. If the import encounters a billing series code that was not previously defined then it defines the billing series for you by replicating the default billing series for the publication.

These three fields control the billing of the order. If the order was prepaid, or no bill has yet been issued then they should all be blank. Otherwise the Last Bill Effort and Last Bill Date fields should contain the number and date of the last effort sent, and the Response Bill field should contain the number of the bill that was returned with the payment (or blank if no payment has been received yet).

To help ensure that these fields are consistent, missing values will be supplied as follows:

The code for the renewal series that was (or will be) used to renew this order. If this field is blank then the import will first check the QuickFill Tracking code definition for a renewal series and if none is defined it will use the default renewal series for the publication. Generally you should define all renewal series in the database prior to running the import. If a renewal series code is encountered that was not previously defined then the import will define it for you by replicating the default renewal series for the publication.

The tracking code (for new orders) or the renewal effort code (for renewal orders) from which this order was produced.