May need to clear some values left behind if you've copied them over from the primary outlet:
update fTaxRates set tr_tax1_ca_seq=0, tr_tax2_ca_seq=0, tr_tax3_ca_seq=0,
tr_tax1_def_ca_seq=0, tr_tax2_def_ca_seq=0, tr_tax3_def_ca_seq=0
where tr_d_dept_seq=2
update fFeeTypes set fee_amount=0, fee_active=TRUE, fee_auto_calculate=FALSE,
fee_tr_Seq=0, fee_ca_Seq=0, fee_pb_seq_list=''
where fee_d_dept_seq=2
An employee needs to be first added to the database as a patron, then it can be activated as an employee. Using the Patron Import process, it will do both steps at the same time. Refer to either information provided by the client as part of the data import for the listing of employees, or refer to the company's website for a listing of the current Staff Members.
Remember, within the data to be imported, it should contain a listing of employees who are currently working at the venue and has worked in the past. Do we import past employees? Yes we should. This way we can tag historical ticket sales, membership sales, donation sales to those employees if that reference is within the data. Just as historical data is important, having a listing of historical employees shows the completeness of data importing that Arts Management provides. It goes towards the 'extra step' that we do. Having employees imported during data conversion also allows the onsite trainer to 'move quicker' during the initial training as everyone has their own login ID, and less time is spent setting up employees on the first day when the goal is to introduce them to the application rather then 'how to setup employees'. This also avoids the initial discussions of user access and who gets what privileges - at least until later.
Employee Initials are important. If they are not provided in the import data, they will be created based on the employee first, middle and last name. If possible, define them within the import spreadsheet.
After importing the employees, it is recommended to review the initials and make them unique for each employee. This is not mandatory, however it does allow for more easy identification between the employees throughout Theatre Manager.
All employees import will be created with a user access level set as a 'Normal User
Initial password will be set as Tickets@123
Import Column Template: MKT_FIELD_1,gPatronType,E_INITIALS,C_FIRST_NAME,C_INITIAL,C_LAST_NAME,C_TITLE,gEmailWork,gPhoneWork,C_COMPANY,AD_ADDRESS1,AD_CITY,AD_PROVINCE,AD_POSTAL_CODE,AD_COUNTRY
Defaults: gPatronType = Staff Member
Gather a listing of the various venue maps, physical locations, number of seats within each venue, and for reserved seating, a seating chart - clearing showing the different sections, row and seat names.
The venue map is something that will need to be created in Theatre Manager manually. As part of the import, refer to the client's website for a venue icon for TicketTrove along with a venue description that can be used.
The Best Seat Search - provide a default to allow the most choices for the patron. When the trainer is onsite, they can have the client review the seating options and make the necessary changes required.
Import Column Template: Logical Seat,Door,Section Name,Row Number,Seat Number,Price Codes,Seat Code,Seat Priority,Best Avail Area,Best Avail Index,Seat Note Use?,Seat Note,Seat View
For the Best Seat #, just number then sequential from top to bottom. When the trainer is onsite, they can have the client review the seating chart to determine exactly how they want the seats to be numbered for sale and make the necessary changes required.
G/L Account Columns: gType,CA_FULL_ACCT,CA_EXTERNAL_ACCT,CA_DESCRIPTION,CA_POSTING,CA_LEVEL
CA_FULL_ACCT = account number WITHOUT any dashes in it. If you add formatting to this field, the record will not be imported. Easiest to just import the CA_EXTERNAL_ACCT as a formatted field, and the import routine will set the CA_FULL_ACCT for you.Account Types (gType)
Account Setting (CA_POSTING)
Reporting Level (CA_LEVEL)
Unless specified, the External Account Number will default to the Account Number
Gather a listing of the events and performances.
Event and Performance Columns: P_PLAY_YEAR,P_SHOW_CODE,PB_SHOW_CODE,P_PLAY_TITLE,PB_SERIES_CODE,PB_PERF_NOTES,PB_PERFORM_DATE,PB_PERFORM_TIME,gSalesMethod,gTax1Account,gAccount,gTaxRate,P_VE_SEQ,P_TM_SEQ,P_ACTIVE
To help create the Event Number for the Event Code, refer to the previous event code in the list:
To help create the Series Code, take the day of the week from the performance date.
or use the calcuation of:
=IF(WEEKDAY(G2)=1,"SUN",IF(WEEKDAY(G2)=2,"MON",IF(WEEKDAY(G2)=3,"TUE",IF(WEEKDAY(G2)=4,"WED",IF(WEEKDAY(G2)=5,"THU",IF(WEEKDAY(G2)=6,"FRI",IF(WEEKDAY(G2)=7,"SAT","error")))))))
gSalesMethod
gTaxCode
For importing G/L Accounts, use the column titles of:
Note: you will require remote access to the database via pgAdmin to execute 2 SQL commands to enable/disable a user access level (you will be prompted with the exact command during the process). Once just prior to running the process, and once again after the process has completed. The changing of the outlet number will still happen even without the enable/disable of the user access level, however it will just take longer to complete. The routine disables triggers from running on the updated table.
If the giving level matrix is already setup, then just pass in ONE of the columns for DC_GM_SEQ or GM_DESCRIPTION, it will link the campaign based on those fields.
If the giving level matrix is not created, it will create the giving level matrix based on the GM_DESCRIPTION field - this saves you from having to create them all first, then importing. It will create the giving matrix, then you can go back later and update the giving levels within the matrix.
If the gAccount number has not been created in the Chart of Accounts, the import routine will create it for you - this saves you from having to create them all first, then importing.
At a minimum, import DC_CAMPAIGN, gAccount, GM_DESCRIPTION - then you can go in and customize the donation campaigns to each of their own unique needs.
Do you need to create the campaigns before importing donations? No you do not. If the donation campaign does not exist during donation import, the donation import routine will create the donation campaign automatically based on these same fields.
Campaign columns: DC_CAMPAIGN,DC_DESCRIPTION_EXTERNAL,gAccount,DC_GM_SEQ,GM_DESCRIPTION,GM_SEQ,DC_NOTES
When preparing the import file for donation gifts, the key item that needs to happen is the proper mapping to the Patron Record. Identify that field and ensure that when the Patron record is imported, that the field can be used to map each donation gift to the patron.
Never delete any data. Just move the column to the far right of the spreadsheet, just in case you need to reference that data later. If there is any data in the end that looks like notes, comments, dates, or just references to other data, then move it into a notes field. This at least leaves a trail for the client to reference in the future if they need be.
Donation columns: MKT_FIELD_1,DD_FISCAL_YEAR,DD_PROGRAM_YEAR,DC_CAMPAIGN,gAccount,DC_GM_SEQ,DD_DONATION_DATE,DD_AMOUNT_PLEDGE,DD_AMOUNT_ACTUAL,PAY_TOTAL_PAID,gDonor1Field,gDonor2Field,gDonor3Field,DD_NAME_OF,gPaymentMethod,PAY_CARD_NO
Excel calc to determine fiscal year: =IF(MONTH(D2)>=5,YEAR(D2)+1,YEAR(D2))
After the data has been imported its time to post the data to the accounting module.
Prior to starting:
Determine the Fiscal Year end date (i.e. May 31)
Create a deposit for each Fiscal Year end date. Do not selectively pick dates, nor just do a single massive deposit to get all the imported data deposited. This is a clear sign of sloppiness. Doing it accurately will allow possible analysis down the road of the year-by-year statistics of the accounting module.
Then when you hit the current year, do a deposit for each month end.
Then when you hit the current month, do a deposit for the date that the last payment was entered for.
The deposits should appear as the following if May 31 is the fiscal year end and the last payment imported was Sep 10th.
May 31 2001
May 31 2002
May 31 2003
May 31 2004
May 31 2005
...
...
May 31 2013
Jun 30 2013
July 31 2013
Aug 31 2013
Sep 10 2013
Trick: If you want to have multiple machines performing deposits at the same time add the following search query to allow only a selection of payments.
wDepositList.$buildSearchString
Do iQueryGroup.$loadSimpleQueryString('PAY_DATE_RECVD`>=`JUN 1 2001')
This will allow you to limit a selection of payments between a date range. In this example, it would be implied that MAY 31 2001 was then the year end date for the fiscal year and the deposit date would be set to MAY 31 2002. Thus, this deposit would get a range of JUN 1 2001 to MAY 31 2002.
Use the routine File >> Import/Export >> TIckets >> Create Transactions
This is a special menu option where it will create the transactions only. It does not create the sales entry at this time. This allows you to be creating transactions in the background while data import happens without it having any effect on the G/L. This is purely used as an efficient use of time to maximize the number of sessions/machines that you have available.
Trick: If you want to have multiple machines performing transaction creation as the same time, add the following search query to allow only a selection of data.
tF_PLAYSEATS.$journalizeTickets
Calculate exists as con(exists,' and ORD_DATE >= ',$ctask.tDatabase.$sqlDATE('JUN 1 2001'))
tfOrderFees.$journalizeOrderFees
Calculate exists as con(exists,' and ORD_DATE >= ',$ctask.tDatabase.$sqlDATE('JUN 1 2001'))
Donations - no need to worry about as their transactions are created at time of creation.
Memberships - no need to worry about as their transactions are created at time of creation.
Payments - no need to worry about as their transactions are created at time of creation.
Projects - not done at this time, but could update tProjectsJournalizeList.$journalizePersonnelAndResouces for the exists() portion of the search() string.
Just start the process and let it run normally.
Post as normal.
Then run the following command to update the posting date of the imported data to set it to the final date of the imported data. As well, set all this data as already been exported - to avoid having to export out all this data in case they use this function later on.
update F_GL_HEADER set GL_DATE_POSTED='2013-09-10 17:00:00', GL_EXPORTED = TRUE
This will give the imported data a consistent date posted and a clean date based on the last date of imported data. As well, it provides a way to easily know what data was imported and a way to use the GL_DATE_POSTED field in reports if we need to exclude this data from the current years reports.
If you wanted to de-activate all imported sales promotions so the client can start with all new promotions and have the old promotions not appear in the event setup window:
update fSalesCodes set SC_ACTIVE = FALSE, SC_DATE_END='yyyy-mm-dd' where SC_PROMOTION_USE = 0
In case you missed setting the Sales Promotions into their correct designation of Regular, Season, Ticket Type 3, Ticket Type 4, or Ticket Type 5 - it's too late to easily go back and recreate all the G/L Sales Entries. In most cases as this is historical data anyway, its not too critical (unless of coarse it is current years data and effects deferred/earned season/regular revenue).
Here is an easy way to get all the Patron Statistics to get shown correctly which is more important for when the client goes and does a historical analysis of the patron's buying history.
update all the sales promotions correctly.
delete from fPatronStatistics
select postPatronStatistics(F_TRANSACTION,true,true) from F_TRANSACTION
alter sequence fPatronStatisics_cs_seq_key restart with 1
In case you imported performances for a future date, then the client didn't use the database and you need to go and set those future dated performances (which are now in the past) as rolled over or flagged as history.
update F_PLAYBILL set PB_GL_JOURNAL_NO_CODE='History', PB_GL_SEQ=-1 where PB_PERFORM_DATE
Timeline of Action Events
After Merge has completed
Reset the "Secondary-Client" Outlet Number - Set the Outlet number to be what you would like the Outlet number to be in the final merged database
Reset the "Secondary-Client" Sequence Numbers - Sets the Sequence numbers for every table and connecting child records to be different then what is in the "Primary-Client" database
The main goal is to get a consistent look to the imported data. Make the data look pretty and stand out as quality data shining in the application. Fill in empty spots; move data from incorrect or general notes fields, into Theatre Manager's specific named fields. The patron data should be referred to as 'gold' as it takes quality names and contact information to nurture and grow the patron's desire and need to keep returning to the venue over and over again.
This is where Theatre Manager's CRM module stands out. It all starts with clean, clear, and consistent data.