October 11, 2007

 

Hi, Alan;

 

Here is some background information on the files I sent you separately.

 

TBL_AddressInformation.csv, TBL_PersonalInformation.csv, and TBL_StudyGroupData.csv are direct exports from our FileMaker Pro database.

 

There is an entry for each individual in the TBL_PersonalInformation file.  All address information is kept in TBL_AddressInformation so that multiple household members listed in TBL_PersonalInformation have their records linked to the same address information contained in TBL_AddressInformation.  These are linked by the entry in the Address ID field in TBL_PersonalInformation. 

 

At present if we add a new household member, we highlight the value in Address ID, make a duplicate record of the TBL_PersonalInformation record, and then paste the Address ID value from the original record.  This links the new record to the address information.  We then edit the new record with the new household member's information.

 

TBL_StudyGroupData.csv contains information about study groups.  These records are linked to TBL_PersonalInformation records.  You will see some odd fields in this table which end with the phrase "to use"-- for example, Location_to_use.  This table displays information from the TBL_AddressInformation file such as City, State, etc.  It also displays information from the TBL_PersonalInformation file such as the full name of the person, etc.  Values from these tables appear as defaults in many TBL_StudyGroupData fields.  However, there is an option to enter a preferred value and if there is a value in the preferred field, this will be used instead of the default.  For example, a person may have a Denver address but wants the location of their study group to be "Denver Tech Center." Or, instead of having the host listed as "Bill Jones" we might want to have "Bill Jones and Susan Smith."

 

The information in TBL_StudyGroupData is sent to a web page on a weekly basis where it is imported into yet another database program residing on a web server.  This web page is at http://urantiabook.org/study_groups/domestic_study_groups.htm

 

We would like for our database to contain tables with past conference attendee information linked to the PersonalInformation records.  I sent you four files with past conference attendee information.  They are:

 

Copy of SSS06-Registrations received-as of 2006-06-27.xls

IC02_attendees.xls

IC05_PrimaryRegistrantNames_w_MemberID.xls

SSS07Registrants_Final.xls

 

There are also several donor history files.  We would like this donor information to be integrated with the TBL_PersonalInformation records as well.  The two most important files are:

 

Contributions_1995-1998_w_memberID.xls

Donor history yes-no-2000-thruApr2006 in Access.xls

 

We really only want to know the year, and if the person was a donor during that year.  So a table might have only the person's name and then a column for each year and some indication of whether or not they donated in that year.  Each of these two files will need to have some parsing done to get this information. 

 

If you can extract and include the information for May and June of 2006 from the file Contributions_Jan-June_2006_w_MemberID.xls that would give us relatively complete records for 2000 through June of 2006.  Beginning in July of 2006 Paula has kept donation records in Quickbooks. 

 

Integrating the present data sets which I have sent you with the Quickbooks data will need an additional task.

 

Beyond this I think you can ignore the other contribution/donor history files. 

 

We also need to integrate the information contained in Skipjack_Data.txt.  This is a download of an ascii report from an outside payment gateway through which we process all of our online credit card transactions.  Paula also processes contributions and payments received in the mail through this service using her Quickbooks POS software.

 

We will likely want to export this ascii data from the online service on a weekly basis and have it pulled into our primary database or imported into Quickbooks.  Integrating our primary database with the data contained within Quickbooks is a real nagging issue.  You can call Paula and talk to her about the issues involved here.  We currently have reader information in two primary databases -- FileMaker Pro and Quickbooks.  We don't like this and want to have each bit of information stored in only one place even though it may be referenced from multiple places in our operation.

 

At this point my primary concern is the integration and normalization of all this data.  The inclusion of past donor and conference attendee information is a one-time problem.  We plan in the future to keep this information in our primary database and to use this database with conference registration forms as well as having it integrated with Quickbooks. 

 

You can use whatever software you prefer to do this initial work.  Again, I'm initially concerned with integration and normalization.  I plan to move all of our data to MySQL during first or second quarter of 2008.  I have a lot of needs related to web-based access to various subsets of our data and I can do this easily with php and MySQL.

 

I hope this is helpful. 

 

Thank you again for your help with this.  I'm sorry that this data is in such bad shape--it has been retrieved from a variety of places used by different individuals over the past 10 years.

 

David