FileMaker: CATS UofA Bookstores Database System

Description:

My largest project by far, I spent 3 years working on creating, updating and maintaining this system. It was designed to be the backbone of CATS. The only thing it did not manage was the daily inventory, only because there was a bookstore wide system for this. It did track everything from special orders, software sold, to order invoices. The two most interesting pieces to me are the scheduling and software license systems.

Features:

  • Customer Tracking: There is tracking for both individuals and accounts (UofA Departments) through the whole system. Any type of record could be one or the other often both.
  • Product Tracking: While there was a central POS and inventory system for the bookstore it did not tie into the CATS system very well on its own. To bridge this gap there was a system created to download all of CATS products and dump them into a FileMaker database. The user side of this bridge was the database Products-PRISM which made it so that information about products could be pulled into various ordering and tracking databases. To assist in the extra needs of the licensing team there was an extended products database created for them.
  • Software Sales Tracking: As an educational software reseller the bookstore had to track who was buying what software to be sure that each student only purchased one of each title. While departments had no such limit we still had to account for each box sold. Software lists were tied to either an individual or a department.
  • Special Orders: This gave us the ability to order items for customers. It tracked what needed to be ordered, which PO it was ordered on, when it was received, and when customers were contacted to pick it up. There are three screens to accomplish all of this: order entry, buyers report, and customer pickup.
  • Order Invoices: Part of the tracking needed as an education reseller also entailed tracking computers sold. Even more important was as a service to the buyer, since we held onto records for years it enabled customers to come back to us for serial numbers and purchase receipts. To assist in the tracking of ordering and receiving there was tie between this database and special orders.
  • Service Invoice: To help the service center track repairs a database was created for checking in/out and tracking repair data.
  • Licensing System: To assist the licensing team this system was developed to track orders, POs, and distribution. They had been using a handwritten worksheet, one for each type of license. Orders would come in and have to be added to the sheets until enough of each type of license was requested for a PO to be placed. Their system was full of redundant work, and made it rather hard to track who wanted what and how many. While they had managed it this way for many years, I worked with them to develop this system.
    • Product Tracking: This is an extended version of the Products-PRISM database. It includes information on customer requests and POs.
    • Customer Requests: From here the buyer can enter into the system the customer's requests. Instead of a separate sheet for each type of license the buyer can enter all the customer's licenses in one place. After filling out the information the database calculates if any licenses are available for the customer.
    • PO Tracking: To help the buyer figure out what to order and when the PO system looks at the outstanding customer requests and gives the buyer a list of each type with the licenses needed. Once the buyer selects what type of licenses to order it builds the order list so the buyer can enter how many of each license to order.
    • Receiving: When licenses come in the buyer receives it into the system entering in important data such as cd keys and other needed codes. From here the buyer then triggers the distribution of the received licenses.
    • Distribution: From here the buyer can print the license and track the contacting of the customer for pickup.
  • Schedule System: Before this database an excel spreadsheet was used to track employee scheduling. The problem was that the calculations involved loved to break, and it was hard to keep track of which file was the most current. The other issue came in to play for employees trying to track down the printed copy, and there often ended up being a few different prints of the same schedule with variants depending on what changes had been made. Essentially a big confusing mess. This database was designed to streamline this process and to make it easier for employees to check their schedule. This database went through several versions while I worked at CATS, and I am working on a FileMaker 7 version I hope to have released soon.
    • Semester: Also called time period. Schedules at CATS generally ran for a full semester, hence calling the time period a semester. It would allow for exceptions by entering another period overlapping the main one.
    • Staff Info: lists all the current employees, also used by the email system to create team wide emails. There is a regular staff view, and a management view with more details.
    • Schedule Input: One of the advantages of this system is the ease of entering the hours worked, and getting instant graphical updates.
    • Schedule Viewing: An employee can either view the week at glance or the daily schedule
  • Out Going Email: To help with customer contacts and tracking emails sent out this was built on top of a FileMaker plug in SMTPit. It was also used to send out team emails.
  • Fix Requests: For when features were broken, or if someone wanted to make a suggestion for something new.
  • LookupID: Created to help facilitate the lookup of customers and departments to add them to orders. This part was phased out in favor of adding customer and department references more directly

Conclusion:

This was was my pet project for most of the time I was at the UofA Bookstore. It started as a need for one or two new features, to having me revamp the whole database from scratch. This is one of those projects that would have never ended. I was only starting the licensing system, and would have reworked the whole thing for FileMaker 7, however I left the bookstore to work for Apple.

Due to this system being owned by the UofA Bookstore I am not posting a downloadable copy to be used. If you would like a demonstration of the database please contact me at eagleeyes@mac.com to set something up.

Links:

Comments: [Add a Comment]

Creative Commons License   This webpage and all of its contents are licensed under a
Creative Commons License by Dr-EagleEyes.com.