Profile

Designs by Valerry (DBV) is a small, Chicago-area costume manufacturer run lovingly by the co-owners, Val and Perry Krilis. For years, DBV has been putting out an annual catalog filled with bright, sequined, cute and cuddly costumes for dance companies and theatres around the world and making a decent living at it. They've only recently set up an e-mail account and are not on the Internet and like it that way...they're bursting at the seams (pun intended) as it is trying to keep up with the very seasonal costume-creation business they already get.

Technologically, they have the basics...several iMac (and older Mac) workstations and an iMac FileMaker server. Also, there's a DOS-based workstation that handles FedEx shipments.

DBV is a medium-sized, busy, rapidly growing Chicago-area company who's database needs are a system that can track orders and customers, do payroll and accounting, and generate what are called cut tickets which tell the cutters upstairs how much of each material to cut for that round of costumes that are about to be sewn and assembled.

Challenge

The first challenge was to design a system that could provide the functionality DBV wanted in many stages over time and on somewhat of a showstring budget. The second challenge was developing a sophisticated system that tracks all of the cosutmes they make, allowing them to be put on orders, keeping a very accurate count of all inventory, tracking returns and customer credits, doing a biweekly payroll and generating meaningful reports.

Solution

DBV did have an existing FileMaker 3.x database system that basically helped them keep track of all the recipes for the costumes they make (two yards of this, six buttons, two zippers, et cetera). When Application Architects took over, we added a checks, deposits and payroll system, an accounting module, a live inventory system, an invoicing module, purchase orders, cut tickets, back orders and a customer refunds and credits module. We didn't augment the graphic user interface that much, but we did automate the functionality quite a bit. For instance, when an order is created, it automatically requires a deposit which is then entered into the deposits database, it automatically decrements inventory for all materials in all costumes on an order and automatically generates a "shipping week of" date based on how many costumes DBV can manufacture in a week (which is about 1,200!) and then automatically prints the invoice for faxing to the customer (electronic faxing integration is also used).

System

The system quickly spits out payroll checks every other Friday based on an employee's salary or hourly rate and records the checks in the register. Also, the payroll module generates many useful tax reports such as the 941, FUTA and SUTA reports.

The Styles database keeps track of all styles (costumes) and materials that make up those costumes. Creating a new style is as easy as clicking a button and selecting a few materials and quantities for those materials in a few fields to make the style's "recipe". After that, the style is ready to go on an invoice. The Styles database also shows you how many costumes are on hand in inventory, how many have been sold, shipped, returned and et cetera.

Cut ticket reports include the total of every material needed to be cut for that week, including what sizes of costume are being cut and for what invoices.

The checks and deposits areas allow DBV to keep track of all money coming in from customers and all checks going out to DBV's vendors.

The PO database automatically generates POs based on materials that are needed for the costumes that have been ordered.

Special Features

The styles area of the database is a self-recursive data structure, allowing the styles and materials that make them up to live in the same database. This allows DBV to also sell raw materials out of their outlet store which they sometimes do and also allows them to create sets of styles if they desire them to be sold as a "package deal".

The system employs the Troi Text plugin to sum related text for cut ticket reports.

The system can generate many meaningful sales reports such as best-seller lists, sales by style, date range and et cetera.

The reconciliation table allows DBV to reconcile their transactions just like in Quicken or in a checkbook.

The Print Checks function allows DBV to print many checks at once (even "summary checks" which cover several bills from one vendor at once) just by checking off which bills they want to pay and clicking the Print button.