Clear Contents of NULL Cells

• Sort each column of data in ascending order. The first cell should contain valid data. If the first set of cells are blank, select them all and clear the contents to force them to blank. Exports of data create NULL cells of data, they appear blank, but they really are not.
• Select the cell(s); Right-Click; Clear Contents

Missing Columns

• There will be times where there may be missing columns of data. For example: Company Name, Patron Type, Patron Salutation. It is normal. Manually add in the extra columns that are needed to properly separate the data into their own elements.

First Name

• Sort the column and move all Dr., Mr. Mr. & Mrs., Ms., Capt., Rev., Father, Sister, etc. into the Salutation Column.
• Review how they show the patron spouse connectors. Do they use the 'and' or do they use the '&' symbol. If they are consistent with one of them, set this setting in Theatre Manager's System Preferences settings. If they do not use any consistency at all, use the '&' symbol.
• Move any Company Names into the Company Name field.
• HINT: There is no need to alter this 'and' or '&' setting in the import file. During the import process, Theatre Manager will automatically set it based on whatever is in the System Preferences settings.
• HINT: If the first name field contains the first and last name of a patron, there is no need to split it apart. Theatre Manager will automatically split it into the proper fields during the import process. But you MUST supply a column title for the Patron Last Name, even if it is an empty column otherwise the last name will be lost during import on the name split. Best to also supply the Patron Initial column too to allow the name to be split properly.
• Exception: if the first name field is Mary Anne Jones, then it will place the First Name as Mary, Middle Initial as Anne, and Last Name as Jones. In this case if you wish to have it show 100% correctly, splitting of the name into the proper fields should be done manually so that the First Name is Mary Anne and the Last Name is Jones.

Initial and/or Middle Name

• Hint: There is no need to remove the trailing period from the middle initial. Theatre Manager will remove the trailing period upon import.

Last Name

• Move any Company Names into the Company Name field.
• Hint: Sort the list by First Name and by Last Name. At the bottom of the list, it will show you all records missing a first name which typically could be all the records that have the Company Name located in the Last Name field.

Nick Names or Greetings

• These should be imported into Theatre Manager's Patron Greeting Field (C_NICK_NAME).
• Hint: If the field is blank or not supplied, Theatre Manager will define this field based on the settings in Company Preferences.

Formal Name

• If a Formal Name is provided, sort this column and review it for all the salutations at the beginning of the field. Verify the salutation used in the Formal Name column and UPDATE the Patron Salutation column if it is blank to match what the salutation is in this column.
• Using search/replace, adjust all salutations in this column to be consistent.

Address Lines

• There is a maximum of 2 lines. If need be, combine the data into either of 2 columns, or split long addresses into the Address Line 2 column.
• Move any ATTN from other fields into the Address Line columns.
• Multiple addresses can be imported, however you will require the assistance of the AMS Developer to use the CustomOverrideRoutines to accomplish this.

City

• Sort by the City name column then review all the city's and copy/paste in the correct spellings. It only takes a bit of time, however this is the best time to fix up historical mistakes in a mass correction.

State/Province

• Sort by the State/Province column then review all the States/Provinces and copy/paste in the correct spellings. It only takes a bit of time, however this is the best time to fix up historical mistakes in a mass correction.
• Verify and correct all States/Provinces into a 2 character designations.
• If a State/Province is missing, none will be assigned automatically during import.
• If a State/Province is missing and it is obvious based on the City Name, then update the record to have the correct information.
• If a State/Province is incorrect based on the City Name, then update the record to have the correct information. (Historical mistakes and/or data entry where there was less attentiveness to accuracy.)
• Hint: No need to change to the 2 character State or Province code. If the long version of the State/Province is supplied and spelled correctly, the 2 character representation will be used fro the designation when the data is saved back to the database.

Zip Code/Postal Code

• Sort by the Zip/Postal Code field and review the data to update the Country Field based on if it is a Zip Code or Postal Code.
• If there is a separate column for the 4 digit Zip + 4 setting, merge this column with the Zip/Postal Code column to create a single import column.
• Hint: No need to add or remove the dash for a Zip + 4 column. The import routines will add/remove as required.

Country

• Move all indicators to the Country Field for addresses that are USA, Canada, Japan, Germany, etc. and remove them from the field where you located it.
• Make sure that all countries are valid and exist in the Country Code Table.
• NOTE: If the country field is empty on import, it will default to the Company's country as defined in Company Preferences. This may be correct or incorrect for some patron addresses. It is best if the Country (even if the column needs to be added to the import file) is assigned to each patron's record to ensure accuracy.
• Hint: When sorting the State/Province, you are able to determine the Country by the State/Province.
• Hint: When sorting the Zip/Postal Code, you are able to determine the Country by the Zip/Postal Code format.

Marketing Fields, Do Not Mail, Deceased Flags

• If you notice this data in a notes, address, or another column, create a new column and set the data so that it goes into the right data field in Theatre Manager.
• Set the Do Not Mail-Patron if you find a 'do not mail' indication.
• Set the Do Not Mail-Venue if you find a 'wrong address or moved' indication.
• Set the Deceased if you find a 'deceased or died' indication.

Phone Numbers and Email Address

• Each contact must go into its separate column.
• The primary phone number or email address will be assigned to the first phone number imported for that patron. This means that the column sequence order is important for the phone numbers. Have the patron's home, work, then cell phone numbers in that order to avoid having the Cell (or Other) phone number set as the primary contact number when really it should be the Home Phone.
• Move all comments from the phone numbers (i.e. not sure if this number is correct) into the Patron's Marketing Notes column.
• Using search/replace, set all phone number extensions (Ext., #, extension) to be a consistent value of 'x'.
• If there is a separate column for the phone number Extension setting, merge this column with the Phone Number column to create a single import column.
• Hint: No need to set the formatting of phone numbers, the import process will clean up all phone numbers and standardize their setting upon import.
• Hint: If you notice there are many phone numbers without area codes, it is recommended that in System Settings, that you turn OFF the requirement that Area Codes are mandatory. This way, phone numbers do not get imported as "(123) 4-567" and they go in as "123-4567". After the import, you can go back and turn ON this requirement.
• if there are other types of phone numbers (Summer, Winter, Former, Vacation), these can be imported accordingly using the CustomImportRoutine overrides. Refer to an AMS Developer for assistance.
• Do you need to manually go through and remove duplicate phone numbers? If they are for different patrons, then no. Patron Merging will clean this up. If they are for the same patron account, then yes. - OR - ask a AMS Developer for assistance as there is a quick routine that can be run to purge all duplicated phone numbers for a patron that were imported.

Email Address

• Separate out multiple email addresses into their own separate columns.
• Ensure that any website URL addresses (addresses that begin with a www prefix) are moved to the gWebsite column
• Do you need to manually go through and remove duplicate email addresses? If they are for different patrons, then no. Patron Merging will clean this up. If they are for the same patron account, then yes. - OR - ask a AMS Developer for assistance as there is a quick routine that can be run to purge all duplicated email addresses for a patron that were imported.

Mailing Lists

• Hint: no need to alter a column of mail lists if they are comma delimited within the row. The import routine will automatically separate comma delimited names. If they are delimited by another method, (i.e. semi colon, space, hypen), the import routine can be changed OR just do a search/replace to set the name separator with a comma.
• No need to combine into a single column. 16 different columns can be imported at the same time to create mailing lists.
• If you have more them 16 columns of mailing lists, import the first 16 when the patron record is created, then import the remaining columns using the Patron Mailing Lists import option.

Default Solicitors

• Default Solicitors must be entered in as Patrons first. Assign those default solicitor accounts a unique Import ID#. Then those patron's records need to be imported using the Patron Employee Import process. Then using the customImportOverride routines for updating the Patron Marketing Fields, the link to those solicitors can be made.
• If you are thinking in advance, import the Employee records first, so that when importing the patron records, the solicitor association can be made at the same time. This will save import steps and time.

Patron Salutation

• In many situations, this field is set towards the end of the file cleanup. With the data cleanup, you should have moved and/or copied the salutation from other fields (ie. first name, greeting, formal name, Donor Publication Name).
• It is very important that this field be consistent and correct prior to import. Sort by the list and set the field for proper spelling and punctuation.
• Set all 'and' connectors to become '&'.
• Hint: During import, if the Patron Salutation does not exist in the Code Table values, this value will automatically be added for you.

Patron Type

• In many situations, this field is set towards the end of the file cleanup. With the data review, you now have the ability to see how to properly populate the gPatronType column with Individual, Company, Foundation, Staff, Supplier, etc.
• Note: During import records flagged as type Individual will have their Address Location assigned as 'Home'. All other patron types will have their Address Location assigned as 'Work'.
• Hint: During import, if the Patron Type does not exist in the Code Table values, this value will automatically be added for you.
• Default the column value to Individual for all records.
• Sort by first name, last name, company name. All those with no first and last name, assign as Company.