Freight Cost Analysis System
RH (Restoration Hardware)
Corte Madera, CA
Restoration Hardware needed a solution to manage their growing shipping operations. They received tens of thousands of line items detailing information related to shipping product from warehouses to their customers. Information like region, carrier, mileage, costs, etc. We developed a system to validate and import data from weekly reports. That data was scrubbed based on data maps and other logic. Filters allowed staff to manually review items requiring additional mapping. Once confirmed, data is pushed to a set of SQL back-end tables. Screens enable users to filter, review and export select data. Reports provide summary-level statistics and other insights by carrier, region, period, rolling 12-month ranges, etc.
Physician-Student Pairing System
UCSF (University of California San Francisco)
San Francisco, CA
The UCSF School of Medicine provides a program where students are paired with physicians for training based on several shared attributes, such as specialty, language, location, etc. With over a hundred students and physicians to match, and communicate with, managing this program manually via Excel was an incredibly time-consuming process.
We collaborated to develop a system to automate this process to the greatest extent possible. Information on students and professionals is maintain in an online database from which exports to Excel are generated. Those exports are imported directly into the new Access system via a click of a button. Listing screens with sort and filter functions enable quick record retrieval. Users can add additional records manually or update attributes as needed. Staff can pre-assign select pairings and allow the algorithm to do the rest. Reports and exports provide details on which students were paired with which physicians. Manual overrides can be applied when necessary. Personalized emails (“Dear Dr. Bones”) , along with details embedded in the body and as file attachments, are automatically generated and sent via Outlook. The finished product enables the department to save hundreds of hours every academic year.
Customer/Invoicing and CRM Systems
Diablo Solar (Diablo Solar Services)
Martinez, CA
When we originally connected with Diablo, they had a single customer database designed in Access they used to manage their 20,000 customers. The system worked but there was room for improvement. We upgraded to an SQL back-end database to improve system performance. We then added invoicing and scheduling modules. Upon receiving a call, customer service agents can quickly recall the customer’s contact details and information about installed products. Using integrated calendars in Outlook, they can schedule technicians for the appointment. Invoice details are entered and are either printed to multi-part forms or automatically emailed to the customer as a PDF attachment.
We also developed a separate CRM system to manage the pre-sales process. Off-the-shelf systems weren’t adequate to meet their specific needs. Sales leads come in via phone and email. These are assigned to sales reps and scheduled in Outlook for onsite meetings. Scheduled meetings automatically flow into our SQL database with contact and other information on potential customers. Reps can enter additional information regarding product details, site information, pricing, etc. They can generate pre-populated contracts as PDF documents and forward to customers as email attachments. Once an installation is begun, customers are kept up-to-date with emails detailing progression. All the pertinent details are copied to the Customer database once the installation is complete. A suite of canned and customizable reports exist, providing management with productivity reports, financial details, and others.
Customer Transaction System
Recycling Specialists
San Jose, CA
Recycling Specialists buys metals and other recyclable products from both corporate customers and individuals. They had an existing system but wanted to migrate to one that had been built years before for a sister organization. But there were problems with the replacement system. There were over 125 tables, 700 queries, 500 forms, as well as several third-party ActiveX components. The code would not compile. The original developer had moved on and didn’t leave behind much in the way of documentation. The fundamentals were there, but the software would not run. The first step was to review the code and correct all offending logic, so the program would at least run. We demoed the program for key personnel and thought we were done. We weren’t even close.
The system worked great for the sister organization, but it didn’t meet their current business needs. We met with users at all levels of the organization to understand their requirements. Management wanted images of every product purchased, so we installed cameras to capture snapshots of items as they are weighed. They wanted to review high-dollar transactions, so we designed a routing queue, where all such transactions automatically go to management for review and approval prior to payment. Scale-house personnel working with walk-in customers needed ID scanners to quickly recall existing customers, tools to review buying history, pricing details, etc. Cashiers needed ways to open and close the cash drawer, call up open invoices, pay customers, and run end of day reports. Finance needed reports on inventory for internal use, and details on recyclable transactions for reporting to the State.
The overhauled Recycling Management System has been in production and used daily since Fall 2017.
Payroll Management System
Bayside Solutions
San Ramon, CA
Bayside needed a new system to manage their growing staffing and recruitment business. They had outgrown the use of Excel spreadsheets with complex embedded macros. We designed and developed a database to serve as a single repository for payroll processing. Details on clients and employees, along with placements, hours and rate history, are all maintained in a single database. Reminders notify staff of key eligibility dates. The system imports weekly timecard data in a variety of formats to eliminate data entry errors and save time. Automated exports to ADP, Peachtree and other external systems further reduce processing time. Management reports including PDF/Excel attachments are regularly distributed at the click of a button. YTD sales reports are automatically created weekly as Excel files and emailed to reps.
Main Office-Laptop Data Exchange Tool
CEIP (California Emerging Infections Program)
Oakland, CA
Historically, CEIP staff was visiting health care facilities and collecting data on paper forms for entry into their database system. This resulted in duplicate data entry, time delays, along with the potential for error. Data in the master system resides across 30 tables and a corresponding number of entry forms. We developed a system where records from the master database were checked out and copied to secured laptops. While checked out, users in the home office are unable to edit the data, instead receiving a “record checked out” message. Today, data is input directly onto the laptop while in the field and synched with the master database upon return to the office, saving time and effort and reducing entry errors.
Board Tracking System
GDCA
Livermore, CA
GDCA was using Excel to track the flow of circuit boards through its organization. Managing the data became challenging as volume grew and the data was maintained across multiple files. We developed a system to track boards at every step of the process. Order numbers are queried against their SAP Business One software to pull information on part number, description, and quantity. Serial numbers are read via handheld scanners and entered into the system. As boards move through inspection, test, debug, etc., table-based rules dictate how and when boards can be advanced. When boards move from one department to the next, emails are automatically generated notifying the recipient. Board movement is logged every step of the way. An intuitive user interface enables non-technical users to build their own queries to retrieve details, and to export that data to Excel or PDF reports.
Facility Certification System
IMQ (Institute for Medical Quality)
San Francisco, CA
IMQ inspects medical facilities for certification. Facilities are evaluated on hundreds of measures. When a facility fails to meet a standard, details are recorded and shared with the facility for corrective action. Previously they maintained their data in Excel and their communications separately in Word documents. It became challenging to manage the data as the volume grew.
We developed a database where all data would be entered and reported. A home screen provides easy search and sort to quickly locate the target facility. Initial drill down leads to a screen detailing high-level information along with a grid of historical records. Clicking a historical record leads to a detail screen with contact info, dates, details on findings, corrective actions, etc. Dozens of reports and letters are available at the click of a button for save and forward to clients as PDF files. Screens identifying records requiring follow up are available to assist staff in managing priorities. Outputs to Excel enable additional analysis and sharing of data outside the system.