Bare Bones Web Database
©2005 IC Engineering, Inc.
This server software provides a means for multiple users to view and modify organizational data (but not at the same time) without too many bells and whistles.
Jump to:
System Access Logon Screen User Name Password Changing Password Last Database Access Permissions View Only Database Structure Comma Separated Variables Header Row Changed File Persistent Options Download the Entire Database Download older replaced Records Help View Log To Download intra User Notes Search Screen MdLP/YLM Expiration Date Search Results Modify Your Search Parameters Results File Order of Records Field Interpretation Member Code Central Committee Young Libertarians National ID and National Expiration Date Record Last Updated Altering the Database Prohibited Characters Dates undo Delete Add a New Record New Feature Requests Mini Excel Tutorial Mini Quattro Tutorial Printing Mailing Address Labels with Word Word Document Template Printing Mailing Address Labels with Word Perfect
If someone else is logged on when you attempt to log in, you'll get a message telling you who currently is in control. You have to wait until no one else is active to get the logon screen.
Logon Screen: enter your User Name and Password, and click Log In (or type <Enter>). Both are case insensitive, i.e. it doesn't matter if you enter them in upper or lower case. If you make a mistake and try an invalid combination, wait 60 seconds before trying again.
Once logged on, you remain in control as long as you continue activity or log out. If five minutes elapse without action, your session times out and you're logged off. Because only one person can log on at a time, please log off when finished so that others don't have to wait an extra five minutes for the system to become available.
When you first log on, the message area at the top of the screen indicates when you last accessed the database. In this way you can verify that no one else has gained unauthorized access to your account.
Changing Password: At any point after logging on, you can change your password. Click the Change Password: button after placing your new password identically in both fields to the right of that button. Inspect the message area at the top of the screen afterwards to see if it is accepted. The case of the password is ignored when you create it. There are no restrictions on which or how many characters you use for the password, but if it is too short you'll be advised to select a longer one.
You should change your password immediately from what is initially provided.
Permissions: an account is configured for view-and-modify capability, or view-only permission. In the latter case, the top message line shows (View only) throughout the session.
The master database contains 104 fields per entry. Many of these fields are not commonly used for routine operations. It is maintained and downloadable in CSV format (comma separated variables). The format is widely supported. It is intended that you can import this file (and others) into your favorite spreadsheet (such as Excel or Quattro) or database (such as Access) application. If the Bare Bones database doesn't offer the capability to search your heart's desire, download the whole file and use your desktop machine to extract the information you need. You then can also work offline at your leisure without preventing any other users from logging in.
The first line in each file is a Header Row which identifies the field names. They might not all be self-explanatory.
When records are deleted, they are actually removed from the master database file (not merely marked as deleted). However, the removed entries are appended to a Changed file. When records are modified, the new values overwrite the original information. A copy of the record before it is updated is appended to the Changed file. There can be occasion where you need to review deleted and modified entries; ergo, the menu option to download it.
Downloading either the Entire Database or older replaced records provides the full record information, including many fields which might not be of prime concern. When inspecting the Changed file, review it from the bottom up to see the most recent actions first.
While logged in, several options are provided at the bottom of every screen: Search Screen, Download the Entire Database, Download older replaced records, View Log, intra User Notes, and Help. The Help link opens this description in a new browser window.
View Log: displays system activity. It is useful for security purposes, but can also prove helpful to verify that some other user has not recently edited (updated) an entry you're working on. You might also need to contact whoever changed that entry if it doesn't match your understanding. Look at the log entries at the bottom of the screen for the most recent transactions.
If the display screen contains a center portion, an Uncluttered Screen link appears in the bottom menu.
The remaining options are discussed elsewhere.
To Download: left-click on the desired download link; either select Save As (or something similar, depending upon your browser) and pick the target location (such as the desktop) on your machine, or open the .csv file directly into Excel. (The resulting filename should be database.csv, changed.csv, or results.csv.)
intra User Notes: as an adjunct, you can read and create messages to communicate with other database users. After clicking this link, scroll up a little to review the most recent additions. Enter your comment in the text box, and click Add My Note.
The Search Screen can be used to:
Several fields are provided to assist in these functions. An empty field (First Name, Last Name, Greeting, City, County, Zip Code, Memcode, Notes, MdLP Expiration Date from, MdLP Expiration Date through, YLM Expiration Date from, and YLM Expiration Date through) does not restrict database matching. Placing characters in one or more of these fields can only reduce the number of records found. To be included in the results, each of the corresponding database record fields must match the search field values beginning with the first character (except for Notes); matching is case insensitive. For example, putting "mc" in the Last Name search field finds all McDonald's, McNeil's, etc.; "baltimore" in the county search field returns Baltimore City and Baltimore Co. Further narrow your search by using more than one search field ("r" in First Name and "mc" in Last Name will include Ronald McDonald among the results).
A checkbox for MdLP Member, Central Committee Member, and YLM -- plus Monthly Pledger -- permits you to restrict the results to those groups (but these must be used in conjunction with the MdLP/YLM Expiration Date to validate current membership). A search with the default Search Screen settings finds all MdLP members, expired or not.
Checking MdLP Member matches a numeric Member Code field. Placing a character in the Memcode field (S, C, N, P) restricts matches to those record types. If the Memcode field is used, don't check the MdLP Member box. Using S in the Memcode field along with entries in the MdLP Expiration Date fields locates current newsletter subscribers who are not members.
Placing a date in the MdLP Expiration Date from search field restricts matches to those whose memberships expire on that date or after; placing a date in the MdLP Expiration Date through search field restricts matches to those whose memberships expire on or before that date. Use both fields to find those expiring within a particular date range. The YLM Expiration Date from/through search fields operate the same way. See dates for a date format description.
Once the search fields are set, click the Search button to find your matches. The Search Results screen shows the number of matches found. Click the Next and Previous buttons to peruse the records; left-click the Download Results File link to transfer results.csv to your local computer (for offline viewing or to create a mailing list).
To modify your search parameters, use your browser's back button; to start a new search with the default settings, click the Search Screen link again.
Results file format: the results.csv file only contains the fields displayed on the Search Results screen (not the 104 from the database itself). It also has one additional (useless to you) field prepended to each record, which indicates what the corresponding record number in the master database is (which can change).
Do not attach any significance to the order of the returned records in the results file. These are the same order they appear in the master database file, which is not constant. They might seem to be in alphabetical order by Last Name because that is the initial state of the database; but this will change as records are added and deleted. If order is important to you, sort the records in a spreadsheet.
Field Interpretation: Most of the fields are self-explanatory. However:
Young Libertarians of Md. (YLM):
Important: irrespective of the Member, Central Committee, and YLM codes, the MdLP/YLM Expiration Date must be current to be considered an active MdLP, Central Committee, and/or YLM member.
National ID and National Expiration Date: this information might or might not be up-to-date from the National LP.
Record Last Updated: the date shown in this field is automatically maintained. It is overwritten when a record is modified (updated) or added.
These options are not available for users with View Only permission.
To modify fields, search for the desired record until it is displayed on the Search Results screen. Update the desired fields. Prohibited characters are comma, single and double quotes, and backslash (\). There are no validity checks or bounds verifications. No error messages appear to warn of possible entry errors. Try to follow the conventions used in other entries, especially with regard to upper/lower case and county names. For example, the county field presently shows "Baltimore City" and "Baltimore Co."
Dates: enter dates in [m]m/[d]d/yyyy format. You must include two slashes. [m]m and [d]d means that you can enter one or two digits each for the month and day. The month and day are converted into one or two digits as needed, and the year is stored as four digits.
To update the displayed record, select the Store Modifications option and click the Perform Above Selected Action button. There are no further warnings, as it requires two separate clicks to accomplish the update (because the radio buttons default to No Change). There is no undo option, but you can inspect the previous value (with some difficulty) by downloading or viewing changed.csv. Check the message area at the top of the screen after updating.
Delete a displayed record by selecting the Delete Record radio button and then clicking Perform Above Selected Action. There is no undo option, but you can inspect the deleted record (with some difficulty) as above. If necessary, add a mistakenly deleted record back in using information from changed.csv. Check the message area at the top of the screen after deleting.
Add a New Record: this menu item is always available. However, you should double and triple check with Search Screen first to be certain that a record doesn't already exist corresponding to the new data (uncheck MdLP Member first). There are no internal checks to verify this! Clicking the link displays a blank template similar to the Search Results screen for you to populate. For a member, be certain to set the Member Code field to "1". The First Name is automatically duplicated to the Greeting field unless something is placed into Greeting (a nickname). When ready, select the Add new record radio button and subsequently click Perform Above Selected Action. Check the message area at the top of the screen after adding.
New Feature Requests: You can ask, but don't hold your breath.
Best advantage can be taken of the database if it is used in conjunction with a spreadsheet or database program. Of these, Microsoft's Excel is perhaps the most common.
Begin by downloading database.csv, changed.csv, or results.csv. Double-click the file and and see if Excel automatically activates. If not, right-click it and select Open With Excel (you might as well also check always use this program to open this file so that you can just double-click it in the future).
Let's assume you've loaded results.csv. Click the corner square at the top left of the data area which abuts the Column A and Row 1 labels. This selects all of the data. Click Format (top menu item), Column, AutoFit Selection. Now all of the columns have adjusted so that all data characters are visible. Note that row 1 shows the field names. The most useful feature is Sort. For example, click the cell where row 1 and column F intersect. Click Data (top menu item) and Sort. The Sort option window highlights LastName, and My List has Header Row is selected. If not, manually pick both. Click Ascending if not already set, followed by OK. Now you have an alphabetized list.
Sorting by column Z (EXP_DT) orders the rows (records) by MdLP expiration date. While we're discussing dates, you can change the date format as follows: click the "Z" header above row 1; this highlights the entire column. Right-click anywhere in the column and select Format Cells. In the Number tab, click on Date. In the right panel, select the desired date format (such as 14-Mar-98). Click OK and the expiration dates all change accordingly.
To delete unwanted columns, hold the Ctrl key down and left-click on each column letter (such as A, B, AB) above the header row (1); click Edit (top menu item) and Delete. You can also select a range of rows by dragging the mouse over them or left-clicking the row number at one extreme, and holding down the Shift key and left-clicking the row number at the other extreme. Click Edit and Delete to eliminate them. Note: if you use the Delete key instead, the selected cells are made blank instead.
You can create customized printouts of names, email addresses, telephone numbers, etc. this way.
Zipcode issues: If all of the results are for Maryland locations only, this doesn't matter. Otherwise (such as complimentary and donor addresses) instead of simply opening as file.csv do the following. Rename the downloaded file (or just start that way) as file.txt; open Excel and open file.txt; choose delimited, check (only) the comma box, highlight the Zip field and select text format. (Without doing this, it automatically formats the zipcode as a number and removes any leading zeroes.)
Best advantage can be taken of the database if it is used in conjunction with a spreadsheet or database program. Quattro is Word Perfect's spreadsheet application. These instructions are for Corel Quattro Pro 8, but later versions should operate similarly.
Begin by downloading database.csv, changed.csv, or results.csv. Run Quattro. Click File/Open the csv file (you might have to select CSV under file type).
Let's assume you've loaded results.csv. Click the corner square at the top left of the data area which abuts the Column A and Row 1 labels. This selects all of the data. Click Format (top menu item), QuickFit. Now all of the columns have adjusted so that all data characters are visible. Note that row 1 shows the field names. The most useful feature is Sort. Click Tools/Sort (top menu item) and check the Selection contains a heading box. Click the tab in the 1st field and select the desired sort method. For example, select LastName. Click the Ascending box if not already checked, and click the Sort button. Now you have an alphabetized list.
The date fields are in textual format, so sorting by them doesn't really do what you'd like. (Excel converts them, but Quattro doesn't).
To delete one unwanted column, left-click the column letter (such as A, B, AB) above the header row (1); click Edit (top menu item) and Delete Column(s). Note: if you use the Delete key instead, the selected cells are made blank instead.
You can create customized printouts of names, email addresses, telephone numbers, etc. this way.
Microsoft Word provides a built-in Mail Merge feature which can be used with the database to create mailing labels. If the Search Results provide a complete list of the desired addresses, you can directly use the downloaded results.csv file. Otherwise, import the file into Excel and delete unwanted rows -- don't worry about unwanted columns (fields). Sort by any desired column. Save the file either in csv or xls format. You can close Excel. You can also use Word itself to select which rows will be used for label generation and skip the Excel step.
Let's print mailing labels with the MdLP expiration date on the first line; the name on the second line; the address on the third and fourth line; and the city, state, and zip code on the fifth line.
Word 2000:
Open Word and create a new document. Select a font and a font size of 10 points or smaller. From the menu, select Tools/Mail Merge. Click Create/Mailing Labels. When prompted, select Active Window. Click Get Data/Open Data Source. Navigate to the csv or xls file (you might have to select All files *.* to see it) and open it. When prompted, select Set Up Main Document. Pick the label format number, such as Avery 5160 (which is 30 labels per sheet). Click OK. A Create Labels window appears.
Type "MdLP Expiration Date:". Click Insert Merge Field; EXP_DT; <Enter> (to get to line #2); Insert Merge Field; FirstName; <space>; Insert Merge Field; MiddleName; <space>; Insert Merge Field; LastName; <space>; Insert Merge Field; SuffixName; <space>; Insert Merge Field; SuffixLetters; <Enter> (to get to line #3); Insert Merge Field; ADD1; <Enter> (to get to line #4); Insert Merge Field; ADD2; <Enter> (to get to line #5); Insert Merge Field; CITY; ","; <space>; Insert Merge Field; STATE; <space>; Insert Merge Field; ZIP. Of course, you can customize this as desired.
Click OK and Close. On the Mail Merge toolbar, click Merge. Select all records to a new document (default settings) and then click Merge. Inspect the layout on screen; you might need to adjust the font size and check for good label formatting. Print and you're done!
This process takes more time to explain than to actually do. If it is a repetitive task, you can use Word's macro capability to record your keystrokes, storing a macro file which you can later run to do this entire process automatically. In fact, just saving the formatting page is all that is necessary; the final Merge step does the rest.
Word 2002:
Click the menu item Tools/Letters and Mailings/Show Mail Merge Toolbar. On that toolbar, click the Main Document Setup icon. On the resultant Main Document Type window, select Labels, pick Avery 5160 (for example), and then OK. Click the Open Data Source icon on the toolbar. Navigate to the results.csv file and select it. Select a font and make it 10 points or smaller. Click the Insert Merge Fields toolbar icon. A window comes up with all of the field names on it; in turn, click on each field and Insert (EXP_DT, FirstName, MiddleName, LastName, SuffixName, SuffixLetters, ADD1, ADD2, CITY, STATE, and ZIP); then close that window. Now go to the document and type "MdLP Expiration Date: " in front of EXP_DT; add an <Enter> after EXP_DT; put spaces between each of the name fields; put <Enter> after SuffixLetters, ADD1, and ADD2; a comma and space after CITY; and a space after STATE. Click the Propagate Labels icon; the View Merged Data icon; and finally the Merge to New Document icon. Review and print.
The details might differ a bit from these descriptions, but any modern word processor should be able to merge directly from the results.csv file.
You can try using this Word Document (right-click and save ResultsLabelMacro.doc to your desktop). Place results.csv on the desktop. After opening ResultsLabelMacro.doc (if prompted to choose the encoding, select Plain Text), click Merge from the toolbar and Merge from the prompt window. This works on both Word 2000 and 2002. You should get the above example. Due to differences between systems, you might be prompted to locate the results.csv file.
Word Perfect provides a built-in Mail Merge feature which can be used with the database to create mailing labels. Let's print mailing labels with the MdLP expiration date on the first line; the name on the second line; the address on the third line; and the city, state, and zip code on the fourth line. First download csv-to-dat.wcm and mdlplabels.frm (by right-clicking these two links and saving target as). You only need to do this once. For each run, use Search Results to download the results.csv file. These instructions are for Corel Word Perfect 8, but later versions should operate similarly. From Word Perfect, File/Open results.csv (you might have to change the filetype to *.* to see it in the open window); convert file format from ASCII Dos Text. Click Tools/Macro/Play and select the csv-to-dat.wcm file (navigate to wherever you stored it; but if you move it into whatever default directory Word Perfect goes to from this dialog it might be easier to replicate). Click File/Save As and change the filename to results.dat. Close the results.dat window (but not Word Perfect itself). At this point you have converted the csv file into a record format that Word Perfect understands.
Click Tools/Merge. Click the Perform Merge button. Enter mdlplabels.frm into the Form Document field, and results.dat into the Data Source field -- you'll have to navigate to wherever you've stored these files. Leave Output as Current Document. Click the Merge button.
Highlight and delete the first two useless labels. Review the labels onscreen and print.
Instead of printing address labels, you can print directly onto an envelope. Follow the above directions using the mdlpenvelopes.frm in place of the mdlplabels.frm file. It is configured for a #10 business envelope; it is assumed that the MdLP return address is preprinted. Remember to preview onscreen, and to delete the first (header) page. Or, you can specify records beginning with record 2 through the last one to prevent the creation of the dummy beginning page.
Comments to Robert E. Glaser