AcmeBase Features

AcmeBase refers to three different concepts. There is the AcmeBase meta-database, a fancy data structure that describes a SQL database. There is the AcmeBase library of CGI programs that use the AcmeBase meta-database. And then there is the sample application that is setup to test the system and to base new databases on. It is a customer database intended to be a core application, and its features are also listed below.

Goals

  • Customizable. AcmeBase is a collection of tools that an organization can customize to "make it their own". Page headers and footers and general appearance is set in a collection files that can be localized for each installation.
  • Database neutral. AcmeBase can interface with different database engines, including MySQL and PostgreSQL.
  • Dense. Lists can be large and forms can be comprehensive, including fields from several database tables.
  • Fast. Quick to setup, quick to change, and quick to enter and alter data.
  • Accurate. Lookup fields, auto-fill entries from other tables, and audit trails are supported. AcmeBase can provide the answer the common user question: "Who changed that?".
  • Secure. Access to database tables, table records and columns, reports, and programs can all be restricted to specified users.
  • Pleasant. A system designed to be used by employees every day, as opposed to being occasionally used by the general public. Access to information is as direct as possible using the fewest number of keystrokes and mouse clicks.
  • Independent. To not be under the influence of other software and their version changes.
  • Self maintaining.
  • Self monitoring.
  • Remotely administered.

Assumptions

  • Several different users access the database.
  • Each user logs on to the system using individual IDs.

Concepts

  • Forms are used to enter and edit database information, Lists are used to a) pick an item to edit, or b) perform operations on the entire list like export them or edit them. View screens are richly formatted views of all the pertinent information. They are usually custom built and are analogous to "getting the folder" on the topic. For example, a customer View screen would display their billing address, shipping address, a log of advertising sent them, a log of customer service comments, their pending orders, and their invoices going back a couple of years.
  • List files are text files that holds record ID numbers of a database table, one record ID number per line. List files allow the user to save a "snapshot" of a query. They can later be retrieved and redisplayed. They can be joined with other queries, or they can be excluded from the results of a new query.
  • Text array is a term used to refer to a text field that holds several values. A text array is simply a text string that begins and ends with the punctuation character that is used to separate each element of the array. Some examples of a text array are:
    ;one;two;three;
    |1|2|3|
    Searches for individual elements can be easily made by matching strings like ";one;" or ";two;". Values can be entered into a text array by using an input array, which is a collection of form fields used to enter each item of the text array. They are grouped together into an input array because the actual fields which are used for input can be dynamically changed depending upon lookup table searches.
  • Edit-bullets refer to bullets in front of items in a list. They are usually links to edit the listed item. This is a general concept that has been adopted to clean up displays and to allow the item's text to be used for a link to the View screen.

General Features

  • Standard HTML is used for forms. A field naming convention is used to interface the forms fields to database columns in lieu of using a custom mark-up language with special tags. The Form Filler does look for HTML comments looking like <!--$ ... --> and interprets them as commands to execute and substitute their output.
  • Any form can be used to display database information, but they have to come from a pre-determined "secure" area on the server.
  • No cookies are used. No session-id's either, which means no timeouts.
  • Reconciliation screens are used if someone changes the database information between the time you display a form and the time you post it back to the database.
  • The system uses pop-up windows. Several windows can be open at once so you can compare and contrast your information.
  • It uses a central console screen from which all other windows emanate.
  • It uses a central user options screen.
  • Navigation bars are used to separate sections on Form and View screens. They provide buttons to go up, go down, go to top of page, bottom of page, or to go to the previous place on the page where the user started skipping from.
  • Appointments and call-backs are emailed to users daily. Call-backs that are not acknowledged can be forwarded to an administrator for further action.
  • The appearance of all windows is determined by CSS style sheets.
  • Configuration files are cached and automatically re-read by the system when changed.

Searches

  • Several fields can be searched at once depending upon how the search string "looks" and on the search fields defined in the AcmeBase schema files. For example:
    • If a search entry looks like an ID number, then the individual record is looked up.
    • If a search entry looks like a list of ID numbers, then those records identified by the ID numbers are listed.
    • If a search is all capital letters or all lowercase letters, possibly with numbers, and a code field has been defined in AcmeBase, the code field is searched.
    • If a search has magic characters (?.*[]) in it, then the name field is searched for matching names.
    • If a search field has a @ character, then the email fields are searched.
    • If a search field looks like a phone number, then the phone fields are searched.
    The system administrator can identify more than one name field, code field, email field, or phone field in AcmeBase. It can be set up that a search for a phone number can look at both the home phone number field and the business phone number field.
  • Search strings can be scripts which can define a title for the search, parameters the user needs to enter first, date arithmetic, list files to include or exclude, and the list's sort order.
  • Past searches are saved for re-use later by the user. It helps users remember what they were previously working on.
  • Query by form. The form stays on the screen so that its parameters do not need to be re-entered for each list queries. Pop-up parameter entry screens assist formulating search strings.

Forms

  • Forms fields can be entered using the keyboard's keypad. To do so, the following characters are special:
    Enter key
    Goes to next field in form.
    Ctrl-Enter
    Submit the form.
    Esc
    Cancel and close window.
    Ctrl-Spacebar
    Show main console window.
  • Dates can be entered the following ways, with delimiter characters, without delimiters, or as relative numbers:
    mm/dd/yy
    mm-dd-yyyy
    mmddyy
    mmddyyyy
    mmdd
    0
    today's date
    -7
    seven days ago
    +30
    thirty days from today
    Dates can also be picked from a pop-up calendar.
  • Drop-down lists of options include an <Add New> option (if security allows) that allows one to dynamically add an entry to the list of options.
  • Combo fields present a drop-down list of options for a field and an input field that the user can use to enter a value that is not in the list of options.
  • Table look-ups are defined by a Search button or a Choose list. A Search button pops up a lookup window. A Choose list is a drop-down list of options and the options are loaded when the form loads. Any number of form fields can be assigned values from the lookup table.
  • Multiple tables can be easily entered on one form if they are all joined one to one or one to many.
  • Double-clicking on a form field label when editing a record pops up an audit window which lists the changes to the field, when the changes were made, and by whom.
  • Text configuration files are used to define forms. They are quick and easy to copy and change.
  • Forms can be setup and tested before creating the database tables.
  • Forms can be saved as templates and manually changed to appear just like you want.

Lists

  • Lists can be rendered as a formatted list or as a table of rows and columns.
  • Lists can be customized and saved as reports.
  • List columns can be resized.
  • Filters can be set to change the list contents.
  • Individual list items can be checked or unchecked for group export or edits.
  • Lists can be exported as CSV text files directly into Excel.
  • Lists can be saved in text files and retrieved later for further processing.
  • Lists can be compared to each other for duplicate records or to find records in one list that are not in the other.
  • The items in a saved list can be excluded from a query. This allows one to build up a list of, say 5000 names, that can come from several different search criteria, each search adding only the new names to the list.

Calendars

  • A client-side calendar renders calendars by the year, quarter, month, or day, and it can render a month as a little pop-up calendar, as a full-screen calendar, or as a list.
  • Calendars can be used to display any database records from any table that has date columns.
  • Several calendars can be superimposed on one another.

Security / Maintenance

  • Users can be restricted from viewing any database table or field, any database record, any report and any program.
  • Forms do not display fields that the user cannot enter or alter because of their lack of security privileges.
  • A security alert is emailed if there is an attempt to alter a database field that is not defined on the form.
  • All programmed SQL statements that are combined with user search strings are checked to see if the user has access to all the tables and fields in the query.
  • Database errors and security alerts are emailed to system administrator(s).
  • Lists and exports can be limited in size and email can be sent to a supervisor if a limit is exceeded.

Sample Database

  • Database tracks customers, advertising, sales, and keeps a running log of interactions with the customer and user comments.
  • It accommodates both domestic (American) and international addresses.
  • It tracks customer's privacy preferences
  • Easy import and export of customer data from and to spreadsheets -- including dupe checks.

Future

The following are the projects and enhancements to AcmeBase which are being worked on. They are not listed in any particular order.

  • Simplified installation and setup procedure.
  • Generalize customer import procedure from spreadsheets to be used with all tables.
  • Update database tables from spreadsheet data.
  • List sub-totals.
  • Enhance Join property syntax to allow table synonyms and self joins.
  • Allow more than one detail table to be associated with a field.
  • Write parse_address() JavaScript function to parse a name and address into its constituent parts. It will allow an address to by typed into (or copied and pasted into) the search box on the control screen so that the database can automatically be searched for matching names before the customer entry form is even displayed.
Our proud sponsor.
Top of page.