In This Section:
Lockbox is an optional module which imports data into PledgeMaker. Lockbox can import batches of gifts and payments, new donors, address changes, event registrations, and much more. For a complete list, see the table at the end of this document. Your organization must have licensing to both Lockbox and the associated module (e.g., Membership) in order to import data.
The Lockbox module allows the PledgeMaker user to map how information should be imported using customizable file definitions. Lockbox setup for definition of files can be accessed by clicking [lockbox] under the Utilities branch of the main navigation tree.
With the definition in place, Lockbox can be scheduled to run at a particular time and/or interval using the scheduling screen.
External files can be comma or tab delimited, fixed length or XML. It is possible to call a custom procedure to pre-process files before they are imported. Once the files are processed, the batch files can then be viewed in PledgeMaker Batch Entry screens if desired, and/or they can be posted to the database using Batch Posting. Processing automatically renames the processed files so processing occurs only once.
For a very basic overview of how to import donations, see the Lockbox Quick Start document, linked here .
Lockbox supports a number of features that give flexibility and accuracy in importing and updating data from various vendors and sources.
• Automatically creates new Prospect records as needed.
• Performs duplicate checking in creation of Prospect records.
• Imports donations, pledges and/or payments.
• Imports detailed order information.
• Can handle processed or unprocessed credit card payments.
• Imports/updates names, addresses, emails, and phone numbers from new data.
• Imports one or more premiums with each gift or membership.
• Allows optional address standardization.
• Can automatically translate vendor codes to PledgeMaker codes using the translation table.
• Can assign an appeal to a gift given a package code.
• Can include pre-processing of text files.
• Applies capitalization to imported text per user preference.
• Imports fixed length, comma-delimited, tab-delimited or XML files.
• Saves a detailed batch log file which notes the batches created and errors or problems encountered.
• Renames processed files so processing occurs once for each file.
• Creates a batch entry file which can be viewed and verified in PledgeMaker before posting to the on-line records.
• Stores definitions for importing files so they can be re-used for repeated imports from specific vendors.
• Allows creation of a new definition based on the current definition.
• Allows the user to set defaults for any viable data field.
• Converts from any standard date or currency format.
• Can be scheduled.
The Lockbox Process
The Lockbox process requires that the user set up file definitions and processing schedule(s) to import data. Once the data is imported, the log file must be checked for errors in processing. Corrections should be made for any errors, and finally batches should be posted.
Note - Encountering Codes Not Available in Table Maintenance
If codes which the user attempts to import are not present in Table Maintenance before the import, users may experience various errors or failure of the import (e.g., constraint errors).
Importing Data with Lockbox Processing
1. Be sure the appropriate codes are established in Table Maintenance (e.g., Appeal, Fund, Order and Gift Type codes for the transactions included in the file) and that necessary translation values are set up in the translation table.
2. Contract with a vendor to provide services and compatible files for import into PledgeMaker. See the dedicated submission form document for lockbox files and the File Compliance and Available Fields List sections of this document for more information on what to provide for the vendor.
3. Define the layout of the vendor's Lockbox file using the Lockbox Processing screen and Detail Fields. An existing file definition can be chosen for the vendor if it has already been created. See the sections on File Definitions and Lockbox Screens, The Lockbox Field Definitions Screen, and Adding / Editing a File Definition for more information on this setup.
4. Place the file(s) to be processed in folder(s) appropriate for processing. These folders and file names will be specified in the Lockbox definition.
5. Schedule Lockbox. This schedule will initiate processing of the text file and create batch records according to the definition.
6. View the log file created during processing to check for processing errors. These errors will generally consist of errors in the file definition or data file. Certain user errors may require re-running the process before continuing (e.g., selecting the wrong definition for processing the file).
7. Check for batch detail errors using the Batch Error Report (or similar report).
8. View the batch(es) in PledgeMaker's Batch Processing screens to make any necessary corrections. Corrections for some data file errors can be made at this point (e.g., inserting any transaction imports which failed due to missing data).
9. Resolve potential duplicates (choose [duplicate resolution] from under the Batch branch of the main navigation tree).
10. Post the batch(es) using Batch Posting (choose [posting] from under the Batch branch of the main navigation tree) after errors and potential duplicates have been addressed.
Note - Addressing Log File Errors
Appropriate steps should be taken to address any data conflict outlined in log files before posting batches.
File Definitions and Lockbox Processing Screens
Create the file import definition using data file and layout information provided by the vendor. A lockbox definition can be created either by replicating an existing definition or by creating a new one. Replicate can be helpful when you have files with similar layouts. See below for an explanation of the Replicate toolbar button.
Note - File Names and Location for Lockbox Processing
File and definition names are case sensitive. Be sure they match exactly in the definition. If the names are not exact, the process can fail. Though there is no length limitation for file names, avoid using spaces or underscores in the file names for best results. Files should be located in the Input File Path directory for processing.
To begin a new file definition, access the Lockbox Add screen. This can be reached by clicking [lockbox] under Utilities branch of the main PledgeMaker navigation tree, and then clicking the [new] button when the Lockbox History screen appears. This opens the Lockbox Add screen.
From this screen, you can create a new file definition. This screen has two tabs that provide areas for entering general information. Buttons on each of the tabs provide access to additional screens for field and header descriptions.
Note - Input File Path Values
The Input File Path values are determined in Path Usage Maintenance. Path Usage Maintenance can be accessed under Security under the Utilities branch of the main PledgeMaker tree ([utilities] > [security] > [path usage maintenance]).
Definition Name. New definitions can be created with a name only; other information will be necessary before the definition is viable for processing. The name should be entered in the Definition Name field using up to 10 characters. If the name is already taken, PledgeMaker will alert the user that a unique name should be entered. If the definition has been saved previously, the Definition Name will not be editable. If the user requires that many changes be made to the description, it may be best to enter a new definition - especially if there is a chance that the definition chosen for editing might be reused.
Note - Absence of ID Number
If there is no ID Number in a row when a record is being imported through Lockbox, and there is a Last name, the record is assigned an ID Number by PledgeMaker.
File Location and Format. Enter the name of the file to be processed in the Input File field. The file name must match the name of the file to be processed, and should include the extension (e.g., filename.txt). Wildcards like an asterisk (*) can be used in matching filenames, but special care should be taken not to include wildcards that encompass the file extension (so 'file*.txt' is ok, but 'file*' is NOT). Wildcards can also be used in the file name to process multiple files (see the Processing Multiple Import Files sidebar later in this document).
Note - Lockbox Input File Re-Naming
Lockbox renames files when processed so that processing will not be duplicated. All files are renamed by replacing the existing extension with .sss.
Select the file path from the Input File Path drop list. The input file path is limited to those paths specified for use by Lockbox in [security] > [path usage]. You will not be able to [continue] to run Lockbox until both the file name and file path are entered. For further information on input file paths see the Path Usage document.
Warning! Lockbox File Naming
Lockbox renames processed files using the extension .sss. This extension should never be used in the Input File field as the name of the original file.
Select the format of the input file from the radio buttons listed under Format. Import files can be Fixed (fixed length), Comma (comma-delimited), Tab (tab-delimited), or XML. The choice assumes that the file to be imported by Lockbox complies to the standards of the file type, and does not contain illegal characters. The file type can not be changed once the Lockbox definition has been saved.
Note - Update Prospect Checkbox
The Update Prospect checkbox is on the Data Types panel of the Main tab. Unchecking the box will cause PledgeMaker to ignore Prospect information in the file when a match is found for the import row. Only new information will be imported. Unchecking the box is most useful when matching on Reference number, when importing data where some names are new and some already exist (e.g., eDonations imports).
Match On. The Match On control allows the user to specify what values in PledgeMaker will be used to determine matching for imported records. If ID Number/Reference is selected to match on, PledgeMaker will look at Acquisitions, ID Number, Audit Merged, Reference and Alias for matching an imported record to existing information. If Reference is selected for Match On, PledgeMaker will look at Reference and Alias only. This functionality works in conjunction with the Substitute Duplicate checkbox in cases where duplicate checking is initiated and a single distinct match is found. See the Substitute Duplicate Checkbox and ID Number/Reference Matching Controls in this document for more information.
Note - ID Number Links
If PledgeMaker is looking for a prospect ID number for other fields that use IDs (e.g., Honor/Memorial party, Unit, etc.), Reference and Aliases will be checked if matching to an ID fails. If imports include a unique reference, this makes it possible to import a new prospect in one row of a file and make that new prospect the Honoree in the gift in the next row.
ID Number / Reference Matching Controls
If ID matches Acquisitions: Create prospect, apply transactions and run duplicate check*
If ID matches Reference, Alias, Prospect Audit Merged ID or IDNumber: Apply transactions to existing prospect
If no ID is present, and Last name is present: Create prospect, apply transactions and run duplicate check*
If no ID is present and no Last is present: Create record using lockbox default ID
* If the Substitute Duplicate box is checked and exactly one match is found in duplicate checking, transactions are applied to the matching prospect.
Match Alias. The Match Alias control allows alias type to be specified for matching purposes. If a type is specified then PledgeMaker will only use prospect alias records with that type when searching to find an ID Number.
The default ID# entered on the definition will be assigned to all transactions where there is no ID# match. It is useful to set up a Prospect for the purpose of accepting these transactions.
1) Create a Prospect to use as the Lockbox Default. (e.g., named Lockbox Default). Note the ID#.
2) Enter that Prospect ID# created in step 1 as the Default ID# when creating the Lockbox definition.
3) After creating the batch by successful Lockbox import, and before posting, check the batch for all transaction rows where the default ID# was used and make corrections to the entry(ies).
In cases where there is no specific desired default (e.g. Address Line 1), 'NONE' can be used. To cause Lockbox to halt processing when an invalid code is encountered, do not enter a default.
Duplicate Check Checkbox. This option allows you to control whether or not duplicate checking is to be performed. The box defaults to checked (duplicate checking will take place); uncheck the box to disable duplicate checking for prospect records imported by this lockbox definition.
Substitute Duplicate Checkbox. This checkbox allows the user to specify how to handle situations where the duplicate checker finds exactly one potential duplicate when duplicate checking is invoked. If a distinct match is found and the box is checked, imported data is applied to the distinct match (see details in ID Number/Reference Matching Controls). If the box is not checked, data will not be applied even if there is only one distinct match and all records will have to be addressed as part of Duplicate Resolution ([batch] > [duplicate resolution]).
Standardize Address Checkbox. If the user system has eDataQuality functionality present, checking the Standardize Addresses checkbox will standardize information in the import file before the import. This can help attain better accuracy in duplicate checking, and will assure that imported address information is correct.
Data Types. Making selections for Data Types on the Main tab of the Lockbox screen tells PledgeMaker what type of data the import file will contain (e.g., what the user wants to import from the information available in the file). Selections also affect the fields that will require default values. Fields will automatically be populated into the Field Definitions screen based on user selection. For more information about using the field definitions, see the Lockbox Field Definitions Screen section in this document.
Effective Use of Duplicate Substitution
The Substitute Duplicate option will be most effective under the following conditions:
1) All addresses in PledgeMaker are standardized
2) Imported addresses are standardized before or during the lockbox import
3) Duplicate check criteria are set strictly enough to avoid false positives (potential duplicates that are not true duplicates)
The Additional Tab.
The Additional tab allows entry of additional file information.
Header and Trailers. Header and trailer information in a file may be additional information that the user may or may not find necessary for successfully importing the data. The header and trailer information can include (but is not limited to) control information, batch number, batch date, pledge and payment defaults, field names included in the file, information about the generation of the file, information about the vendor, etc.
Note - Data Row Identifiers
Data row identifiers are not required if all rows are detail rows.
Header and trailer Data file records must be identified by identifiers at the beginning of each row of data to identify the data as detail, header or trailer information. These codes are entered as the identifier in the ID field. Identifiers may be up to three characters, and should be entered as they appear in header or trailer rows. Length fields associated with the identifiers will populate automatically after the identifier code is entered and the user exits the field (e.g., by tabbing). Identifiers are not required if all rows in the file are details. Boxes for Header and/or Trailer should be checked if header or trailer information is included in the file. Checking the Process box will include the information when the file is processed.
Batch Number, Detail Count, Total Payment, Total Pledge and Batch Date can be defined on the header Fields screen. Clicking the [fields] button opens the screen. The Header Fields screen allows definition of imported header information for batches. Lockbox will use Batch Header defaults even if the file does not contain Header records.
Note - Fields Available in the Header Definition
Depending on the format selected for the definition, different fields will be available on the Header Definition screen. For fixed length files the Start and End values are required (the length is calculated and cannot be changed). Comma and tab delimited files require entry of the data sequence position. Data sequence position is also used for XML files, but does not have to be sequential - providing a position value serves as an indicator for Lockbox to look for the field in the file.
Formats for Total Payment, Total Pledge and Batch Date must be chosen from the drop-down list of selection. Pressing [f9] when in the Data Format field for each of these items will open an LOV so formats can be selected. Clicking [continue] will exit to the user to the Lockbox screen. The user must save the definition (click the [apply] or [ok] button on the Lockbox screen) for the header field changes to be committed with the definition.
Note - Batch & Batch Header Default Dates
Batch Date comes from the B_DATE field in the data file. If this is blank, Lockbox uses the B_DATE default. If there is no default, Lockbox uses the current system date.
Gift Date comes from the D_DATE field in the data file. If this is blank, Lockbox uses the D_DATE default. If there is no D_DATE default, Lockbox uses the B_DATE field in the data file. If this is blank, Lockbox uses the B_DATE default. If there is no B_DATE default, Lockbox uses the current system date.
Note - Defining the Batch Number
There is no time when the Batchno (Batch Number) is required. It can be defined in the Header or the Detail. DO NOT define the Batchno in both the Header and the Detail as this will cause conflict and failure of the import.
Trantype Data Translation. The Trantype Data Translation panel on the additional tab allows the user to convert transaction codes provided in the import file to standard PledgeMaker transaction codes. The defaults for the fields are the PledgeMaker standard transaction codes, but these can be changed to match the codes from the data in the file. Any code of up to 8 characters can be entered.
Capitalization. Capitalization options will affect changes to the imported data by capitalizing the first letter of every word (Title), capitalizing every letter (UPPER) or leaving the data as it is entered into the file (no change). These options affect name, address, and acquisition fields only.
Default Header Status. The default setting for the batch header created for the import is controlled using the Default Header Status drop list. The header value can be On Hold or Ready to Post for each header created when a file is processed using this definition. Selecting On Hold will allow the user to check over the batch before marking it as Ready to Post. Selecting Ready to Post can streamline posting and make imported batches immediately available for scheduled batch posting. For more information about posting see the dedicated Batch documentation. For information about scheduling, see the dedicated Scheduling document.
Note - Lockbox Batch Header Defaults
The Lockbox Batch Header Defaults will be used whether or not the Process Header box is checked, and whether or not there is a header record in the file. If the Process Header flag is checked, any header fields (Batch Number, Batch Date, Control Totals, Count) which are entered on the Header Fields screen should either have position information, or default values, or both.
It is not necessary to get all of the header information from the file. For example, the Batch Date can come from a header record in the file, and the Batch Number can be assigned by Lockbox. To accomplish this, the Header Fields screen should show only the Batch Date field.
Error Mode. This option allows users to choose to either stop the import process at the first critical error or continue and log all errors before terminating. This option affects only the log file. On either option, if a critical error occurs, nothing will be saved into the PledgeMaker database. It is usually best to stop at the first critical error when you are testing a new import because otherwise an incorrect import definition will result in the same error being repeated for every record in the file. It is usually best to continue and log all errors when you are using a previously tested definition for a routine daily or weekly import because this will make it possible for you to correct all of the problems at once so that a second attempt to import the file can complete successfully.
Unit. Unit ID allows users to associate the current definition with an organizational unit for security purposes. This Unit ID is not a default value for imported records.
Audit Traffic. Optionally captures API transaction history for use in trouble-shooting and general usage auditing.
Note - Skipping Batches
Skipping Batches if a batch number in the lockbox file already exists as a batch in PledgeMaker, records with that batch number will be skipped. The log file will contain a line like the following: ERROR: Batch #999888 already exists and was skipped.
Additional windows and functions related to lockbox entry are accessed by clicking the respective toolbar buttons. The tool bar buttons will be available in various places depending on the current screen and applicability of the function/screen. These buttons include Delete, Replicate and Audit.
Delete. Clicking the [delete] button on the toolbar will initiate deletion of the current transaction.
Replicate. Clicking the [replicate] button on the toolbar opens the Replicate Definition screen for the lockbox definition record. The screen allows the user to name and create a new lockbox definition using the existing definition.
Audit. View audit information for a lockbox definition record, including when it was created, who created it and when it was last updated.
The Lockbox Field Definitions Screen
The Field Definition screen contains parameters that tell PledgeMaker how to interpret the data rows in the import file. The screen lists the details for the fields to be imported, including how to identify the data element (Sequence, Start, End and Length), Alias Field Name, Table name, Default Value and Data Format. Access the Field Definition screen by clicking the [fields] button on the Additional tab of the Lockbox screen. This button becomes available only after the Lockbox definition has been saved.
Note - Fundcode Default
Fundcode comes from the FUNDCODE field in the data file. If this is blank, Lockbox uses the FUNDCODE default. If there is no FUNDCODE default, Lockbox uses the fundcode from the Appeal.
Note – Appealcode Assignment
Lockbox will assign an appealcode to a gift, pledge or payment transaction based on a package code. For this to take place the file must contain a package code mapped to gift_packagecode, and no appealcode; the lockbox definition must include a default appealcode. If the packagecode is a white mail code, the appealcode will be assigned based on the appealcode associated with the package in the appeals table. If the transaction has a corresponding mail record, appeal will be assigned based on the mail record. If neither of these conditions are met, lockbox will assign the default appeal to the transaction.
On being opened, the Field Definitions screen is pre-populated with fields that are required based on the data types selected on the Main Tab of the Lockbox definition screen. For instance, if you select 'Address', the Field Definitions screen will populate with the ADR_PREFERENCE, ATYPE, LINE1 and IDNUMBER fields. Any address added to PledgeMaker must contain values for these fields, either in the file passed to the Lockbox or as defaults in the Lockbox definition.
Note - PledgeAmount vs. GiftAmount
Use the pledgeamount field in Lockbox only when importing pledges. Use giftamount when importing cash (both donations/gifts and payments on pledges).
Fields are navigated using the [tab] and arrow keys. To edit a field definition, switch to Edit mode by clicking the  button, and type the desired changes directly in the field. Each row in the field definition represents a column or data element in the import file. Enter a field definition sequence if a data element is included in a comma or tab delimited or XML file; enter a start and end position for fixed-length files. XML file tags must also match the field alias for the data element to be processed. Rows with format variables (e.g., dates, numbers/decimals, etc.) require Data Format selection. Available formats are selected by placing the cursor in a Data Format field and pressing [F9]. The cursor can not be placed in a Data Format field where a data format is not required.
Note - Pledge Restrictions
Lockbox rows should not contain both Pledge and Pledge Payment information. Pledges and Payments should be provided in separate rows. New pledges with accompanying payments can both exist in the Lockbox import file. If both pledges and payments are contained in a single file, they must be in separate records, with appropriate trantypes, and the payment must come after the pledge.
To enter a new row, press the keyboard down arrow until the cursor enters a new (blank) row, tab to the Alias Field Name column and press [f9] to select from a listing of available fields. Selections available on the drop-down menu for Data Format will change as appropriate for the data type (e.g., dates or numbers).
To remove existing rows, highlight the row to be removed and click the [delete] button on the toolbar.
To Create a New File Definition
1. Locate the file description provided by the vendor, and keep that on hand to use as reference when setting up the field definitions.
2. Open the Lockbox Processing screen by selecting [lockbox] from the Utilities branch of the main PledgeMaker navigation tree.
3. Enter the Name of the file layout in the Definition Name field.
4. Describe the file/definition in the Note field. This can clarify the purpose of the definition. The Note screen can be opened using the note view button.
5. Input the File Name for the data source in the Input File field. This should match the name of the file provided by the vendor (e.g., filename.txt).
6. Select a file path from the File Paths drop list. These selections reflect information in Path Usage Maintenance.
7. Choose a Match On option from the Match On drop list.
8. Check the Substitute Duplicate box if it is desirable for potential duplicates that have only one potential match to be treated as an exact match.
9. Check the Standardize Address box if it is desirable to standardize addresses before importing information.
10. Select the format type for the data file by clicking the Comma, Fixed, Tab or XML radio button. The selection should match the file type of the input file.
11. Select the data type(s) you wish to import from the Data Type(s) checklist.
12. Click the Additional tab.
13. Check the Header Record flag if a header is included in the data file.
14. Check the Trailer Record flag if a trailer record is included in the data file.
15. Enter IDs for the header, trailer, detail and sub-detail rows if necessary. Header and Trailer IDs should be entered if these boxes are checked. Detail ID should be entered if either Header or Trailer is checked.
16. Click the Process checkbox if there is header information that should be processed with the file.
17. Change the Gift Codes according to the transaction type codes used by the vendor creating the file. Options are available for setting codes for Pledge, Pledge Payment (Pledge Pay), Donation, Lifetime Pledge (LT Pledge) and Lifetime Payment (LT Pledge Pay). These default to standard PledgeMaker codes.
18. Select a method of capitalization.
19. Enter a Default Header Status by selecting a value from the drop list.
20. Commit the changes by clicking the [apply] button to store the definition before continuing.
21. Click the [fields] button on the Additional tab to define the file layout and field parameters for the import file. You will need to add data position, length information and defaults for each field.
22. Commit the changes and exit the screen by clicking [ok] or [apply] and then [close].
23. Access the Header Fields screen by clicking the [fields] button on the Data Import panel on the Additional tab. Define the parameters for the file header information as necessary.
24. Once the header is defined, click the [ok] button to return to the Lockbox screen.
With the setup of the definition complete, processing can be scheduled. Click the [continue] button at the bottom of the Lockbox screen to get the Lockbox Scheduler screen. For more information on using the Scheduler see the dedicated Scheduler documentation.
The Lockbox Log File. When Lockbox is run, it generates a log of information which details the processing, including: a list of Batch numbers for the batch(es) created, the number of records inserted in each batch, the total Amount inserted, and the number of invalid records. Log files will also list errors. For more information about log files see the dedicated Log file documentation.
Log File Error Listings
An integrity constraint error suggests data or default values do not match existing PledgeMaker codes; an error processing details suggests an error in the data, or in the format or layout of the fields in the definition.
Processing Multiple Import Files. Wildcards (*) can be used to process multiple files in a specific directory. Enter the wildcard character in the appropriate part of the file name. If processing several files named with similar naming schemes (e.g., based on date and vendor), characters and Wildcards can be used in combination to both limit which files get processed and select multiple files. Examples below show the results of different Input File names on a set list of file names.
Lockbox Batch Detail and Batch Error Reports. Batches created by Lockbox can be viewed in Batch Detail reports. These reports list details of the batch(es), errors, and the ID Numbers of potential duplicate records. Batch Detail reports are located under the Batch branch of the Reports navigation tree ([reports] > [batch] > [batch detail]; [reports] > [batch] > [batch detail breakdown]). To view batch errors only, use the Batch Error report ([reports] > [batch] > [batch error report]), which lists batch number and errors only. These reports will allow the user to view the results of batch ranges, date ranges and/or batch type. Select Lockbox as the Batch Header Type to view only Lockbox batches. Batch Detail Breakdown may also be sorted by Appeal, Fund, or Gift Type. All reports can be scheduled and emailed to appropriate recipients. For more about running reports, see the dedicated Reports documentation.
Handling Record Imports & Duplicate Resolution
Note - Duplicate Checking
Duplicate checking is employed when a new Prospect is created either from Acquisitions or directly from import through Lockbox.
Note - Batch Posting and Duplicate Resolution
Duplicate resolution should be performed before posting associated batches. PledgeMaker does not allow batches to be posted until duplicates are resolved.
For each new prospect created, Lockbox runs duplicate checking using the Organizational settings for Duplicates. Duplicate checking occurs after triggers have been fired so that any changes occurring to the prospect or address are taken into account. If a match is found, the matching records will be made available in the Duplicate Resolution function, accessible through the Batch branch of the main navigation tree. If no match is found, the prospect is created. If the Substitute Duplicate box is checked on the lockbox definition, transactions on imported rows with exactly one match will be committed to the matching record without needing to be resolved in duplicate resolution. For more information, see dedicated Duplicate Checking Documentation.
Name Parsing. This is an optional feature that allows name cleansing of the data in the import file. Name cleansing has features to parse, identify and enhance name data such as donors' names, titles, organizations, gender, etc. If the last name does not exist in the file then the full name will be parsed to create the last name. Additional licensing is required for this feature.
The Data Translation Table
The File information being imported through Lockbox can be translated during the import to different, acceptable or standardized values using the Data Translation table. This translation takes an old file value and converts it to a new value to be stored in the database. The data translation table holds user-customized values that will be converted during the import. This can allow the user to standardize such data as the spelling and capitalization of last names. For example, 'DEPAUL' would normally default to 'Depaul' using standard Title capitalization. The translation table would allow the user to insert 'DePaul' as the translated value.
To use the translations, values to be converted have to be entered into the Data Translation table. The translation table requires the value that will be translated (old_value) the field the value will be found in (description) and the value that should be substituted (new_value). Contact Support for Assistance in setting up these translations. Lockbox looks at these translation values during the import. If a match is found to an old_value in the column noted in the description, the new_value is used as the translated value.
Data Translation Description Field List
The fields included in the translation process when Lockbox is run include the following. One of these values must be used in the Description column to pose a valid translation.
To Translate Imported Values
1. Enter the values to be translated in the Data Translation table. Old_value is the value in the data file; Description is the field where the value can be located; New_value is the value that will be substituted upon encountering a match.
2. Run Lockbox. Translation values will be detected and replaced automatically during processing.
Lockbox allows import of subdetails as separate records in a file for premiums or orders (see Importing Orders or Premiums section of this document), or as multiple details in an XML string. Records that qualify for XML sub-detail import include order details, premiums and registration fees. To establish a lockbox defination that will accept sub-details, the format must be XML and the Sub-Detail Data Import field on the Additional tab must contain a value – note: the value entered in this field does not matter, so long as it is not blank.
Import of this file will result in a single order containing details for ITEMA, ITEMB and ITEMC.
Lockbox imports orders with single or multiple details.
Pricing. Prices and shipping amounts must be provided in the import file or defaulted by the definition. Lockbox does not get default, sale, or discounted prices from Inventory.
Extended line price and total order price are calculated by Lockbox and cannot be directly imported. Unit price must be included on the import record or defined on the definition. This value will be used to calculate total price and pricecode will be set to CUSTOM.
Transactions. When Lockbox is importing a file and encounters an Order (trantype) record, it will insert a batch detail record with trantype O (order). For each Order, Lockbox will insert one order detail record for each itemcode in the file. If there is no itemcode in the file, no order will be created, but a donation will still be created for any giftamount included on the Order import record.
If the file consists entirely of one-line (single item) orders, the itemcode can be included on the Order import record. If any orders in the file are multi-line orders, all itemcodes must be on sub-detail lines.
The appealcode must be included on the Order row or defaulted in the definition; it cannot be on the sub-detail (item line) rows.
Note - Gifts and Payments Included with Orders
Lockbox will add additional transactions to batches being created from order imports if the following are true for any order:
If GIFTAMOUNT > 0, Lockbox will insert a batch detail record with trantype D (donation).
If PAYAMOUNT > 0, Lockbox will insert a batch detail record with trantype IP (invoice payment).
Payments. Any payment method supported in PledgeMaker, including credit card, can be used with order payments, as with any other payments. Payment amount must be included on the order row, or the order will be created as invoicable (with a balance due). The payment amount must include any gift amount which is part of the Order record.
The Book/Ship credit card method, optionally used in Batch Entry for orders which are not prepaid, is not supported by Lockbox.
Backorders. If quantity available is not sufficient to cover the order, that line of the order is placed on backorder. The line split option to ship available quantity and place the remainder on backorder is not supported by Lockbox. The Guidelines for Lockbox Order Imports expands on these details.
Assemblies. Pick-assemblies are supported by Lockbox. Import the itemcode of the assembly; Lockbox will add the components to the order.
Pre-assemblies are also supported by Lockbox, but are handled like non-assembled items, just as in Batch Entry.
Use of a pick-assembled item as the definition default itemcode is not supported. Template-assemblies are also not supported by Lockbox.
Note - Guidelines for Lockbox Order Imports
The following are possible cases for file information that will be handled by Lockbox and suggested procedure for handling. Please consult SofTrek support for more information and help in setting up successful imports if necessary.
Case 1: Import files contain only Order transactions, no gift amounts, single item in each order. The trantype can be defaulted to Order in the definition. Do not enter a sub-detail identifier if the itemcode is part of the order row in the file. Check the Orders data type, but not the Gifts data type.
Case 2: Import files contain only Order transactions, with gift amount, single item in each order. The trantype can be defaulted to Order in the definition. Do not enter a sub-detail identifier if the itemcode is part of the order row in the file. Check the Orders data type and the Gifts data type.
Case 3: Import files contain only Order transactions, with gift amount, multiple items in each order. The trantype can be defaulted to Order in the definition. Enter a detail identifier, which must be in the file on the Order rows. Enter a sub-detail identifier, which must be in the file on the item rows. Check the Orders data type and the Gifts data type.
Case 4: Import files contain a mixture of Order and Donation transactions. The trantype must be in the file. Check the Orders data type and the Gifts data type.
Case 5: Import files contain only IP (Invoice Payment) transactions. The trantype can be defaulted to IP in the definition. Check the Gifts data type.
Lockbox can import Additional Greeting records. Only one greeting per solicitor code is allowed. Additional Greeting fields will be available in the definition when Prospect is checked. A greeting must be included in the file for additional greetings records to be created.
Address, Email and Phone
Address, phone and e-mail records can be imported or updated via Lockbox. A data type to add e-mail addresses does not exist on the Lockbox main tab. To make e-mail fields available in the field definitions screen, select the 'Address' data type. To update instead of import a record, the transnum or an exactly matching e-mail or phone number should be included in the file.
When an address, phone or e-mail record is imported, the defaults to make the new record 'Preferred', though this can be controlled by setting a Preference value in the import file or as the field definition default. The new record is made 'Preferred', the existing 'Preferred' record is given an end date and its 'Preferred' flag is changed to 'No'.
Duplicates are not created. When the new address, phone, or e-mail would be an exact duplicate of an existing record, the new record is not imported. Only key fields - not all fields - are considered when determining whether the new record would be an exact match.
Appeals and Funds
Lockbox can serve as a loader for appeals and funds. This makes it possible to create all of the appeals for a campaign, including fully detailed segmentation criteria, based on an external spreadsheet file.
In addition to creating new appeals, Lockbox is able to update existing appeals. When the appealcode in the file matches an existing appealcode, that appeal is updated based on information in the file (not defaults). When there is no match, a new appeal is created. The same applies for funds.
Project Elements fields are available in appeal import definitions. Note that if elements and appeals are being imported in the same file, then APPEAL and ELEMENT_APPEALCODE must both be explicitly mapped to the file in the Lockbox definition. Project Elements can also be updated by selecting the ‘Appeals’ data type; to update the element’s transnum must be included in the import file.
Lockbox can create and update Bio records. The Bio fields are only available in the definitions that have either Prospect or Update Prospect checked. A Bio record will be imported or updated when any Bio field has a value; there is not a required field.
Lockbox can create Committee and Committee Assignment records. Lockbox will also update both Committee and Committee Assignment records if they exist. These fields only become available when the Committee data type is checked.
Lockbox can import contact records. In addition to creating new contact records, Lockbox is able to update existing contacts when given the transnum.
Lockbox can create and update inventory items and components. Lockbox can also create inventory adjustment records. An Item code must be included in the file for these inventory records to be created. In addition, quantity is required for Inventory Adjustments and componentcode is required for Item Assembly records. Lockbox checks for an existing items record with that itemcode, or an Item Assembly record with that itemcode and componentcode. If no record exists, then a new record is created, if a record does exist then the existing record is updated only with fields that have a value in the file.
Lockbox can create and update Mail records, and can create Mail Response records. The Mail and Mail Response fields only become available when the Mail data type is checked. A Mail record is created when mail_appealcode has a value in the file, provided there is not already a mail record for the same idnumber and appealcode with a mail_status of E(xported) or M(ailed). If mail_transnum has a value in the file, then the corresponding mail record will be updated. On update, only fields which have a value in the file will be changed. Lockbox will attempt to create a Mail Response record when mailresp_item, mailresp_appealcode or mailrsp_opt_out have a value. A mailresp_mailid OR an idnumber + mail_appealcode must exist in the file for a Mail Response record to be imported without error. Duplicate Mail Response records are allowed and will be created; there are no updates of these records.
Lockbox can import Memberships and Gift Memberships. These transactions must be identified to Lockbox by an M or GM trantype code in the file or as the definition default. The membership amount is calculated based on Type and Length; not imported from the file. The payment amount must be included in the file; it is not calculated by Lockbox.
Membership renewals can be accomplished by importing a membership as though it were new. Batch posting will determine whether the transaction is a new membership or a renewal. This is similar to entering a membership in Batch Entry without using the Application screen to select a specific membership for renewal.
Payment information, including credit card information, can be part of the Membership or Gift Membership import. In the import file, the payment information should be on the same row as the membership information.
It is also possible to import payments for existing membership transactions. To accomplish this, check the Gift data type option in the Lockbox definition and map the payment fields appropriately. These payments must be identified to Lockbox by an MPP trantype code in the file or as the definition default. Do not check the Membership data type unless Membership or Gift Membership transactions are in the same file.
Lockbox can import one or more premiums with each gift or membership. Import of standalone premiums is not supported.
If only one premium is being imported with each transaction, the premium fields can be included on the main Detail row (with the gift or membership information) or can be on a separate sub-detail row immediately following the Detail of the transaction. If more than one premium is being imported, the premium fields must be on sub-detail rows. Setting a default premium code will not result in premiums being created; the premium code must be in the import file.
If the Date Sent is populated, importing the premium will not affect inventory quantities. Otherwise, inventory quantity will be affected. Lockbox will abort if a sufficient quantity is not available in inventory.
Premiums can be imported with the following types of transactions:
|GL||Gift Lifetime Pledge|
Projects can be imported and updated through Lockbox. The project fields are only available in the appeal import definitions, although the file can contain projects only. A project type must be included in the file for a project record to be created. A project transnum must be included in the file for a project record to be updated.
Lockbox can import registrations,
registration fees and registration payments. Lockbox also has the
ability to find an event group to assign to a registrant based on the
Lockbox can create Prospect Relationship association records. Lockbox adds only new relationship records and will not update existing ones. Reciprical relationships are automatically created based on Relationship codes. The Relationship fields only become available when the Relationship data type is checked.
Lockbox can create Prospect Research ratings. Lockbox accepts only one rating per record. This feature of Lockbox serves a different purpose than that served by the dedicated Prospect Research Import utility, and cannot accept the same types of files.
Lockbox can import Subscriptions and subscription payments. The Subscription Total Fees and Balance are calculated based on the Journal Code, Fee Type and number of Copies. The payment amount, if any, must be included in the file; it is not calculated by Lockbox. Payment information, including credit card information, can be part of the subscription import. In the import file, the payment information should be on the same row as the subscription information. These transactions must be identified to Lockbox by a Trantype of SP, either in the file or as a default in the definition.
It is also possible to renew a subscription. To renew an existing subscription you must provide the current transnum in the previous_transnum field.
It is also possible to import payments for existing subscription transactions. To accomplish this, check the Gift data type option in the Lockbox definition and map the payment fields appropriately. These payments must be identified to Lockbox by an SP trantype code in the file or as the definition default. Do not check the Subscription data type box unless Subscription transactions are in the same file.
Lockbox can import Tracking records. Checking the Tracking checkbox will allow addition of Tracking fields to the Lockbox definition. A Fund code must be included in the file for a Tracking record to be created.
Lockbox can create and update unit records. The unit fields are only available in the definitions that have either Prospect or Update Prospect checked. A unit record will only be created when the unit field has a value. When inserting a unit record the prospect's unit field is set to 'Y'.
The import rules for unit area are different from those of other tables. The constraint is that the combination of area and area type must be unique. The fields for use in importing Unit Area are included with Prospects. If unit_area area has a value in the file, Lockbox checks for an existing unit_area record with that area value and type. If no record exists, then a new record is created. If a record does exist, the existing record is deleted and if there is an idnumber value provided in the file then a new record is created.
Lockbox can create Volunteer records. Lockbox will also update Volunteer records if they exist. These fields only become available when the Volunteer data type is checked.
Lockbox can create Work History records. The work history fields become available when the Research data type box is checked. An employer ID# (emp_empid) must be included in the file for a Work History record to be created.
Though every effort has been made to simplify the process of incorporating data through Lockbox, the flexibility of the process relies somewhat on the vendor to create compatible files. In order to ensure that the vendor supplies proper files, the vendor should get a copy of the Lockbox Data File
Requirements. A presentation packet is available from SofTrek so users can reproduce a pre-formatted page for submission to vendors. The file includes a cover letter with instructions to the user and Data File Requirements, information that is important from the vendor's perspective. Alternatively, most compatibility issues can be resolved through customized processing.
Data File Requirements. The following parameters will help the vendor to check that their files meet the minimal requirements for compliance with Lockbox standards.
• Fixed-length, comma-delimited, tab-delimited, and XML files are acceptable.
• Files can contain numerous fields in any sequence. Unmapped data will be ignored.
• Data can exceed maximum field length; excess data will be ignored.
• Detail, header, trailer, and sub-detail records must be coded with an initial identifier (e.g., 'H,' 'D,' 'T', 'S') if the file contains header, trailer, or sub-detail information.
• If batch numbers are included in the records, the file must be grouped by batch number.
• Different transaction types in the same file must be distinguished by unique transaction type codes (e.g., 'D' identifies a donation and 'P' identifies a pledge).
• Names and addresses must be parsed into separate fields.
• A single file may include new, unchanged and/or updated name and address information.
- To add a new name (or name for which no ID# exists) the ID# must remain blank.
- To update a name, the full name is required.
- To insert an address, the full address is required.
Conditions under which Adds and Updates Occur
Lockbox performs an add or an update based on whether there is data in specific fields in the import file. Adds are not performed based solely on defaults, and no defaults are used for updates. To update a field in an existing record to null, the import file must include 'PMNULL' (without the quotes) for that field.
|PledgeMaker Table||Data which causes an Add||Data Which
Causes an Update
(Requires a match to an existing record)
|ADD_GREETINGS||ADD_GREET_GREETING||Record is replaced based on IDNUMBER + ADD_GREET_SOLICITORCODE match|
|BIO||any BIO field||any BIO field|
IDNUMBER + EMAIL match
|INVENTORY_ADJUSTMENT||ITEMS_ITEMCODE and ADJUST_QUANTITY <> 0||n/a|
|ITEM_ASSEMBLY||ITEMS_ITEMCODE and ASSEMBLY_COMPONENTCODE||ITEMS_ITEMCODE and ASSEMBLY_COMPONENTCODE|
|LISTS||LIST_CODE and LIST_DESCRIPTION||n/a|
|MAIL_APPEALCODE||MAIL_APPEALCODE or MAIL_TRANSNUM|
|MAILRESP||any MAILRESP field||n/a|
|MEMBERSHIP||TRANTYPE OF M, GM, or MP||n/a|
|ORDER||TRANTYPE OF O||n/a|
|UNIT||any UNIT field||any UNIT field|