In my time at Digital Color Concepts, I made a handful of major changes to the structure of files in use and how they communicate with each other, and how the data flows in and between files -- changes, in other words, to the basic architecture of the solutions themselves.
Creating the Combined NY/NJ System
The first such major change was precipitated by the merger of two companies: Dolan Wohlers Terwilliger of New Jersey became the New Jersey office of Digital Color Concepts. The New Jersey office used a simple FileMaker solution to take down information about potential jobs during the early sales process, and to generate sales proposals and, eventually, sales orders for the press workers to instruct them to begin work. Meanwhile, the New York office used a complex sprawl of FileMaker files as a production-tracking and preliminary job-summarizing-for-billing system. My job was to merge the two systems and host the combined system on a single FileMaker Server, while converting the entire system from FileMaker 4 to FileMaker 6.
Here is a diagram showing what I started with (21 files on the New York side, plus multiple copies each of 2 files used in New Jersey), and the 15-file unified solution I created from them.
Over the course of the next year, most of the changes took place within those files, within that structure. Here is a summary of the only structural changes to the overall system during that time.
The combined system came to an end when administrative personnel decided that New Jersey ought to have its own dedicated FileMaker solution and server, and at their insistence I split the solution into NY and NJ clones, and from that point on they were developed separately and diverged as the workflow and needs differed.
Rearchitecturing the Central File in the New York System
The structure of the New York system was set up to work like this: each job was to contain and consist of a series of tasks, performed by the various departments, and then after the job was completed that list of tasks was, theoretically, to constitute the components of the invoice, hence the file name "InvLineItems" (Invoice Line Items). This was not effective: the attempts to drive the tasklist by priced products made it an ineffecient tasklist, so actual instructions were buried in comments fields and were used as a "diary" between production personnel and the operators doing the image and assembly work, and the resultant list of selected tasks were almost useless to the financial department for invoicing purposes. At the time I came in, a separate Invoice file was under development, and during the Combined System era the invoices were created in FileMaker by generating an "echo" set of records based on those in "InvLineItems" (no longer serving as "invoice line items" at all) and then massively edited with macro scripts and manual rekeying and so forth to generate the actual invoice. The prices for priced products were irrelevant because for many products prices per each product would change as the total quantity changed, as well as differeing from client to client depending on contract and annual business volume and other variations in arrangements between companies, but the existing architecture for looking up prices assumed a set rate for a named product as the per-unit price for that product.
First the contents of the created tasklist was revamped to make it an effective tasklist, a valid activity and work-assignment driven system. This was done with no true architectural change in the system, just changes within the file "JobTicket" to drive a very different data-entry workflow and different valuelists. This removed any connection to priced products for invoicing, but that had not been working anyhow.
The big architectural changes followed: the central file JobTicket was cloned and then split, with one half becoming a new file DccProduct. From this point on, jobs contained priced products, which were driven again by value lists of invoicable items, this time acquiring prices from a more robust product-price lookup architecture that could accomodate different prices per unit as the quantity changed as well as different prices depending on client. Then each such product would contain the tasks which would be performed by the various departments.
Throughout the system, scripts and relationships and other references to the central JobTicket file were examined and repointed to DccProducts where appropriate, and new navigation tools implemented to accomodate the new file and its various screens.
Rearchitecturing the Main Sales File in the New Jersey System
The New Jersey system was structurally very simple -- too simple, ultimately,
to accomodate the things that the people using it wanted to be able to do
with it. As originally acquired, it consisted of only two files: Sales Trac,
the main file, and a file called "Addresses", which was used simply
to drive a dropdown value list of clients and then populate the client address
fields in Sales Trac once the client had been selected. (Addresses was subsumed
into Clients during the creation of the Combined System, and New Jersey
continued to use their copy of Clients in this capacity after the Split).
Other than this value list drawn from values in an external file, Sales
Trac as I acquired it was a flat-file system of the sort that could be written
in FileMaker Pro 1.0. Even after I added scripts and relationships to connect
it to JobTicket (a proposal once accepted by a client would become an actual
job, so a corresponding record would be generated in JobTicket, which would
acquire its data from Sales Trac, after which point the workflow would take
place entirely outside of the original New Jersey files), it remained essentially
a relic of an earlier, flat-file era.
Sales Trac suffered from the shortcomings of a flat-file system: each potential job record held fields to describe press options, papers, binding options, and other parameters, and up to 4 quantities that the customer might be considering contracting for. Each of the 4 quantities had a corresponding field for price (which originally came in off a Lotus 123 spreadsheet; at some point shortly after the Split, this data came in from the FileMaker files that replaced it), a field for markup, and a calculated result field for final price. Then, for the sales order entry that would be sent on to the production people, there was a final set of fields for the quantity that the customer agreed to, its price, its markup, and its final total price. So with only four possible quantities this flat-file system was encumbered with a lot of redundant fields.
The Sales Trac file could not accomodate a customer wanting estimates quoted for a half-dozen different quantities. Worse, it could not accomodate customer requests for estimates for one paper type and also another paper type in order to compare prices. The salespeople were forced to either overwrite data after printing or duplicate the record to put in the other data-variations, and, either way, the proposal form (another screen referencing the same record in the same flat file) could not display all these variations.
So the Sales Trac file's functionality was split up into a relational design of 3 files: the parent Sales Trac file, within which each record was a potential job; the SalesTrac Products file, which would let an infinite number of product variations be entered, each of which in turn could generate an infinite number of quantities to work up estimated prices for in the estimator (the quantity fields were removed from Sales Trac altogether and housed in the Estimator itself, so entering a quantity would autogenerate an Estimate record for the estimating team to work on); and, finally, the STForms file, letting each Sales Trac potential-job record generate multiple proposals, each of which could reference all or any specified selection of products to generate a proposal or a final sales order entry.
The fundamental type of change -- essentially splitting up one file into multiple files and putting some of the components of the original file in one and some in anther and so on -- was very much akin to the above-mentioned splitting up of the JobTicket file on the NY side, but because of the simplicity of the original New Jersey fileset there were far fewer external references and scripted routines that had to be rewritten to accomodate the changes, so, overall, the change was a simpler one.