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.

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 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:

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.")

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.

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

  2. Make a backup copy of the QuickFill database.

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

  4. 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.

  5. 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.

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:

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.

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:

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:

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

 

Read

12

input records

Skipped

deleted input records

Skipped

input records with bad pub codes

Rejected

duplicate input records

Merged

existing customer records

Created

new customer records

Created

subscription records

Created

12

order records

 

errors found

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:

12(original) - 4(skipped/rejected) - 1(merged) = 7.

The eight new subscriptions are the result of:

12(original) - 4(skipped/rejected) = 8.

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.

The import will left justify and space fill strings.

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 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 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 can be entered either in YYYYMMDD format with no separators or in MM/DD/YY format with separators. 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. When entering dates with separators you can use either two or four digit years. When using two digit years, a year less than 70 will be treated as being in the twenty-first century. Years greater than or equal to 70 are assumed to be in the twentieth century. The month and day can either have leading zeroes or not.

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.

The format of date fields in Excel is controlled by the date format of the cell or column, which is typically M/D/YYYY (four digit years, no leading zeroes on the month and day), but may vary depending on the regional settings of the Windows system.

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.

 

ID1 (or RECNUM)

String

Length 6

ID2

String

Length 8

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.

 

SUBTYPE

Code

Length 1

SHIPTO

Code

Length 1

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.

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

 

PREFIX

String

Length 12

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

FNAME

String

Length 20

The subscriber's first name or names.

LNAME

String

Length 30

The subscriber's last name.

SUFFIX

String

Length 12

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

SALUTATION

String

Length 15

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.

SEX

Code

Length 1

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.

TITLE

String

Length 40

The subscriber's title or position.

COMPANY

String

Length 40

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.

DEPT

String

Length 40

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

STREET1

String

Length 40

STREET2

String

Length 40

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

CITY

String

Length 26

The city for the subscriber's address.

STATE

Code

Length 2

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

ZIP

String

Length 10

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.

ZIP2

String

Length 2

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

CARRT

String

Length 4

The carrier route code.

CNTRYCODE

String

Length 4

COUNTRY

String

Length 24

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.

 

TAXJR

Code

Length 3

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.

PHONE

String

Length 20

CELLPHONE

String

Length 20

FAX

String

Length 20

Enter the subscriber's telephone, cell phone and fax numbers, 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.

EMAIL

String

Length 60

EMAIL2

String

Length 60

Enter the subscriber's primary and secondary E-Mail addresses.

USERID

String

Length 15

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

PASSWORD

String

Length 15

Enter the subscribers password.

TAXABLE

Code

Length 1

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

 

RENTPOSTAL

Code

Length 1

RENTEMAIL

Code

Length 1

RENTPHONE

Code

Length 1

RENTFAX

Code

Length 1

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:

Y

- All uses allowed

I

- In-house use allowed

R

- Renewal notices only

N

- No use allowed

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

 

BADSEED

Code

Length 1

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).

 

DEMCODEA (Demo Code A)

Numeric

Length 4

DEMCODEB (Demo Code B)

Numeric

Length 4

DEMCODEC (Demo Code C)

Numeric

Length 4

DEMCODED (Demo Code D)

Numeric

Length 4

DEMDATA1 (Demo Data 1)

String

Length 19

DEMDATA2 (Demo Data 2)

String

Length 19

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.

EXTCUSTID

String

Length 12

QuickFill assigns its own customer numbers to all of the customer records it creates. If you need another identifier or number to link the customer to an external system you can use this field for that purpose.

SELFLAGS

String

Length 8

There are eight distinct "selection flags" available for each customer. To set a selection flag include the corresponding digit in this field. For example, if you want to set the third, fifth and eighty selection flags then enter "358" in this field. If you leave the field blank then no selection flags will be set in the customer's record.

CASSDATE (CASS Certification Date)

Date

Length 10

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.

CREATEDATE

Date

Length 10

POSTALDATE

Date

Length 10

EMAILDATE

Date

Length 10

PHONEDATE

Date

Length 10

FAXDATE

Date

Length 10

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.

CUSTMISC (Miscellaneous info)

String

Length 60

Use this field to hold any miscellaneous data about the customer that you wish to maintain.  This data will be included in the exports and can be accessed using ODBC.

SHIPCLASS (Ship Class)

Code

Length 1

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

SHIPCOPIES (Ship Copies)

Numeric

Length 4

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'.

PUB (Publication)

Code

Length 12

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.

SUBDATE (Subscription Date)

Date

Length 10

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

RENEWALS (Times renewed)

Numeric

Length 3

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.

TRACK

Code

Length 12

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.

CHANNEL

Code

Length 12

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.

SALESPRSN

Code

Length 12

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

RENEWFLAG (Renew Flag)

Code

Length 1

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.

GIFTCOMP (Gift or Comp)

Code

Length 1

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.

CANREASON (Cancel reason)

Code

Length 2

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."

HISTORY1

String

Maximum length is 255 for dBASE files, no limit for delimited text files.

HISTORY2

String

Same as above.

HISTORY3

String

Same as above.

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 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 60 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.

 

EXPIREDATE (Expire Date)

String

Length 20

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.)

 

TOTALDIC (Deferred Income Cash)

Dollar amount

Length 9

TOTALDIA (Deferred Income Accrual)

Dollar amount

Length 9

 

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

 

BILLMETHOD (Bill Method)

Code

Length 1

BILLDELAY (Bill delay days)

Numeric

Length 3

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.

BILLDELDT (Bill Delay Date)

Date

Length 10

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.

CBILLSENT (Cancel Bill Sent)

Code

Length 1

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.)

PREMFLAG (Premium Flag)

Code

Length 1

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. For more information about premiums see the information below (Fields that apply to orders in the subscription chain, PREMIUMn).

AUTORENEW

Code

Length 1

This field indicates whether the subscription has been set up for automatic renewal. Enter '0' if the subscription is not scheduled for automatic renewal. Enter '1' if the subscription is to be automatically renewed and the amount due is to be billed. Enter '2' if the subscription is to be automatically renewed via a credit card. In order to renew by credit card the PASSID field must contain the VIP (formerly EPS) stored payment account ID for the card.

PASSID

Code

Length 50

This field should contain the stored payment account ID when a subscription has been scheduled for automatic renewal using a stored credit card. Payment account ID's are alphanumeric codes issued by Vantiv Integrated Payments (formerly Element Payment Services) as part of their secure storage feature. For security reasons It is not possible to import actual credit card numbers into QuickFill.

 

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). If the subscription is expired or has been canceled then the current order is the last order.

The same set of fields is used for each order. The first set of fields applies to the history order and end with the digit '1'. The second set applies to the current order and end with the digit '2'. The third set applies to the future order and end with the digit '3'.

The current order fields (the ones ending with the digit '2') 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.

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.

 

ORDDATEn (Order Date)

Date

Length 10

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 the ORDDATE2 field (the date of the current order) is blank and the number of renewals is zero then the import will use the subscription date as the current order date. In all other cases, the imported order date will remain blank.

ORDBATCHn (Order Batch)

Numeric

Length 4

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 and leave this field blank.

STATUSn (Status)

Code

Length 2

This field contains the status of the order, as described in the following table. If the STATUS1 or STATUS3 are used to indicate the presence of the history and future orders, respectively. If the status for either of those orders is blank then that order will not be imported. You specify the status of the order using one of the codes from the following table:

Code

Permitted in:

STATUS1

STATUS2

STATUS3

A

Active

No

Yes

No

SN

Suspended for non-payment

No

Yes

No

SI

Suspended indefinitely

No

Yes

No

ST

Suspended temporarily

No

Yes

No

G

Graced

No

Yes

No

E

Expired

Yes

Yes

No

CN

Canceled for non-payment

Yes

Yes

No

CC

Canceled at subscriber's request prior to end of term

Yes

Yes

No

CX

Canceled at end of term.
A subscriber requested non-renewal

Yes

Yes

No

CP

Canceled by publisher (mass cancel).
Only used for audited publications.

Yes

Yes

No

F

Future order

No

No

Yes

SHIPCLASSn (Ship Class)

Code

Length 1

The shipping method that QuickFill should use to serve issues. Valid codes are single letters, ranging from A to Z. The meaning of the shipping class codes is up to you. You can set meanings for all of the shipping class codes on the 'Shipping' tab of the publication definition screen.

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

COPIESn

Numeric

Length 4

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. If this field is blank then the import will use a value of '1'.

TERMn

Numeric

Length 4

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.)

FREEISSn (Free Issues)

Numeric

Length 4

The number of free issues in the order.

SERVEFRSTn (Serve First or Last)

Code

Length 1

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).

PREMIUMn

Code

Length 12

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.

SERVICEn

Code

Length 2

The service code for the order, if any.

PRICEn

Dollar amount

Length 9

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.

TAXn

Dollar amount

Length 9

The amount of the sales tax for the order.

SHIPPINGn

Dollar amount

Length 9

The amount of the shipping charges for the order.

PAIDn

Dollar amount

Length 9

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

PAYDATEn

Date

Length 10

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

PAYTYPEn

Code

Length 1

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

C

Cash or check

MP

Mastercard

VP

Visa

AP

American Express

DP

Discover Card

NP

Diners Club

PP

PayPal

PN

Credit card, unknown type

W

Wire transfer

T

Transfer from another order

F

Claims paid

O

Other

AC

ACH

Credit card payments must have been previously processed outside of QuickFill. You may also use the codes M, V, A, D and N for credit card payments, but QuickFill will silently change these codes to the corresponding "preprocessed" payment types of MP, VP, AP, DP and NP. 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.

PAYNUMn

String

Length 19

Enter the check number, truncated credit card number or wire transfer number for the order. Do not enter a complete credit card number.

REFUNDn

Dollar amount

Length 9

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

WRITEOFFn

Code

Length 1

Enter a 'Y' if the balance due for this order has been written off, otherwise enter an 'N' or leave the field blank. In most cases you can omit this field and QuickFill automatically determine if a write-off has occurred. This field is present primarily because it is one of the fields exported by QuickFill and is useful when you are exporting data from one QuickFill database and then importing it into another.

PRORATEn

Code

Length 1

Enter a 'Y' if the price has been prorated based on the number of issues served, as a result of a subscription cancellation. In most cases you can omit this field and QuickFill automatically determine if a cancellation has occurred. This field is present primarily because it is one of the fields exported by QuickFill and is useful when you are exporting data from one QuickFill database and then importing it into another.

PONUMn

String

Length 20

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.

CONTROLn (Controlled)

Code

Length 1

REQTYPEn (Request Type)

Code

Length 3

SUBCLASSn (Subscription Class)

Code

Length 3

 

BILLSERn

Code

Length 12

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.

BILLDATEn (Last Bill Date)

Date

Length 10

BILLEFFn (Last Bill Effort)

Numeric.

Length 1

RESPBILLn (Response Bill)

Numeric

Length 1

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:

BILLEFFn

RESPBILL1

BILLDATE1

Result

Missing

Present

Present

BILLEFF1 set to equal RESPBILL1.

Present

Missing

Present

If the order is paid then RESPBILL1 set to equal BILLEFF1.

Missing

Missing

Present

BILLEFF1 set to 1. If the order is paid then RESPBILL1 is also set to 1.

Missing

Present

Missing

BILLEFF1 set to equal RESPBILL1. BILLDATE1 is set to 10 days ago.

Present

Missing

Missing

If the order is paid then RESPBILL1 set to equal BILLEFF1. BILLDATE1 is set to ten days ago.

RENSERn

Code

Length 12

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.

ORDSOURCEn

Code

Length 12

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

ORDCHANn

Code

Length 12

The order channel is optional for nonaudited and audited nonpaid subscriptions. Fill this field in if you wish to track new orders and renewals by the channel used to obtain them. The import does not require that the channel is already defined in the database.  If you use an undefined channel code the import will define the code.

For paid audited subscriptions the channel definition for renewal orders 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 supply this field for each renewed order and that each code that you use is defined in your database.

ORDSALESn

Code

Length 12

The order salesperson is optional for all types of subscriptions. Fill this field in if you wish to track new orders and renewals by the salesperson used to obtain them. The import does not require that the salesperson code used be defined in the database.  If you use an undefined code the import will define it.

MISCn

Code

Length 12

The miscellaneous code is optional. You can use this field for whatever purpose you wish. The files created by the subscription export will contain this field.  You can also obtain this field via ODBC.

AGENCYn

Code

Length 12

The code for the subscription agency from which the order was obtained. Generally you should define all subscription agencies in the database prior to running the import. If the import encounters an agency code that was not previously defined then it will define the agency code for you with discount rates of zero.

DISCRATEn

Numeric

Length 4

The discount rate for an agency order.  If the agency code field contains a code and this field is blank, the import will enter the appropriate rate from the agency definition in QuickFill when it creates the order record.  If the agency is not defined in QuickFill, the import will leave this field blank.

EXTORDIDn

String

Length 12

Use this optional field to enter a number or other identifier that links the order to another system external to QuickFill. When converting from another system to QuickFill it is useful to keep the original order ID from the old system for reference purposes in QuickFill.

The remaining fields apply to the history and current order only. These fields are ignored for future orders.

RENDATEn (Last renewal date)

Date

Length 10

RENEFFn (Last renewal effort)

Numeric

Length 2

These two fields specify the method being used to renew this order. If no renewal effort has been made, then these two fields should be blank. Otherwise the last renewal effort and last renewal date fields should contain the number and date of the last effort sent.

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

RENEFFn

RENDATEn

Result

Missing

Present

RENEFFn set to 1.

Present

Missing

RENDATEn is set to 10 days ago.

RESPRNUMn

Numeric

Length 2

Response number. This field is only used if this order has been renewed. The data relating to how the renewal was obtained should be placed in the ORDSOURCEn and ORDCHANn fields for the renewal order.

The resprnum is the renewal effort number that the subscriber responded to. If this field is blank or zero then the value of the last renewal effort (field 100) will be used.

SERVEDn

Numeric

Length 4

The number of issues served for the order. Be sure to include the free issues if any. Note that the number of issues served may be greater than the order term plus free issues. If so, and the order status is 'A' (active) then the import will automatically change it to 'G' (graced).

LASTISSn

Numeric

Length 4

The issue number of the most recent issue served from the order. If this field is blank and the NEXTISSn field is not blank and SERVEDn is not zero then the import will assume that the last issue served was the issue before NEXTISSn. If this field is blank and NEXTISSn is blank and served is not zero, it will assume that the last issue served was the publication's current issue. If this field is blank and SERVEDn is zero, then LASTISSn will be set to zero to indicate no issues yet have been served. You should define the current issue before running the import. Click here for information on defining the current issue.

NEXTISSn

Numeric

Length 4

The issue number of the next issue to be served from the current order. If this field is blank then the import will first check the value of the SERVEDn field. If SERVEDn is 0 the next issue  is set to the current issue + 1. If SERVEDn is not 0, the next issue is set to the last issue plus one (this assumption is valid unless the order has been suspended temporarily).

QuickFill uses sequential issue numbers to internally identify each of the issues. You should define these issue numbers in the database prior to running the import.

FRSTISSDTn

Date

Length 10

The date when the first issue for the order was served (not to be confused with the cover date of the first issue). If this field is blank then the import will compute an approximate date based on the number of issues served, the publication frequency and the last issue date.

LASTISSDTn

Date

Length 10

The date when the most recent issue for the order was served (not to be confused with the cover date of the most recent issue). If this field is blank, then the import will use yesterday's date.

 

The remaining fields apply only to audited publications. Their descriptions are at the end of this section.

 

QDATE or QUALDATE (Qual. Data Date)

Date

Length 10

QBATCHDATE (Qual. Batch Date)

Date

Length 10

QBATCHNUM (Qual. Batch Number)

Numeric

Length 4

Qualification Data

String

 

Qualification Data

String

 

Qualification Data

String

 

Qualification Data

String

 

 

The following is a description of each of the fields that apply only to audited publications.

 

CONTROL1,2,3 (Controlled)

Code

Length 1

Enter a 'Y' if this is a controlled order. A controlled order is one that is free and can be cancelled at the publisher's discretion. Enter 'N' if this is not a controlled order. If this field is blank, then the import will assume a value of 'N'.

 

REQTYPE1,2,3 (Request Type)

Code

Length 3

The means by which you received the request for the order or the information source you used to determine that the person is qualified. For subscriptions that are not classified as 'Verified' use one of these codes:

PDW

Personal direct written

NRT

Non-request telecom

PDT

Personal direct telecom

NRI

Non-request Internet/E-mail

PDI

Personal direct Internet/E-mail

AR

Association roster

CDW

Company direct written

BD

Business directory

CDT

Company direct telecom

FR

Field reports

CDI

Company direct Internet/E-mail

LIC

Licensees

MI

Membership individual

LIS

Lists

MO

Membership organization

OTH

Other sources

NRW

Non-request written

 

 

 

For subscriptions that are classified as Verified Individual Use you should choose one of these codes:

CRD

Card holders

PPR

Partnership program

CHD

Charitable donors

PRO

Professionals

CLM

Club members

SUB

Publication subscribers

CON

Contest entrants

PDB

Public database members

EDU

Educators

SPH

Season pass holders

EVP

Event participants

SBY

Service buyers

EXE

Executives

SRE

Survey respondents

INR

Individually requested

TKB

Ticket buyers

LCH

License holders

WRE

Warrantee registrants

MBY

Merchandise buyers

OIU

Other individual use

ORM

Organization members

 

 

 

For subscriptions that are classified as Verified Public Place you should use one of these codes:

DHC

Doctor/health care providers

TRA

Transportation outlets

PCS

Personal care salons

GOV

Government/civil services

FRF

Fitness/recreational facilities

HTL

Hotels/lodges

EDL

Education/learning facilities

SPR

Specialty locations/retail

BPR

Business/professional services

IRH

In-room hotel/lodging

AUT

Automotive outlets

OPP

Other public place

If this field is blank, then the import will assume a value of OTH (Other sources).

 

SUBCLASS1,2,3 (Subscription Class)

Code

Length 3

Every subscription to an audited publication must be classified under one of these categories. Use one of these codes:

NPI

Non-paid individual

NPL

Non-paid list source

NPA

Non-paid association

NPM

Non-paid membership benefit

NPB

Non-paid bulk

NPR

Non-paid rotated

PI

Paid individual

PC

Paid combination

PS

Paid sponsored

PA

Paid association

PAN

Paid association non-deductible

PM

Paid club membership

PMN

Paid club membership non-deductible

PB

Paid bulk

PD

Paid deferred

PL

Paid loyalty/award point

PP

Paid partnership

PPN

Paid partnership non-deductible

NQA

Non-qualified advertiser

NQP

Non-qualified paid

NQR

Non-qualified rotated

NQS

Non-qualified samples

NQO

Non-qualified other

If this field is blank and the price is non-zero then the import will set the sub class to NQP, otherwise to NQO.

 

The fields below apply only to subscribers who have qualification data. For two party or group subscriptions, enter these in the 'shipto' record(s) not the 'billto' record.

 

QDATE or QUALDATE
(Qualification Data Date)

Date

Length 10

The date the subscriber completed the qualification form. If this field is blank, then the import will use the subscription date. For audit reasons, we strongly recommend that you do not leave this field blank if you are entering any qualification data. The field name may be either QDATE or QUALDATE. Only one of these fields is needed. If you have both QDATE and QUALDATE fields the QDATE field will take precedence.

QBATCHDATE (Qualification Batch Date)

Date

Length 10

The date you recorded the qualification data. If this field is blank, the import will use the current date.

QBATCHNUM (Qualification Batch Number)

Numeric

Length 4

The batch number used to record the qualification data. If this field is blank, then the import will use the batch number of the import.

PERSID (Personal identifier)

String

Length 20

The response to the personally identifying question that was requested when qualifying a subscriber by telephone or on the Internet.

 

Qualification Data

String

Length varies based on number of responses

Qualification Data

String

Length varies based on number of responses

Qualification Data

String

Length varies based on number of responses

Qualification Data

String

Length varies based on number of responses

The audit qualification data fields apply to the qualification form. The field names should match your qualification form questions on the publication definition 'Audit' tab. For example, the response(s) for question 1 should be in a field named Q1, the response(s) for question 1A should be in a field named Q1A, etc. A question can have up to three alphanumeric characters.

The number of qualification data fields and their lengths will vary depending on the number of questions in your qualification form(s) and the number of responses allowed for each question. For questions that allow multiple responses, all responses should be put into the field that matches the question number and should be separated by commas. For example, lets say that the possible responses for question 1 are the numbers 1 through 10, you allow multiple responses, and one of your customers responded with 1, 3, 4, and 5. Your import file should have a field named Q1 that contains 1,3,4,5 for that customer.

The qualification data fields should be defined as character fields if the allowed responses are letters or numbers or as numeric fields if the allowed responses are numbers and the corresponding Code field on the publication definition 'Audit' tab in QuickFill contains "#". The response(s) to questions can have up to three alphanumeric characters for character fields and up to nine digits for numeric fields.

When importing subscriptions that include qualification data and are for multiple publications with different qualification forms, the import file should contain a separate field for each question that is unique. For example, if the qualification form for publication A has questions 1, 2, an 3 and the qualification form for publication B has questions 1, 1A, and 1B, the import file should have qualification fields named 1, 1A, 1B, 2, and 3.

The number of qualification data fields that your import file can have is limited according to the maximum number of fields allowed for dBASE files. The field limit is 255 fields.

THE ACCOUNTING IMPACT OF IMPORTING SUBSCRIBERS

We suggest that you discuss the information presented in this section with your accountant before beginning your conversion.

From an accounting perspective QuickFill maintains a subsidiary ledger with income-related accounts. The Subscription import calculates the total accounting effect of each order it converts and posts the resulting journal entry to QuickFill's internal ledger. That is, it posts an entry that is equivalent to all of the entries that would have been made if you had keyed in the order, and any transactions to it, in the regular way.

It is probable that most of the information in these journal entries has already been captured in your existing accounting system. You may, however, want to bring some of your balance sheet accounts into agreement with QuickFill's ledger. We will explain how to do this in the fourth step (Deal with the accounting generated by the import) below. You will also want to be sure to isolate the accounting generated by the import from the accounting for any transactions you have already entered directly into the system. The following instructions explain how to do this.

1. Before running the import

Run the Journal extract which you will find under Accounting on the QuickFill main menu. You should post this extract to your general ledger as usual.

Run the General ledger report (located under Accounting Reports on the Reports menu). We suggest that you run this report twice, once with the "Accounting basis" set to 'A' (accrual) and the other time with the "Accounting basis" set to 'C' (cash). The extra run is simply to provide you with a more complete view of the effects of the subscription import.

2. Run the Subscription import

3. After running the import

Run the Journal extract which you will find under Accounting on the QuickFill main menu. The amounts obtained by this extract should NOT be posted to your general ledger accounts. You do not post this extraction because these amounts represent the effects of actions already completed through your "old" fulfillment system and have already been (or, in the case of actions performed recently will be) handled by your pre-QuickFill accounting system.

Run the General ledger report (located under Accounting Reports on the Reports menu). You may want to run this report twice, the first time with the "Accounting basis" set to 'A' (accrual) and the second time with the "Accounting basis" set to 'C' (cash).

4. Deal with the accounting generated by the import.

During the subscription import QuickFill produces summarized accounting transactions for the cumulative effect of all the orders imported. These transactions are then posted to the QuickFill general ledger. Entries to accounts like PAYNC (new order cash) are cumulative entries that you can ignore when you reconcile the post-import QuickFill General ledger report to your "outside" accounting general ledger. You can usually ignore all of the values in the post-import General ledger report with the following exceptions:

For cash basis accounting:

SUBDC

Deferred subscription income

S_HDC

Deferred shipping and handling

REFUND

Refund liability

For accrual basis accounting:

SUBDA

Deferred subscription income

S_HDA

Deferred shipping and handling

A_RN

Accounts receivable from new orders

A_RR

Accounts receivable from renewal orders

TXU*

Uncollected sales tax

REFUND

Refund liability

TXU* represents uncollected sales tax for all jurisdictions.

Each of the above accounts represent balances as of the end of the import, based on all of the orders in the QuickFill system. If you have supplied all of the data (price, term, issues served, amount paid, tax due, and shipping and handling due) then these values will be an accurate measure of the current balances in these accounts. If so you will probably want to bring your outside accounting system into agreement with the QuickFill values.

The differences that may exist between the QuickFill general ledger account values and your outside ledger can be caused by many different factors. Perhaps you are currently using methods for approximating certain values (like earned and/or deferred income) that, while perfectly acceptable from an accounting point of view, do not generate the same results that QuickFill obtains by using exact calculations on a transaction by transaction basis. Without knowing the details of your situation, we cannot prescribe the exact journal entries you should use but the following should be a useful guide:

For cash basis accounting:

1. To adjust deferred subscription income

Db. Deferred income

(General ledger deferred income balance - SUBDC - S_HDC)

Cr. Subscription income

(General ledger deferred income balance - SUBDC - S_HDC)

 

2. To adjust refund liability

Db. Refund liability

(General ledger refund liability - REFUND)

Cr. Subscription income

(General ledger refund liability - REFUND)

 

For accrual basis accounting:

1. To adjust deferred subscription income

Db. Deferred income

(General ledger deferred income balance - SUBDA - S_HDA)

Cr. Subscription income

(General ledger deferred income balance - SUBDA - S_HDA)

 

2. To adjust accounts receivable

Db. Accounts receivable

(A_RN + A_RR - General ledger accounts receivable)

Cr. Subscription income

(A_RN + A_RR General ledger accounts receivable)

 

3. To adjust uncollected sales tax

Db. Uncollected sales tax

(General ledger uncollected sales tax - TXU*)

Cr. Subscription income

(General ledger uncollected sales tax - TXU*)

 

4. To adjust refund liability

Db. Refund liability

(General ledger refund liability - REFUND)

Cr. Subscription income

(General ledger refund liability - REFUND)

 

If you are planning to run your old system and QuickFill in parallel for a short test period you need to be extra careful not to "double" count the affects of transactions that you enter in both systems.

After running the import your QuickFill general ledger account values for "Period to date" and possibly "Year to date" will include the effects of the import which will most likely come from prior periods (and years). These amounts are exactly those which, as explained under item #3 above, you have not posted to your general ledger. This difference between your QuickFill accounts and your actual general ledger will continue until the period (and possibly year) have been closed.

For example, assume you have entered 10 paid orders with a term of 12 and a price of $36. You have served one issue to each of these orders. You do not collect sales tax, nor do you charge for shipping and handling. The balances in your accounts will be:

 

Account

 

Balance

PAYNC

(new order cash)

$360 Db.

EIC

(Earned income cash)

30 Cr. 

DIC

(deferred income cash)

330 Cr. 

 

Now you import 1,000 customers with mixed terms, prices, and numbers of orders served.

 

Account

Pre-import

Import

Post-import

PAYNC

$360 Db.

$37,200.76 Db.

$37,560.76 Db.

EIC

30 Cr. 

16,429.83 Cr.

16,459.83 Cr. 

DIC

330 Cr. 

20,770.93 Cr.

21,100.93 Cr. 

 

What does this mean?

                                    See Also

 

ERROR MESSAGES

The following is an alphabetical list of error messages generated by the Subscription import.

The number in the RESPBILLx (responded to bill) field is greater than the number of invoices sent.

The billing series code shown was not previously defined in the database. The conversion utility will define the series for you based on the publication's default billing series.

Every subscriber should have a person's name or company name.

Every order other than the future order should have either the last issue or the next issue specified.

The cancel bill flag is set indicating that the cancel bill has already been sent but the order status is not CN (Cancel for non-payment) or CC (Cancel at customer's request).

The cancel reason code shown was not previously defined in the database. The conversion utility will define one for you.

Both the country name and the country code are blank .

The country code has been found in the country table but the country name in the table does not match the one in the data being imported.

The country code field is blank.

The four demo code fields (A, B, C and D) must be numeric. Use the demo data fields for non-numeric demographic data.

User IDs should be unique for each subscriber. When the ID for a subscriber matches that of another customer, QuickFill assigns an alternate ID to the subscriber you are importing.

The 'E-mail' allowed usage field (RENTEMAIL) contains an invalid code.  This field should contain Y (all uses allowed), I (in-house use only), R (renewal notices only), or N (no use allowed).

The expiration issue for this subscription has not yet been defined in the issue table for the publication.

The expire date field does not match the name of the calculated expiration issue for the subscription.

The order is expired but the sum of the term and free issues exceeds the number served. Expired orders should be completely served.

The status field indicates the order is graced but the sum of the term and free issues exceeds the number served.

The 'Fax' allowed usage field (RENTFAX) contains an invalid code.  This field should contain Y (all uses allowed), I (in-house use only), R (renewal notices only), or N (no use allowed).

The qualification date field contains a future date (that is a date after the system date). It is important to enter a correct date for audited publications.

The second and subsequent records in a two-party or group subscription should have the ship-to flag set to 'Y'.

The batch field contains non-numeric data.

The bill responded to field contains non-numeric data.

The billing delay date field contains an invalid date.

The billing delay days field contains non-numeric data.

The billing method flag was not 'W', 'I' or blank. The Import will assume the method specified in the publication definition.

Canadian postal codes should have the format ANA NAN where 'A' is an alphabetic character and 'N' is a number.

The cancel bill field must either be blank or contain 'Y' if the cancel bill has already been sent.

The ship-to field must either be blank or contain 'Y' if this is the ship-to customer in a two-party or group subscription.

Response X is not a valid response for question X on your qualification form. Either the response is not one of the responses defined in your qualification form (on the Audit tab of the publication definition) or, if the response is a letter, it is not in the same case (uppercase/lowercase) as it is in your publication definition.

The country code is not defined in the country table.

The current order status code cannot be blank or 'F' (future). The Import assumes 'A' (active).

The firstissdt field contains an invalid date.

The free issues field contains non-numeric data.

The future order status code must be blank or 'F' (future). The Import assumes 'F' (future).

The gift/comp field must either be blank or contain 'G' for gift or 'C' for complimentary subscriptions.

The history order status code cannot be 'F' (future), 'A' (active), 'SN' (suspend nonpay), 'SI' (suspend indef) or 'ST' (suspend temp). The Import assumes 'E' (expired).

The last billing effort date field contains an invalid date.

The last billing effort field contains non-numeric data.

The last issue date field contains an invalid date.

The last issue field contains non-numeric data.

The last renewal effort date field contains an invalid date.

The last renewal effort field contains non-numeric data.

The nextiss field contains non-numeric data.

The copies field contains non-numeric data.

The served field contains non-numeric data.

The renewals field contains non-numeric data.

The prior subscription copies field contains non-numeric data.

The order date field contains an invalid date.

The paid field contains non-numeric data.

The pay date field contains an invalid date.

The pay type field contains an invalid payment type code. The Import will use payment type code 'O' (other).

The premium method flag was not 'W', 'I', 'P' or blank. The Import will assume 'I' (immediate).

The price field contains non-numeric data.

The prior subscription class field contains an invalid subscription class.

The qualification batch date field contains an invalid date.

The qualification batch field contains non-numeric data.

The qualification date field contains an invalid date or is missing. It is important to enter a correct date for audited publications.

The refund field contains non-numeric data.

The renewal effort responded to field contains non-numeric data.

The renew field must either be blank, 'N' or 'E'.

The request type field contains an invalid request type.

The ship-to copies field contains non-numeric data.

The ship-to ship class field contains an invalid shipping method code.

The shipping field contains non-numeric data.

The ship class field contains an invalid shipping method code.

The state field did not contain a valid U.S. state code, Canadian province code, or the code 'ZZ' for a foreign country. (Click here for U.S. state codes or here for a list of Canadian province codes.)

The subscription class field contains an invalid subscription class.

The sub date field contains an invalid date.

The subscription type must either be 'S' for single, '2' for two-party or 'G' for group.

The tax field contains non-numeric data.

The term field contains non-numeric data.

The incoming record is for a USA address and the zip code is either non-numeric, its length is not 5 or 10 (zip plus4) or its length is 10 but there is no hyphen.

The subscriber has received an issue that has not yet been published. Perhaps the current issue pointer for the publication is not set to the most recently served issue.

The customer 'Type' field (BADSEED) contains an invalid entry.  This field should contain S for customers you wish to mark as seeds, B for customers you wish to mark as a bad debt customer, or nothing for normal customer records.

The company, zip and last name fields are all blank.

The incoming record contains more than one response for question X, but question X is defined (in the qualification form on the Audit tab of the publication definition) as allowing only one response.

The order date field is blank.

The price for a controlled order should always be zero.

A controlled order should always be classified as non-paid or verified.

The 'Payment ' field (for the order specified in the 'Import subscriptions' report) contains a negative amount.  If you are importing subscriptions from another QuickFill database and a group subscription contains an order created by a 'Group change', a negative payment for that order is valid.  In most circumstances though, payments should not be for negative amounts.  

The 'Phone' allowed usage field (RENTPHONE) contains an invalid code.  This field should contain Y (all uses allowed), I (in-house use only), R (renewal notices only), or N (no use allowed).

The 'Postal' allowed usage field (RENTPOSTAL) contains an invalid code.  This field should contain Y (all uses allowed), I (in-house use only), R (renewal notices only), or N (no use allowed).

The premium flag can have a value of 'W' (with issue) only for new orders. The Import will assume 'I' (immediate). Premium flag can be W only for orders where served is 0 The premium flag can have a value of 'W' (with issue) only for new orders where the first issue has not yet been served.

The premium flag should have a value of 'P' (when paid) only if the order is currently unpaid. The Import will assume 'I' (immediate).

You specified the condition for shipping the premium but you did not specify what premium to ship.

The 'Price ' field (for the order specified in the 'Import subscriptions' report) contains a negative amount.   

The publication code is blank. This record is not imported.

The publication code is not in the database. This record is not imported.

The incoming record does not contain a qualification batch date, but is classified as being qualified and contains a qualification date and/or qualification data.

The incoming record does not contain a qualification date, but is classified as being qualified and contains qualification data.

The incoming record does not contain a response for question X, but question X is defined (in the qualification form on the Audit tab of the publication definition) as being required.

The incoming record does not contain a numeric response for question X, but question X is defined (in the qualification form on the Audit tab of the publication definition) as having a numeric response.

The 'Refund ' field (for the order specified in the 'Import subscriptions' report) contains a negative amount.   

The number in the RESPRNUMx (responded to notice) field is greater than the number of renewal notices sent.

The renewal series code shown was not previously defined in the database. The conversion utility will define the series for you based on the publication's default renewal series.

If there are free issues, you must specify whether they should be served first (before the paid issues) or last (after the paid issues).

The service code shown was not previously defined in the database. The conversion utility will define one for you.

The 'Shipping ' field (for the order specified in the 'Import subscriptions' report) contains a negative amount.   

Single party subscriptions should always have a blank ship-to field.

An internal error has occurred during the conversion processing. Please contact Technical Support and report this error.

The 'Tax' field (for the order specified in the 'Import subscriptions' report) contains a negative amount.   

The number of issues per year as defined on the publication definition general tab is used instead.

The first input record contained a 'Y' in the ship-to field. The first record for a two-party or group subscription should always be the bill-to so the ship-to field should be blank. Single party subscriptions should always have a blank ship-to field.

The numeric response for qualification question X, in the incoming record, contains too many digits.

The first three digits of the zip code do not match the state code. Click here for a list of valid zip code ranges for each state.