More information

Phonathon Pledge Utility

This utility is launched by navigating to X:\Inetpub\WWWRoot\Mill\Tools\phonathon.exe using Windows Explorer, and double clicking that file. It executes in its own window and not from within the standard Millennium interface.

The Phonathon Pledge utility is a specialized import utility designed to create pledge transactions for constituents who responded to a Phonathon effort. The data for the new transactions will come from two sources. Some of the data will come from your manual input into the utility's interface and that information will be standard for all of the pledge transactions that are created by the utility. Some of that data will be controlled by your Millennium lookup table and some will be entered as text or dates. Other data will be variable from transaction to transaction and that data will come from a dbf file that has been created by your staff or an outside party, based on the responses to the Phonathon. Using the utility, certain fields from that dbf file are mapped to the appropriate Millennium data columns. In the utility interface, certain of the data fields allow you the option of choosing the manual input of data or mapping a field from the input dbf file for use in creating the new transactions.

Important! To comply with Payment Card Industry – Data Security Standards (PCI-DSS), complete credit card numbers are not imported or stored in the Millennium database.

Utility Classification

This utility is a user utility. It creates new data rows. It does not effect existing data and it is classified as a system administrator tool. You must use a database administrator (sa or MILLORA User ID) or Millennium system administrator log on to activate the utility.

Specifications for the Phonathon Pledge Input File

This utility requires the use of a dbf file which includes fields containing the following data :

Data description	Millennium Field Name	Data type/length	Requirement for Phonathon
Source of Payment (Constituent ID Number)	giftid	char/10	mandatory
Amount of the pledge	giftamount	money/number	mandatory
Deductible amount of the pledge	giftdeduct	money/number	mandatory
Gift Date	gifteffdat and giftprocdt	datetime/date yyyymmdd	optional
Account Number	giftacctno	char/30	optional - if used, it MUST be the Account Number, NOT code or name The corresponding account number entry in the database lookup table must be active. If the 'Use Account Number' item in the Options menu is unchecked, this field will be unavailable for import.
Gift Restriction	giftrest	char/6	Optional - If used, it MUST be the code, NOT name (chart_val) or account number (chart_acct). The code must be a valid, active entry in the chart_of_accounts lookup table. If the 'Use Account Number' item in the Options menu is checked, this field will be unavailable for import.
Billing Cycle	giftplgtyp	char/6	optional - if used, it MUST be a valid, active code in the Pledge Type lookup table
Classification 1	giftclass1	char/6	optional - if used, it MUST be a valid, active code in the Gift Classification 1 lookup table
Start Date	giftpstrty, giftpstrtm, giftpstrtd	yyyy/mm OR yyyy/mm/dd	optional - Start day optional within the Start Date field. If used, format of field must be yyyy/mm/dd. If not used, format of field must be yyyy/mm, and start day will default to first day of the month. The year is a four-digit year between 1600 and 2200.
Number of Payments	giftpaycnt	numeric/number/5	optional
Tender	gifttender	char/6	optional If used, it MUST either be Cash (c), Credit Card (cc) or Bank Draft (bank). Other tender types will default to Cash.
Secondary Gift Type	giftcrdtyp	char/6	optional If used, it MUST be a valid, active entry in the Credit Types (credit_types)lookup table.
Comment	giftcomm	varchar/varchar2/4000	optional
Credit Card Type	credcctype	char/6	optional - credit card field only If used, it must be an active, valid credit_card_types lookup table code.
Account Type	credbnktyp	char/6	optional - bank draft field only If used, it must be an active, valid bank_account_type lookup table code.
Routing Number	credroute	char/10	optional - bank draft field only
Bank Name	credbanknm	char/	optional - bank draft field only
Checking or Savings?	credacctty	char/3	optional - bank draft field only Only SAV, DDA and blank are valid entries
Type of Draft/Debit	credcustty	char/3	optional - bank draft field only Only TEL, ARC, RCK, WEB, PPD, CCD, and blank are valid entries
Account Number	credccnum	char/16	bank draft field
Expiration Month	credexpmn	char/2	optional - credit card field only
Expiration Year	credexpyr	char/4	optional - credit card field only
First Six Digits of Card Number Account Number	credfirst6	char/6	optional - credit card field bank draft field
Last 4 Digits of Card Number Acct Number (Last 4)	credlast4	char/4	optional - credit card field bank draft field
Masked Card Number	credmasked	char/16	optional - credit card field
Approval Code	credapprov	char/15	option credit card and bank draft field
Name on Credit Card Name on the Account	credccnm	char/varchar2/50	optional - credit card field bank draft field
Billing Address Line 1	credaddrl1	varchar/varchar2/100	optional - credit card and bank draft field
Billing Address Line 2	credaddrl2	varchar/varchar2/100	optional - credit card and bank draft field
Billing Address City	credcity	char/30	optional - credit card and bank draft field
Billing Address State	credstate	char/6	optional - credit card and bank draft field If used, it must be an active, valid places lookup table code.
Billing Address Zip Code	credzip	char/15	optional - credit card and bank draft field
Billing Address Country	credcntry	char/6	optional - credit card and bank draft field If used, it must be an active, valid countries lookup table code.
Phone Number	credhphone	char/15	optional - credit card and bank draft field
Email	credemail	char/200	optional - credit card and bank draft field
License Number	creddlnum	char/15	optional - bank draft field only
License State	creddstate	char/6	optional - bank draft field only If used, it must be an active, valid places lookup table code.
Birth Date	credbdate	datetime/date	optional - bank draft field only
Social Security Number	credssnum	char/9	optional - bank draft field only
Federal Tax ID	credein	char/11	optional - bank draft field only

Optional items that map to fields in the gift table may also be entered manually into the utility's interface if you want for all of the pledges to contain standard data for that field.

Optional credit card items that map to fields in the credit card table will only be available for mapping if Credit Card is selected from the list of available tenders, or the tender is a specified field in the input file.

Optional bank draft items that map to fields in the credit card table will only be available for mapping if Bank Draft is selected from the list of available tenders, or the tender is a specified field in the input file.

It is not necessary for the names of these fields in the dbf to match the names of the Millennium fields into which they will be imported, but the imported data must conform to the data type and length of the Millennium field. The fields may appear in the dbf in any order. The dbf may contain any number of additional fields of data but they will not be considered in creating the pledge transactions.

The length of the Name on Credit Card and Name on Account fields cannot exceed 50 characters.

Phonathon Pledge Instructions

In addition to the specifications for the import file to be used, there are some pre-conditions and certain steps involved in setting up the Phonathon utility for use at your institution. The following describes and discusses these procedures. Following the descriptions, you will be given a set of instructions for using the Phonathon Pledge utility.

Workstation and Login

Some of the standard Millennium utilities make use of certain Microsoft applications and associated files. While we ensure that the required applications are present and registered on the IIS machine as part of the installation process, Millennium's thin-client architecture does not install them on every workstation at your institution. Therefore, to ensure success, the Phonathon Pledge Utility must be launched via the IIS machine, using the system administrator's login and password.

The Phonathon Pledge Utility is run outside of the interface you use to access Millennium, in the utility's own application window. It must be activated by locating the program file (phonathon.exe) in the Tools directory, using the Windows Explorer or other appropriate tool, and then double clicking the program name.

An application window for the Phonathon Pledge Utility will open, showing the File command and a File Open button below it. When you click on either of those items, you will first be prompted to login to the Millennium database with ODBC. Use the Machine Data Source tab to specify Web SQL (or another Data Source Name that you may have set up). You must log on as either the database administrator (sa or MILLORA User ID) or as a Millennium system administrator.

When you are successfully logged into the database, you will be given a Windows common dialog box to identify the location of the dbf Input file.

This utility does not use the SQL Views for the Millennium data tables.

Application Windows

When an appropriate dbf file has been correctly opened, the utility window will show a Title Bar, then a menu bar, followed by a Function Toolbar.

The Menu bar will contain the items, File, Options, and About. The File drop down menu includes the items, Open, Start, and Exit. The Options drop down menu allows you to activate or deactivate the Tool Tips, Toolbar, and Status Bar in the application window. About displays version information for the utility.

The Function toolbar shows buttons used to open a dbf input file, to start the run of the utility, and to pause the run of the utility.

When a dbf has been identified, the utility screen will fill in and appear as shown here.

This screen is used to create the equivalent of a conversion crosswalk, telling the system where to look for the data for the pledge transactions that will be created. The data for these new rows comes from a variety of sources. Some data is automatically entered by the utility and will be standard for all rows. Some is taken from the input dbf file and will be variable from row to row. Some is manually entered in the utility interface and will be standard for all rows.

Using the image shown above, you see that there are different types of text boxes in the utility interface. Some are pull-down list boxes. These will show either the names of the fields from the dbf file, or the entries in the associated Millennium lookup table. Some are not list boxes, but neither are they free text. These require an entry that conforms to the expectations of the utility. Some text boxes can be switched from a listing of the dbf fields in the input file to a listing of the lookup table entries for that field by using the Use input file check box that follows that text box.

If Tender is a specified field from the input file, or Credit Card is selected from the list of acceptable Millennium tender types, the Credit Card Information form will be shown.

If Tender is a specified field from the input file, or Bank Draft is selected from the list of acceptable Millennium tender types, the Bank Draft Information screen will be shown.

Data Requirements and Options

Each of the fields is described below. Note that the description includes certain fields that are automatically created by the system.

Utility Field is labeled:	Maps to:	Utility Requirements:
Type (not displayed)	gifttype	Automatically set equal to Pledge for all new rows.
Secondary Gift type	giftcrdtyp	Manual selection from the pull-down listing of active entries in the Credit Types (credit_types) lookup table for a standard entry for all new rows OR Specified field from Input file for varying secondary gift types for each new row.
Tender	gifttender	Manual selection from the pull-down listing of acceptable tender types OR Specified field from the input file must be Cash, Credit Card or Bank Draft. Tenders will default to Cash if not one of Cash, Credit Card or Bank Draft.
User ID (not displayed)	giftuserid	Automatically set equal to User ID that was used to log in to the ODBC connection.
Source of Payment	giftid	Specified field from the Input file to identify the owner of each pledge row.
Batch	giftbatch	Manual entry must be an active, open entry from the Gift Batch Number lookup table. This will be standard for all new rows.
Campaign	giftcamp	Manual entry must be selected from the pull down listing of active values in the Campaigns table for a standard entry for all new rows.
Campaign Year	giftcampyr	Manual entry of the year (yyyy) of the Campaign for a standard entry for all new rows. This is a four-digit year between 1600 and 2200.
Solicitation	giftsolic	Manual selection from the pull-down listing of active values in the Solicitations lookup table for a standard entry for all new rows.
Amount	giftamount	Specified field from Input file for varying data for each new row.
Gift Date	gifteffdat giftprocdt	Manual entry using yyyymmdd for a standard process and effective date for all new rows OR Specified field from Input file for varying dates for each new row; format of field must be yyyymmdd.
Account Number	giftacctno	Manual entry of an Account Number for a standard account for all new rows OR Specified field from Input file for varying accounts for each new row. Must be a valid, active entry in the chart_of_accounts lookup table (chart_acct field)
Gift Restriction	giftrest	Manual entry of a valid gift restriction code for a standard code for all new rows OR Specified field from the input file for varying restriction codes for each new row. Must be a valid, active entry in the chart_of_accounts lookup table (chart_code field)
Solicitor	giftsolid	Manual entry of constituent ID Number for a standard Solicitor for all new rows.
Special Acknowledgment	giftackflg	Manual selection of an active entry from the Correspondence Name lookup table for standard entry for all rows. The entry of data in this text box will trigger the creation of a linked Correspondence row for each transaction.
Billing Cycle	giftplgtyp	Manual selection from the listing of the active entries in the Pledge Type lookup table for a standard billing cycle for all rows or Specified field from Input file for a varying billing cycles for each new row.
Class 1	giftclass1	Manual selection from the listing of the active entries in the Gift Classification 1 lookup table for a standard Classification 1 for all rows or Specified field from Input file for varying Classification 1's for each new row.
Start Date	giftpstrty giftpstrtm giftpstrtd	Manual entry of date using yyyy/mm OR yyyy/mm/dd format for a standard date for all rows OR Specified field from Input file for a varying dates for the new rows. Note that the Start day is not necessary; it will default to first day of the month. If start day is not specified, the field format must be yyyy/mm. If a start day is specified, the field format must be yyyy/mm/dd
Payments	giftpaycnt	Manual entry of the number of payments for a standard for all rows OR Specified field from Input file for a varying number of payments for the new rows.
Conditional	giftrevoke	Checked or Unchecked - This will be standard for all rows.
Comments	giftcomm	Manual entry of a free text comment may be up to 4000 characters. This will be standard for all rows. OR Specified field from Input file for varying Comments for each Giving data row. (Due to dbf file structure, the input file will limit the comment field to 255 characters.) We suggest that the Comment field include a notation of the particular Phonathon that generated this pledge.
Credit Card Type	credcctype	Specified field from the Input file for varying credit card types for the new rows. Entries must be valid, active credit card types.
Account Type	credbnktyp	Manual entry must be selected from the pull down listing of the active entries in the bank_account_type lookup table for a standard entry for all data rows OR Specified field from the Input file for varying bank account types for the new rows. Entries must be valid, active bank account types.
Routing Number	credroute	Specified field from the Input file for varying bank routing for the new rows. Routing numbers will be validated before data entry.
Bank Name	credbanknm	Specified field from the Input file for varying bank names for the new rows.
Checking or Savings?	credacctty	Manual entry must be selected from the pull down listing of acceptable values for a standard entry for all new rows OR Specified field from the Input file for varying values for the new rows. Valid entries are blank, SAV and DDA only.
Type of Draft/Debit	credcustty	Manual entry must be selected from the pull down listing of acceptable values for a standard entry for all new rows OR Specified field from the Input file for varying values for the new rows. Valid entries are blank, ARC, CCD, PPD, RCK, TEL, WEB only.
Account Number	credccnum	Specified field from the Input file for varying bank account numbers for the new rows. Account numbers will be encrypted by the utility. If not specified, you may elect to have the utility populate this field with the preferred name.
Expiration Month	credexpmn	Specified field from the Input file for varying credit card expiration months for the new rows.
Expiration Year	credexpyr	Specified field from the Input file for varying credit card expiration years for the new rows.
Last 4 Digits of Card/Acct Number	credlast4	Specified field from the Input file for varying last 4 digits of credit card or bank account numbers. If not specified, the utility will parse out the last 4 digits of the Credit Card Number/Account Number specific above, and populate this field for you.
Approval	credapprov	Specified field from the Input file for varying credit card approval codes for the new rows.
Name on Credit Card/Name on Account	credccnm	Specified field from the Input file for varying names for the new rows. If not specified, for bank draft you may elect to have the utility populate this field with the preferred name. This field cannot exceed 50 characters.
Billing Address Line 1	credaddrl1	Specified field from the Input file for varying address line1 values for the new rows.
Billing Address Line 2	credaddrl2	Specified field from the Input file for varying address line 2 values for the new rows.
Billing Address City	credcity	Specified field from the Input file for varying address city values for the new rows.
Billing Address State	credstate	Specified field from the Input file for varying address state values for the new rows. Must be a valid, active code in the places lookup table.
Billing Address Zip Code	credzip	Specified field from the Input file for varying zipcodes for the new rows.
Billing Address Country	credcntry	Specified field from the Input file for varying country values for the new rows. Must be a valid, active code in the countries lookup table.
Phone Number	credhphone	Specified field from the Input file for varying phone numbers for the new rows.
Email	credemail	Specified field from the Input file for varying email addresses for the new rows.
License Number	creddlnum	Specified field from the Input file for varying driver license numbers for the new rows.
License State	creddstate	Specified field from the Input file for varying driver license states for the new rows. Must be a valid, active code in the places lookup table.
Birth Date	credbdate	Specified field from the Input file for varying birthdays for the new rows. Must be formatted yyyymmdd in the input file.
Social Security Number	credssnum	Specified field from the Input file for varying social security numbers for the new rows. Social Security Numbers will be encrypted by the utility.
Federal Tax ID	credein	Specified field from the Input file for varying federal tax ID numbers for the new rows.
Do Not Import Transaction	none	The Paya Payment Solutions Pledge/Sustainer Payment Processing Tool requires, for successful automatic processing of credit card transactions, that these fields have valid data: Credit Card Type, Credit Card Type, Expiration Month, Expiration Year, Name on the Credit Card, Billing Address Line 1, City, State, and Zip code. If you do not want to import transactions that do not have valid data in all required fields, then select this check box. The Paya Payment Solutions Pledge/Sustainer Payment Processing Tool requires, for successful automatic processing of bank draft transactions, that these fields have valid data: Account Type, Routing Number, Account Number, Name on the Account, Billing Address Line1, City, State, and Zipcode. If you do not want to import transactions that do not have valid data in all required fields, then select this check box.

When all fields are filled in appropriately, you may execute the run of the utility.

Running the Utility

When all data has been appropriately entered or mapped in the utility application window, the run may be executed by clicking the Start Button or by clicking the File menu Start item.

If required data is missing from the application window, the utility will not launch. The application window will be displayed with the cursor positioned in the missing data text box. You must enter data and start the utility again.

As the utility is executing, the application window will display status information.

Number of records - indicates the number or records in the dbf file
Pledges made - indicates the number of pledge transaction rows that have been created. This number should change as the utility executes.
Time Started - indicates the time that the utility began to execute.
Records Read - indicates the number of the records in the dbf file that have been read. This number should change as the utility executes.

A flood bar is also displayed to show the progress of that run.

When the utility is finished, a completion message is given. An error log is automatically generated in the same directory from which the utility was launched (Inetpub\WWWRoot\Mill\Tools). This log file will be named Phonathon YYYMMDD hhmm ampm.txt. For example, if the utility is run on 6/26/2000 at 4:30 pm, the filename would be Phonathon 20000626 0430 PM.txt. This log will list any errors encountered with any data from the import file, as well as any SQL errors encountered during the run. If no errors were encountered, this file will be blank.

Instructions

To run the Phonathon Pledge Utility, you must have access to a dbf that has been created based on the data in your database (matching ID Numbers, lookup table codes, etc.) and from a workstation that conforms to certain requirements. For more information, see the specifications for the input file or the workstation requirements.

Use Windows Explorer to locate the phonathon.exe file in the inetpub\wwwroot\mill\Tools folder and double click to launch it. The utility window will open.
Click the Open File icon on the toolbar. Or, on the File menu, click Open. You will be prompted to open the dbf input file. Navigate to its location, highlight it and click on Open.
You will be given an ODBC log in window. Select the Machine Data Source tab, select Web SQL from the list of Data Source Names, and select OK. You will then be asked to provide a Login ID and provide a password. You must log on as either the database administrator (sa or MILLORA User ID) or as a Millennium system administrator. Do so, and select OK.
When you have successfully connected to the database via ODBC, the utility window will show the complete set of available text boxes.
If you want to enter or map the gift account number instead of the gift restriction, select the Use Account Number item from the Options menu.
Enter the desired data in each text box. For more information on each, see Data Requirements and Options. As a minimum, you must enter data for the Source of Payment, Amount, Deductible Amount, Account Number, and Date.
When all data is appropriately entered or mapped, begin the execution of the utility by clicking the Start Button . Or, on the File menu, click Start. The application window will show status information as the utility progresses. When it has finished, click to OK the Completion message.
Review the Corebio.txt file that is created in the same folder where the utility is located for a log of those records in the input file that could not be matched to constituent ID's in the database.
Review the error log file named Phonathon YYYMMDD hhmm ampm.txt (using the date and time that the utility launched to replace the variables) that is created in the same folder where the utility is located for a log of any SQL errors that may have been generated, and a log of those records in the input file that generated data errors. If no errors were produced, this log file will be blank.

Top of Page