LPUS Database
From Libertarian Wiki
Contents |
[edit] Introduction
This is a brainstorming and documentation page for the people in LPUS and its state affiliates that manage and exchange mailing list database data. Presently it is mainly about the database dumps that LPUS sends to its state affiliates from its new database system, Raiser's Edge.
Nidus is a web-based membership database application to access this LPUS Database.
David W. Roscoe created this page, but anybody can contribute to it using only an ordinary Web browser. If you have a concern, answer, idea, correction, clarification, details, or anything else that might be helpful, then simply click on the (Edit this page) link or an (edit) link on the right side to edit only one section, and make your change. When finished, click the (Save page) button.
[edit] Alternate Discussion
If you're uncomfortable using wiki then you might want to try the LP-Database Yahoo group. It is athttp://groups.yahoo.com/group/LP-Database/. It was created by Marc Montoni.
[edit] Contributor Key
It would be helpful if people that contribute to this page identify themselves, especially if you express an opinion in favor of a particular database dump file feature. In that case, you might want to define an abbreviation here.
- DWR@LPMA: David W Roscoe, LPMA Membership Secretary.
- hardy@freevermont.org: Hardy Machia, Vermont data collector.
- BJS: rabbit@cownow.com: Bonnie Scott, LPNY webmaster and keeper of some NY data. Also did some due diligence on the Raisers Edge (RE) purchase while an alternate on the 2002-2004 LNC.
[edit] Database Dump Documentation
The following summarizes the contents of the old and new database dumps.
[edit] Old Database Dumps
The following describes the fields in the old database dumps. It includes the field name, field length in characters, and in some cases a description.
[This list is based on a 1998 e-mail from Tiffany Rodney. One change since then of which I know is that the MEM_ID field increased from 6 to 10 digits. - DWR@LPMA]
- FNAME 15
- LNAME 25
- ADDR2 31
- ADDR3 31
- CITY 22
- STATE 2
- ZIP 10
- ID_NO 10 (was 6)
- DAY_PHONE 12
- NITE_PHONE 12
- CREATED 8 Date
- EXPIRES 8 Date
- MEM_TYPE 3
- OATH 1
- DUPE_REC 1
- DEACTIVATE 1
- NO_MAIL 1 Won't sell name/addr on mailing list
- NO_PHONE 1 Won't sel name/phone # on list
- BAD_ADD 1
- PLEDGER 1
- ORIGIN 5
- ONEADD 31 This field links to an outside database of persons whh have requested single mailings come to them at the same address (e.g. husband and wife). The field contains the addressee's names.
- CONG 2 Congressional district - two digit code
- COUNTY 25
- REDU_MAIL 1 Reduced mail flag for person who wish to receive the LP News but not our monthly fundraising mailings.
[edit] New Database Dumps
[ The following is based on CSV files created from the XLS (Excel) files send by LPUS. DWR@LPMA ]
[edit] Member/Subscriber Files
The following is a list of the fields from the "dumps" and the "lapsed" files.
- CnBio_ID
- CnBio_Title_1
- CnBio_First_Name
- CnBio_Middle_Name
- CnBio_Last_Name
- CnBio_Suffix_1
- CnAdrPrf_Addrline1
- CnAdrPrf_Addrline2
- CnAdrPrf_City
- CnAdrPrf_State
- CnAdrPrf_ZIP
- CnAdrPrf_County
- CnAdrPrfPh_1_01_Phone_type
- CnAdrPrfPh_1_01_Phone_number
- CnAdrPrfPh_2_01_Phone_type
- CnAdrPrfPh_2_01_Phone_number
- CnAdrPrfAtrCat_1_01_Description
- CnAdrPrfAtrCat_1_01_Import_ID
- CnMem_1_01_Standing
- CnMem_1_01_Category
- CnMem_1_01_Date_Joined
- CnMem_1_01_Cur_Expires_on
- CnPrBs_Profession
- CnPrBs_Position
- CnPrBs_Industry
- CnPrBs_Industry2
- CnPrBs_Org_Name
- CnPrBs_Position2
- CnPrBs_Profession2
- CnAttrCat_1_01_Description
- CnAttrCat_2_01_Description
- CnAttrCat_3_01_Description
[edit] Inquiry Files
The "inquiry" files are a little different from the "dumps" and the "lapsed" files. Differences include:
- They do not have the Membership(1) group fields.
- CnMem_1_01_Standing
- CnMem_1_01_Category
- CnMem_1_01_Date_Joined
- CnMem_1_01_Cur_Expires_on
- The order of the other fields is a little different
The following is a list of fields in the "inq" files, shown without groupings. It is based on CSV files created from the XLS (Excel) files sent by LPUS. DWR@LPMA ]
- CnBio_ID
- CnBio_Title_1
- CnBio_First_Name
- CnBio_Middle_Name
- CnBio_Last_Name
- CnBio_Suffix_1
- CnAdrPrf_Addrline1
- CnAdrPrf_Addrline2
- CnAdrPrf_City
- CnAdrPrf_State
- CnAdrPrf_ZIP
- CnAdrPrf_County
- CnAdrPrfAtrCat_1_01_Description
- CnAdrPrfAtrCat_1_01_Import_ID
- CnAdrPrfPh_1_01_Phone_type
- CnAdrPrfPh_1_01_Phone_number
- CnAdrPrfPh_1_02_Phone_type
- CnAdrPrfPh_1_02_Phone_number
- CnPrBs_Profession
- CnPrBs_Position
- CnPrBs_Industry
- CnPrBs_Industry2
- CnPrBs_Org_Name
- CnPrBs_Position2
- CnPrBs_Profession2
- CnAttrCat_1_01_Description
- CnAttrCat_2_01_Description
- CnAttrCat_3_01_Description
[edit] Field And Field Group Descriptions
The following is from an e-mail attachment from Jennifer via Chris Farris. It describes the RE fields and field groups.
Group Field Header
Biographical
1. ID CnBio_ID
2. Title 1 CnBio_Title_1
3. First Name CnBio_First_Name
4. Middle Name CnBio_Middle_Name
5. Last Name CnBio_Last_Name
6. Suffix 1 CnBio_Suffix_1
Address\Preferred Address
7. Address line 1 CnAdrPrf_Addrline1
8. Address line 2 CnAdrPrf_Addrline2
9. City CnAdrPrf_City
10. State CnAdrPrf_State
11. ZIP CnAdrPrf_ZIP
12. County CnAdrPrf_County
Address\Preferred Address\Phones(1)
13. Phone type CnAdrPrfPh_1_01_Phone_type
14. Phone number CnAdrPrfPh_1_01_Phone_number
Address\Preferred Address\Phones 2(1)
15. Phone type CnAdrPrfPh_2_01_Phone_type
16. Phone number CnAdrPrfPh_2_01_Phone_number
Membership(1)
17. Standing CnMem_1_01_Standing
18. Category CnMem_1_01_Category
19. Date Joined CnMem_1_01_Date_Joined
20. Membership ID CnMem_1_01_Membership_ID
Membership(1)\Current
21. Expires on CnMem_1_01_Cur_Expires_on
Primary Business information
22. Profession CnPrBs_Profession
23. Position CnPrBs_Position
24. Industry CnPrBs_Industry
25. Industry CnPrBs_Industry2
26. Org Name CnPrBs_Org_Name
27. Position CnPrBs_Position2
28. Profession CnPrBs_Profession2
Attributes\Occupation(1)
29. Description CnAttrCat_1_01_Description
Attributes\Congressional District(1)
30. Description CnAttrCat_2_01_Description
[edit] ID Field Values
When LPUS converted their FoxPro database to Raiser's Edge, they did not preserve the ID field values that they had been sending in their database dumps, or at least they don't include them in the new dumps.
What LPUS appears to have done is this:
- If a person has a MEMB_ID (is a present or past member/subscriber) then CnBio_ID is the 9 digit MEMB_ID number prefixed with "M-".
- Otherwise CnBio_ID is the 10 digit ID_NO (aka INQUIRYID) prefixed with "P-".
[edit] ID Field Values Generation
The following is a dBase IV script that illustrates how it appears that LPUS calculated the new ID values that we see in the CnBio_ID field of the new Raiser's Edge dumps.
Here is the script.
* LP database ID conversion demonstration.
* This following displays a selection of fields of * 30 equally spaced example records from * the last MA subset of the old LPUS database dump, * along with a calculated field that matches the Raiser's Edge CnBio_ID values.
set talk off set century on
use maall && Open the LPUS database dump, MA subset. RecNumI= 1 && Start with record 1.
* Output column headings. ? " RecordNum ID_NO MEMB_ID CnBio_ID EXPIRES FNAME " ? " --------- ---------- --------- ------------ ------- --------"
* Display a sample of records. do while RecNumI<=reccount() goto RecNumI && Position database to next record.
? recno(), ID_NO, MEMB_ID, && Output record #, and the 2 old ID field values.
* Output an ID value that matches
* the CnBio_ID from the Raiser's Edge dumps.
* The calculation depends on MEMB_ID and ID_NO.
if .not. isblank(MEMB_ID)
RE_Id= "M-"+MEMB_ID+" "
else
RE_Id= "P-"+ID_NO+" "
endif
?? RE_Id
?? EXPIRES, FNAME && Output expiration date and first name field as a check.
RecNumI= RecNumI+reccount()/30 && Calculate # of next record to show. enddo
wait "" to KeyC && Pause for user input.
quit
The script produces the following output when run on the last old format MA (Massachusetts) dump. The pattern is easy to see.
RecordNum ID_NO MEMB_ID CnBio_ID EXPIRES FNAME
--------- ---------- --------- ------------ ------- --------
1 0000000034 000000034 M-000000034 03/19/2005 Mark C.
345 0000100702 000100702 M-000100702 03/08/1991 Ronald
689 0000139125 000139125 M-000139125 07/07/1993 Phyllis K.
1033 0000227382 000227382 M-000227382 02/22/2003 Michael
1377 0000253444 000253444 M-000253444 06/17/1999 Jeanne
1722 0000276148 000276148 M-000276148 12/06/2000 Richard
2066 0000701039 000701039 M-000701039 06/30/2001 Robert
2410 0000746779 100011730 M-100011730 03/24/2005 Jessica
2754 1000125631 100022355 M-100022355 05/29/2004 Roger Edward
3098 0000155495 P-0000155495 / / Gary
3443 0000207798 P-0000207798 / / Dave
3787 0000222577 P-0000222577 / / Richard
4131 0000244058 P-0000244058 / / Randell F
4475 0000245475 P-0000245475 / / Christopher A
4819 0000264608 P-0000264608 / / Said
5164 0000513389 P-0000513389 / / James E
5508 0000563944 P-0000563944 / / William
5852 0000582155 P-0000582155 / / George
6196 0000612238 P-0000612238 / / Rebecca
6540 0000641334 P-0000641334 / / Lee
6885 0000709827 P-0000709827 / / Sharon L
7229 0000734679 P-0000734679 / / Diana
7573 1000008238 P-1000008238 / / Kim
7917 1000028365 P-1000028365 / / Ryan
8261 1000041382 P-1000041382 / / Martin
8606 1000048618 P-1000048618 / / Christopher
8950 1000063778 P-1000063778 / / Matthew
9294 1000081216 P-1000081216 / / Barbara
9638 1000098564 P-1000098564 / / Paulo
9982 1000117403 P-1000117403 / / Michelle
[edit] ID Field Values Replacement
The following is a dBase IV script that stores the new Raiser's Edge ID field value in a field that was recently added to the LPM.DBF database file of the Massachusetts affiliate. The value is generated using the same logic as the script above, except that if there is no associated record found in the old database dump then a temporary number beginning with a "T" is used.
Here is the script.
* LP database ID conversion.
* This following generates Raiser's Edge CnBio_ID values * and stores them in the LPM->USREID field. * It calculates the value from LPM->USID, MAALL->ID_NO, and MAALL->MEMB_ID. * MAALL is maall.dbf, the last MA subset of the old LPUS database dump.
close all set talk off set century on
select 1 && Select area 1. use maall && Open the LPUS database dump, MA subset there. index on id_no to tmp && Index MAALL->ID_NO, which is also LPM->USID.
select 2 && Select area 2. use lpm && Open the LPMA database.
* Relate the files to find the dump MAALL associated with the LPM record. set relation to usid into maall
RecNumI= 1 && Start with record 1.
* Output column headings. ? " RecordNum ID_NO/USID MEMB_ID USREID EXPIRES FNAME " ? " --------- ---------- --------- ------------ ------- --------"
* Display a sample of records. do while RecNumI<=reccount() goto RecNumI && Position database to next record.
? recno(), LPM->USID, MAALL->MEMB_ID, && Output record #, and the 2 old ID field values.
* Output an ID value that matches
* the CnBio_ID from the Raiser's Edge dumps.
* The calculation depends on MEMB_ID and ID_NO in MAALL.
if .not. eof(1)
if .not. isblank(MAALL->MEMB_ID)
RE_Id= "M-"+MAALL->MEMB_ID+" "
else
RE_Id= "P-"+MAALL->ID_NO+" "
endif
else
RE_Id= stuff(LPM->USID,1,1,"T")+" "
endif
?? RE_Id
replace LPM->USREID with RE_Id && Store Raiser's Edge ID in database field.
?? MAALL->EXPIRES, MAALL->FNAME && Output expiration date and first name field as a check.
RecNumI= RecNumI+1 && reccount()/30 && Calculate # of next record to show. enddo
wait "" to KeyC && Pause for user input.
close all
If you're a state affiliate's database persons, and you saved an old dump with both ID_NO and MEMB_ID in it, then you should be able to use the logic in the above scripts to translate your old ID_NO field values to the new CnBio_ID ones. Of course it would be much better if LPUS could translate its CnBio_ID values to the old ID_NO ones. Let's hope.
[edit] County Field Values
Sun, Feb 13, 2005 at 03:40:56PM -0500, Chris Farris forwarded a note from Jennifer Villareal: "Unfortunately we researched with Blackbaud's user help line and 'County' cannot be automatically uploaded from the spreadsheet batch upload because RE requires that you populate the 'County' field by manually running a process called 'Address Validator' which takes the Zip and auto-populates the County field."
BJS adds: This is not much different from how it worked under FoxPro. If you tried to manually enter the county, it would be zapped because the validator didn't like the address as entered. Correcting the address to postal specs is necessary to get the county to fill in. This URL helps: http://zip4.usps.com/zip4/welcome.jsp
UPDATE: For the APRIL 2005 dump, Chris Farris was able to assist staff in utilizing the Address validator, and we received dumps with ZIP+4 and probably better county information.
[edit] Other documentation
Documentation other than Database Dump documentation.
[edit] Other Raisers Edge info
[edit] Automated imports
RE already has the ability to import changes to the database:
http://www.blackbaud.com/solutions/mailmanagement.asp
Staff are asking for spreadsheet imports. Is that the only possibility?
[edit] Raisers Edge modules that National has purchased
On Wed, Mar 16, 2005 at 08:54:23PM -0500, Chris Farris wrote: This list comes from the new Ops Director at HQ.
RE:7
RE: Anywhere
RE:Member for Membership Management
RE:Search for Prospect Research
RE:EFT? for Electronic Funds Transfer
RE: Crystal Report
[edit] UMP Calculations
The formula was recently fine-tuned (because of problems like people getting life memberships for ordering a lot of literature). What were the results?
One bit of info, from Jennifer Villareal in a response to Jack Tanner, Treasurer@LPF.org:
"The criteria for the UMP calculation is that the member must be current and have given money in the last 12 months that is non-convention gifts."
[edit] FoxPro Documentation
from John Famularo:
The so called 'Old FoxPro' system was a relational
database keyed on ID_NO. There was a base "LP-Master
file" with all the unique fields and summary financial
fields. There was a related "LP-Tran file" which
contained a record of each financial transaction and a
"LP-Data file" which contained non-financial fields
such as phone numbers, email addresses. There was a
"LP-Audit file" which contained a before and after
picture of every non-financial change (e.g. zip code,
member type)
There were a number of other relational tables keyed
off fields in the above files such as the state table
and the zip-code table.
All reports sent to the states on a weekly or monthly
basis were extracted from the above relational
database. There was a decision at the time (1994?) to
only send to the states the fields they required in
the form of a flat file dump monthly. States also got
weekly new and changed record reports.
I left doing the systems maintenance in July 1996. A number of changes were made to the system over the next 5 years. Most of the loss of control over the system were the result of on or more of the following: 1. Untested changes 2. No documentation update 3. No training of new personnel 4. Some bad design decisions (like replacing the unique ID-NO with separate member-id and inquiry-id.
No matter how good of a system some one may install in the next few months, if documentation is not maintained and source code changes are not restricted to one individual, the LPHQ will lose control in short order. Where is the old documentation? Where is the new documentation?
[edit] Kia Documentation
Kia was the company who built the functionality currently at http://www.lp.org/
Bonnie has posted Kia documentation to http://groups.yahoo.com/group/LP-Database/files/
Main needs now:
1. Get states dumps of their web volunteers before the website upgrade starts
2. Give the states and the Costa Rican LP the rights to use the Kia code, and get a copy off the server before it's lost!
3. Allow states to update their own candidate, officer, and college contact info! (coming with the upgrade, we are told)
Just a little bit of PHP/MySQL work is needed to finish up the 5% of the features not finished up that make it a great candidate tracking system and other things useful for a political party.
http://groups.yahoo.com/group/LP-Database/files/ includes documentation on admin features, the 'open items' list for Kia development, and the schema for the volunteer records that the website collects at http://www.lp.org/action/volunteer.html
[edit] Disaster recovery strategies
What are the offsite backup strategies for LP data, including RE and financial records and the website content and programming?
[edit] Issues
The following is a summary of issues as summarized and categorized by Bonnie Scott for the Database phone call May 5, 2005. It does not necessarily cover all bugs listed in the Bug Tracker.
[edit] Issues as of May 5, 2005
The original expanded list of questions (not necessarily including all bugtracker issues) was posted to the database list the morning of the conference call. Most had been provided to Sam New the day before.
Questions for the DB phone conference May 5, 2005, answers received, and other issues brought up
[edit] I. Contractual Obligations
A. regarding Oath information
(embedded in the member type field, CnMem_1_01_Category)
i.e., can you guarantee that
if Category contains "Member" then OATH= "Y"
else if Category contains "Subscriber" then OATH= "N"
else if Category contains "Basic" or "Sponsor" or "Sustaining" or "Patron" then OATH= "Y"
-algorithm provided by David W. Roscoe on the lp-database list
Bonnie asked about the procedure that staff use to set this information. We learned that the donation level is NOT driven from the donations recorded elsewhere in Raisers Edge. We learned that the member type is selected by data entry staff from a drop-down box containing about 13 items. (Not the full list of 24 known entries in that field.)
During the call, Jerry Bloom (MI) expressed concern that the member versus subscriber information is not completely reliable. Bonnie Scott (NY) and others share this concern, among them Joe Dehn (CA) and Dan Karlan (NJ). (Source: personal mail to Bonnie.) The concern stems from the known lack of procedures, yet the reliance on staff to set this combined field correctly. (It contains both the donation level and the oath-signing confirmation.)
During the call, Robert or Sam did a quick search, and found 26 "oath-signers" who were "subscribers." (So, is this a separate field after all, if he could search on it?)
Answer for now: this is an issue for the audit team...TRUE
The delegate counts for the 2006 convention depend on this, and Chris referred to the acknowledged deadline of either November or December, 2005 (depending on the first day of business for the convention. May 30 or June 1, 2006).
Emily Salvette asked whether the Convention Credentialling Committee would have VPN access to the database during the convention.
ANSWER: more likely that they will make a copy and bring it to the convention. No plans to get Raisers Edge Event Module, which is customized for running conventions, down to barcoding the name tags.
RESPONSE: not on a laptop that could get stolen, right?
B. Phone numbers -- when will we get them all?
Some DBAs are leaning towards a relational model for these. Is that really what they are in RE now? APPARENTLY.
Can we talk about the merits of squashing them all down to two phone fields for distribution to the DBAs (what the game plan is describing) versus 'percolating' the phone numbers from "some 20 other different" fields, down to the lowest- numbered HOME fields, which may vary in number by record.
Short-term answer: Any state can request a dump of all phone numnbers straight from national. Please let states know this!
Longer-term answer: The gameplan is to have the info provided by the 'translator' in August, 2005. (Just the first two, though, or all?)
ALSO SEE: Chuck Moulton's SLA amendments that will withhold phone numbers for 'do no phone' and emails for 'do not email' people.
Q: Do any of our members have more than three phone fields?
ANSWER: Chris says that in his research, he saw constituents with up to six or seven. Duplicate phone numbers in different fields have also been noticed, but staff do see all a constituent's phone numbers--on the Bio1 screen--when working with their info.
Related question, because of the RE design: What field will the emails end up in?
Answered in the Game Plan: "Email"
C. We need, electronically, the information to audit UMP payments, as provided in printed form entitled "Feb UMP." Namely the "Gift Total Amount_1" and the monthly "UMP Amount."
Answer: Apparently Mark Nelson responded obscenely when told that this information was provided to states in the instance cited. Staff deny that this field is necessary to audit UMP. As consolation prize, we were given the full text of the UMP I and UMP II agreements, kindly provided by Sam New. (Large PDF files!)
Answer: UMP I is based only on the five-tiered 'level,' which is contained in the category field, along with Oath.
Q: Under UMP II, the states get the 'credit' for getting a new membership. What is the process for transmitting this information (and the money) to national?
Answer: UMP II actually depends only on the length of time a person has been a member.
...Need to check that against the agreement.
F. Renewal procedure
Emily Salvette (MI) brought up the question of when a member lapses...when they renew, does this reset their Join Date? This would have implications for UMP II payments.
ANSWER: inconclusive, believed to be "no, the earlier date is used"
Q: Do renewals extend the expiration date from: a) the renewal date; or b) the prior expiration date. first mentioned: [1]
ANSWER: The "drift" problem is acknowledged by HQ. They are working on a solution and training staff. They have eight months of drift to correct, and they cannot guarantee that new drift problems are not still being introduced until they finish training all staff.
I.e. it is a manual process, and not driven by the data that reside elsewhere in RE.
Bonnie's opinion: That is disappointing from a systems level, and it's not fair to staff to put this work and responsibility on them that is a result of poor design...In this case, a design that was just re-engineered by LNC and staff without keeping the states (who have more memory of why past systems decisions were made) fully in the loop.
G. Christie Wiltie brought up the cross-state membership issue. There are all kinds of implications here that still need to be worked out, and staff agree. One non-trivial class of members potentially affected are the student members.
Tentative conclusion: there needs to be a separate field for "membership in what state." This affects credentials.
[edit] II. Financial implications
A. State DBAs who came before us (for most of us) worked hard to flesh out a set of mailing-related fields to control mailings and prevent needless printing and postage costs.
Q: We used to have a three-tiered system of bad addresses
Answer: Raisers Edge just has a boolean.
Downstream systems like Nidus will provide the more flexible three-tiered system for marking addresses, and can 'dumb down' the information when transferring it to National for compatibility's sake. (Not our preferred choice, it should be noted.)
Q: We used to have a 'household' field called ONEADD.
Juliet says there are a couple of choices. If they are a constituent, they have a 'spouse' field. The limitation with that is that the kids don't get linked in, too.
There is also a 'head of household' field.
Previous data from the ONEADD field was not imported into the system. Chris may be able to backfill it.
I believe Mark Hinkle brought this up, that there should be a way to indicate a combined household this on the web form.
If you currently have a set of records that should be combined into a household, please send that info to pledge@lp.org.
Q: We also should add "Duplicate" to the list of possible values for CnMem_1_01_Standing (current allowed values are Active, Deceased, Dropped, Lapsed). Unless 'duplicate' info is stored somewhere else?
ANSWER: We asked about the 'duplicate' field, but when they get info about dupes, the merge process just merges them. Staff indicates which is the primary record and which is the secondary one. Information from the removed record is shown in the change history of the remaining one. Optimal merge strategies were touched upon, something we will still need to talk more about with national.
Q: Wasn't some de-duping done during the transition?
ANSWER: nobody can recall or noticed that this was done.
B. Timing of the monthly dumps
(Needed to plan mailings.)
Robert Kraus starts the monthly dumps at the beginning of the month, and starts mailing them out alphabetically, usually finishing by the end of the first week of the month. At this point in the conference call, Roger Pettijohn's offer to automate the process was (again) brought up. See below.
[edit] III. Answers necessary to set other priorities
- the schema, which might have been posted to lp-database archives by then
ANSWER: full schema: not possible, it's proprietary to RE
Chris says there are 408 tables, and "400 are junk." Showing gifts, etc. He will provide more information about the field lengths of the fields we share with national.
Fourteen minutes before the May 5 conference call, Steven Kuck forwarded to the lp-database list the schema Chris proposed to have the 'translator' provide. On the call, Chris said he'd come up with that list the night before.
- when will automated exports be available?
The original Game Plan proposed to reduce the number of exported files from 153 (a 12-hour job monthly) to 3 to 6 (a 1-hour job weekly).
Roger Pettijohn's offer back in February to automate this process was reiterated. Robert Kraus said that he trusted Chris to make these decisions. Chris Farris said that he didn't know about this offer from Roger. Here is the URL of a note from Chris referring to Roger's offer, from the lp-database list:[2] Emily Salvette (MI) made a strong case for better using the skills of the willing volunteers among the state DBAs. Roy Meyers agrees that staff and the Implementation Review Team should talk to Roger. Roger can answer whether it needs to be a stored procedure or whether he can just connect to the database, run the query, and deliver it where it needs to go. There need not be any warrantee issues--Roger knows what he is doing.
- when will automated imports be available?
Asked, but no date really given. August?
The key bit of information we have gleaned is that there are separate processes for brand-new records versus updates to existing records.
- is it possible to 'back-feed' data lost during the transition, such as the inquiry dates, which were all set to 9/10/04?
Yes, Chris indicated that he will be doing some of this.
-Life members--are there really three fields now instead of two to worry about, or is the 'life member' "field" merely in the interface, serving to trigger a change of the expiration date to year 2299?
ANSWER: staff insist that when the life field is checked, there is NO expiration date set. I.e., a null value. Need to check that against the May dumps.
So, when the life member field is checked, that field is set to Yes, the expiration date is set to null AND the member category is moved to a Life choice. (There are several of those...see elsewhere in this doc.)
[edit] IV. List of secondary questions
These are still necessary, but the first set of three categories are more important for the May 5 call, and these might be answered by Chris's presentation during it.
update: we did get some answers
Q: When an inquiry becomes a member, does the member id change?
ANSWER: No, it should not
Q: Are web inquiries and 1-800 number inquiries going into Raisers Edge?
ANSWER: Yes, in batch mode.
Q: The "inquiry date" field is really the 'date changed' field, Jennifer seems to have said. Does that mean it will change for inquiries when their record changes?
ANSWER: Jessica says this is probably true, but then Chris said that it's called the "constituent date added" field. She said they don't change inquiries much. The states may, though, and we will have to check whether the inquiry date we are getting back behaves like a 'date entered' field or a 'date changed' field. Chris might be able to backfill this from the FoxPro data.
MORE ABOUT THE CHANGE RECORD IN RE:
There are several 'last changed' fields: for the address, for pledges, etc. Who made the change is also recorded.
Not Yet Answered:
We need each field in the new database documented. This includes size, type, possible values, required or not, and how those fields are used.
CnAttrCat_3_01_Description is less than obvious -- it has values like RM and SM or is blank.
There is a field called "CnAttrCat_2_01_Description" which has a two digit number in it. What is it? What do the numbers mean?
Can we get and start using the source field again?
The various deceased-member issues.
Is there are limit on the number of "user-customizable fields" we get?
[edit] "Issues" other than technical
Sam is working on a handbook for the state chairs and other state officers. No specs for it. No outline, and for the next week or so, he's going to be busy preparing for the LNC meeting. We could try to help start him off at the LPedia.
Robert is negotiating with Blackbaud over a sum in the $20-$30K range. They are hoping to return RE:Anywhere for credit, and to end up with some modules necessary for "web connectivity," which was not explained.
When asked to provide information about changes so that the states didn't just have to figure it out when they saw the changed data, staff said that they would tell us about changes (per what Chris Farris has described and advocated for, presumably), but they weren't planning on telling us about "improvements." This was not acceptable to some, including the NY chair and the NY Nidus admin, who were both on the call. States need notification when policies that affect how data are handled are implemented. Notifying states of improvements is not only good for morale--eight months into a still-incomplete transition--it is in HQ's own good because it will reduce support costs.
[edit] V. Fifth-tier
i.e., we can do it ourselves in Nidus or Chris has said that he plans to do it at lp.org and we should expect it by August 2005, per the RE GamePlan 1.0.
A. Out of state notifications -- This is important, and Chris's plan sounds good to Bonnie. She asked whether the change history might be storing this information already, Chris said no, so that's why he's planning on keeping the field separately.
B. In Michigan (and I imagine this problem extends to other states), we have a county called "St. Clair", however, sometimes RE refers to it as "Saint Clair" and sometimes as "St. Clair". Can we talk about standardizing our addresses?
C. The state DBAs have talked about the need for 'voter address' fields. Is this something that national is interested in, or should we do it on our own?
[edit] Bug Tracker
The Libertarian Party Fix-it List PHP Bug Tracker is now available at http://phpbt.lpf.org/, courtesy of Steven Kuck. Please record new problems there.
[edit] Spec-writing
Please fill this space with what you think should go here.
[edit] Proposed LPDB SLA
Originally proposed by Chris Farris and included in his Raisers Edge Game Plan (April 2005 to August 2004). Bonnie Scott (NY)'s version of the Gameplan can be found here: [3], and that's what the below is starting from.
[edit] Service Level Agreement for Database Export to LP State Affiliates
Version 1.0: Chris Farris, chrisf@lpgeorgia.com
Version 1.1: Bonnie Scott, rabbit@cownow.com (BJS)
Version 1.11: added lifetime field to output (BJS)
Note: prior authors may not have endorsed later versions
The following is a proposed service level agreement (SLA) that the LNC-HQ will follow regarding the data exports to the states. It is designed to be database implementation independent. In other words, this SLA will be applicable under Raiser’s Edge and any future database that the LNC-HQ may choose to implement in the future.
1. For the purposes below, “constituent” is anyone who has paid money to the LP in the past, regardless of whether or not they signed the oath, along with all individuals who have requested information from the national party (aka inquiries).
2. LNC-HQ will provide all officially recognized affiliates with a list of constituents residing in the area covered by that affiliate.
3. The list will include the following data elements: - A Unique ID to be provided by LNC-HQ - The constituent’s First, Middle (if provided) and Last Name - The postal address on file to which the LNC-HQ sends postal mail. - A home and work phone and email address - if the constituent has provided such information to LNC-HQ - The county the constituent lives in, if that information can be obtained via commonly available sources within reasonable cost. - The constituent’s Join and Expiration Dates, and when the record was last modified - The LNC-HQ's donation classification level (basic, sponsor, sustaining, patron, life) - The "life member" field. - Indication whether the constituent has signed the oath or not (i.e., member vs subscriber) - UMP audit information, namely "Gift Total Amount_1" and the monthly "UMP Amount," or UMP payment per member
4. The LNC-HQ may, at its discretion, provide additional data elements (appended to the end of the record to not disturb the order of previously-available elements without notice). It is under no obligation to provide additional elements unless petitioned by a majority of the state chairs.
5. The LNC-HQ will make this constituent list available within the first 5 business days of the month to the Chair of the affiliate, or his designee via email, and weekly via the website.
6. Only the Chair of an affiliate may request that additional people receive copies of the constituent list. HQ will establish and publish formal procedures for state chairs to follow in this regard.
7. The LNC-HQ will endeavor to provide the list in the file format requested (PDF, Excel, CSV, etc), but is under no obligation to do so, as it cannot guarantee that all file formats will always be available. It does guarantee that it will not change the output format without one month’s notice (other than on an emergency basis, as determined by the chair) after this SLA has been adopted.
8. Should the LNC-HQ desire to change the quantity or order of the data elements, other than as provided in section 4, it will provide one month's notice of such change.
9. Furthermore, LNC-HQ will make timely announcements about batched updates of multiple records, such as those correcting discovered errors or implementing new policies, so that they don’t have to respond to questions later when the states notice the systematic changes in the dump.
10. All official communications regarding the database export format will be made via the state chair's email list and the state DBA list at lp-database@yahoogroups.com. It is the responsibility of the state chair to ensure that the appropriate people in his/her affiliate are expecting these communications and able to receive them. The state chairs list is echoed to statechairs_echo@yahoogroups.com for LP members other than states chairs who wish to read the messages posted there.
Chris or a representative of the state DBAs will propose this document to the LNC at the May LNC meeting in Dallas. The LNC should solicit feedback from the state chairs and database managers, and then adopt an appropriate SLA and incorporate it into the LNC Policy Manual at their subsequent meeting.
[edit] Proposed amendments from Chuck Moulton
Chuck writes:
I favor amending C4 and J as follows:
C. The list will include the following data elements:
4. A home and work phone and email address - if the constituent
has provided such information to LNC-HQ and has not opted out
of calls or emails.
J. All official communications regarding the database export format
will be made via the email address database@[state url] and that it is the responsibility of the affiliate to point the email address to the appropriate people with email forwarding.
(e.g., database@lppa.org)
Chuck Moulton Region 5E Alternate, Libertarian National Committee
[edit] amendment to the amendment?
Bonnie counters the amendment to J with a suggestion that national set up a set of aliases, e.g.,
nydata@lp.org -- for where the data goes to
AND
nyadmin@lp.org -- to reach the people managing it in the state
Let the state chairs provide who and where those should go to. Start with all the *data@lp.org addresses going to where they send the reports now.
[edit] What this still doesn't cover
Definitive notification of duplicates merged.
[edit] What it won't cover
State change notification: that will come from a downstream system, like the planned translator. (See the Gameplan, referenced above.)
[edit] Comments
If you have something to contribute, but don't know where or how to put it in the sections above, then put it below. Somebody can integrate it later.
[edit] Things I Track
from Hardy Macia (VT)
- Contact log with members(sent email, got response,called,...)
- State board membership (state exec board member, state committee members)
- Town board membership (chair, secretary, vc, treasurer)
- Donations and purpose (will we have access to enter state donations?)
- Answers to WSPQ Survey if lead was generated at an WSPQ outreach booth.
- Volunteered for (outreach, phone tree, convention organizing, helping with campaign,...)
- Newsletter (email, snail mail, temporarily away,...)
- School
- Groups belong to
from Bonnie Scott (NY)
- Contact log: calls made, feedback from member about reasons for inactivity
- Where available, information collected by the Kia site about volunteer interests. See: http://www.lp.org/action/volunteer.html
[edit] See also
- Bay Tea System under development for distributing databases.
- LPUS Database-archive Old stuff
- Nidus A system to access the database.
