Polytrope: CM Assistant

Getting your data into CM Assistant

There are two ways to get your existing data into CM Assistant: the data can be entered manually, or it can be imported into CM Assistant.

Entering the data manually -- in other words, using the keyboard to create all the necessary driver, route, location and drop records -- is a great way to learn your way around CM Assistant. And if you are not importing historical data, it will probably take you less time to enter the data manually than it will to figure out how to import it yourself. It takes less than a minute to create a new location record and type in its data.

But of course re-typing all your data does take time, and in the process, mistakes are likely to be introduced into your data. Importing your data into CM Assistant's files is more efficient and safer, provided it is done correctly.

Can Polytrope do the conversion?

Yes, we can do it and in fact, 100% of our existing customers opted to let us move their existing data into CM Assistant. We charge a small hourly fee for this service. Typically, this process takes us a couple of hours and will cost you one or two hundred dollars. This amount is billed to you after your files are delivered to you.

Your data is safe with us

Occasionally, a new customer expresses concern about sharing its data with anyone outside its organization. We understand these concerns. Rest assured that the data you send us remains completely confidential and is never shared with anyone. You should also understand that there are potential tech-support situations in which it may be necessary for you to send us your files for repair.

CM Assistant is written in FileMaker Pro. Unlike some other database management systems, FileMaker Pro stores programming resources and data in the same files. If you have a tech support issue that cannot be resolved easily over the phone or by email, it may be necessary for you to send us your files -- containing data -- in order for us to diagnose the problem.

Can you do the conversion yourself?

Yes, you can, in theory.

We estimate that the conversion will take you one or two full days if you do it yourself and if everything goes smoothly for you. You may have to make more than one attempt before you get it right. We should add that there is no free tech support for the conversion process.

If you think you would like to give it a try, by all means, read on. CM Assistant is a moderately complex relational system and in order to perform the conversion properly, it is imperative that you have a thorough understanding of its structure. The rest of this document presents a sketch of that structure.

Before you start

CM Assistant is a relational database application written in FileMaker Pro. The following discussion is aimed at users who are familiar with relational database design. In particular, you need to understand what key fields are (in FileMaker Pro parlance, "match" fields). Since FileMaker's implementation of relationality is somewhat idiosyncratic, it will help if you have some experience building relational databases using FileMaker Pro. It is also imperative that you be familiar with the different types of fields (text, number, date, time, container, calculation, summary and global) and five data types (text, number, date, time, container) supported by FileMaker Pro, since it will be important that you get text data into text fields, number data into number fields, and so on, and that you import your data strictly into the record-level fields (text, number, date, time and container) and not into global fields.

Most of our clients have been using flat-file systems that they developed for themselves. Most of our new clients were using Microsoft Excel before adopting CM Assistant. Some were using FileMaker Pro. A handful were using Access or something else. Because every system is different, the following discussion provides a rather general description of CM Assistant. It will be up to you to figure out what needs to be done to prepare your data for import into CM Assistant.

"Entities." A place for everything and everything in its place.

CM Assistant v3 consists of roughly a dozen files. (The exact number of files depends upon what add-ons you may purchase.) Each file stores just one type of data. The location file, for example, contains only information about locations: the name of the location, its address, its category (restaurant, street box, etc.), and contact information. It does NOT store any data relating to routes. That is stored in a separate route file. All in all, the elements of your circulation system's infrastructure are stored in half a dozen or so files, one each for zones, drivers, routes, locations, publications, and racks.

IDs (key values) and relationships

In nearly all (but not quite all) of CM Assistant's files, every record is automatically assigned a unique ID (primary key) value when it is created. These values are intended to be meaningless and more or less useless to you. They are there for CM Assistant's use only. But they are critically important, indeed, these id values hold CM Assistant together.

Id values in CM Assistant are all integers, with the exception of the publication id, which is a two or three-character alpha value. (If your source data is already in a relational system, you will need to confirm that all the key field values are integers and if not, convert them before you import the data into CM Assistant)

Here is a rough and much simplified entity-relationship (ER) diagram showing CM Assistant's main structural relationships:

Note that there are many more relationships involved in CM Assistant than this diagram indicates, but most of them are managed automatically by CM Assistant and you do not need to worry about them during your conversion process.

In the ER diagram above, the triple bars (|||) indicate which file in the relationship is the "many" (child) file. All the relationships indicated in the diagram involve matches on id fields, so for example, the relationship between the driver file and the route file is triggered when the "id" field in the driver file matches the "driver id" field in the route file, and the relationship between the location and the prep file is triggered with the "id" field in the location file matches the "location id" field in the prep file.

Note. The relationships are all based on integer id values that are stored in FileMaker number fields, with the following two important exceptions. 1. The publication id is a two or three-letter value, normallly an abbreviation for the publication. If you have some experience with relational database design, you may be aware that there are some problems involved in using meaningful primary keys. We decided that the advantages outweighed the risks. 2. There is a delivery prep id field in the drop file, but as of December 4, 2001, the drop file is related to the prep file on a concatenated (compound) key in a text field. This key combines the pub id and the location id; the concatenation of the two values is scripted when a new drop assignment is created. There are technical reasons for this choice that are not important here. What this means is that, if you do your own conversion, you will need to import data into four key fields in the drop file: pub id, loc id, prep id and route delivery id.

Quick overview of the conversion process

Most of our clients were using flat-file systems before they converted to CM Assistant. The process of conversion involves breaking your flat-file system apart and putting the different types of data into the right files. The trick is to do this without losing the connections between the types of data. How do you maintain the connections? First, you'll need to understand how these connections work in CM Assistant, and then you'll need to create all of the id values needed to link the elements of your data together.

Here's a simple example of what's involved. If your Excel spreadsheet contains a different worksheet for each route, and each route worksheet contains something like forty or fifty or sixty rows (each row representing a drop), what you'll need to do (among other things) is something like the following.

1. Give each route a unique integer id value. You will then need to create just one record for each route in the CM Assistant route file, and you'll need to make sure it has the idea you gave it.

2. Find an empty column in each worksheet and use the fill down command in Excel to put that route id value into the same cell of every row.

3. You'll also need to give every row a unique integer id value to represent individual locations. If your existing system handles restocks, you will need to make sure that restock deliveries are associated with the correct location id. In other words, if the Kroger Supermarket on MLK Blvd. has location id 1022, you'll need to make sure that every drop to that location (both the primary, which may be in one worksheet, and the restock, which may be in another) has the same location id.

4. You'll need to associate every single row of every route worksheet with a publication, using a publication id. If your existing system is tracking more than one publication already, you'll need to make sure that your current method of identifying the publication is compatible with the type of data CM Assistant expects, and modify it if it's not.

5. You'll need to give each of your drivers a unique id as well. That value will get imported into the route file in CM Assistant as well as into the driver file.

6. If your current system groups routes into zones, you'll need to create zone ids. Import each route's zone id value into the route file in CM Assistant, as well as into the zone file.

Circulation data, part 1: the delivery prep file

Neither the location nor the route file actually store any information about how many papers get dropped and where. This information is stored in two files that are central to CM Assistant: the drop file and the delivery prep file. We'll deal with the prep file first.

Each record in the delivery prep (or simply "prep") file represents a target drop amount of n copies of publication p to location x, using rack (or rack/pocket) r. The prep file does not store the name of the location, the route, the publication or the rack. It simply stores the ID of each of these elements of your circulation system (what database programmers call an "entity"). So what is actually stored in a record in the prep file looks something like this:

id = 11030 [this is the primary key value for this prep record]
loc id = 3078
pub id = itw [pub ids are two or three-letter alpha values]
rack id = 47
rack pocket id = 1
target drop amount = 50

Now the prep file is where you spend most of your time working in CM Assistant, and we don't expect loc id 3078 to be meaningful to you, indeed, we don't even expect you to notice the loc id value. While you are looking at the prep file, what you will see is that the current record is for Will's Pretty Quick Corner Store at 83752 Main Street. But remember, what you're seeing is really data borrowed from the location file. The prep file is related to the location file on the loc id, in other words, when the loc id field in the prep file matches the id field in the loc file, the relationship which is programmed into CM Assistant is triggered.

If this isn't making any sense to you at all, then we suggest you stop reading and consider letting Polytrope do your conversion. This is very basic relational stuff.

Circulation data, part 2: the drop file

You may have noticed that the prep file does not contain route information. Moreover, the "target drop" amount in the prep file is not the ACTUAL drop amount. The prep file represents your general intention or plan: each week, Will's Pretty Quick Corner Store should get 50 copies of Ithaca This Week. The actual drop order is stored in a separate file, called the "drop" file.

Why is the drop file necessary? For a couple of reasons.

First, any given location may get more than one drop a week, that is, it may get restocked. So prep record id 11030 may be the "parent" of two drop records, one representing a primary drop of 30 papers, and a second representing a restock drop amount of 20 papers.

Second, by putting the TARGET drop amount (an abstraction) into the prep file and the ACTUAL drop amount for this week into a separate record in the drop file, CM Assistant is able to remember the target drop amount even when you temporarily reduce or increase the actual drop amount (for example, because a university bookstore location closes during spring break).

Publications, Dates and Issues

We mentioned above that publication data is stored in its own file. Publication data includes the full name of the publication, a two or three-character abbreviation that is used as the publication's id, and a number of other important default values, such as publication's normal delivery period (weekly, monthly, etc.), the average number of pages in each issue, the average bundle size, and so on. If you use CM Assistant to distribute only one publication, then the pub file will contain only one record.

Each time you get ready for a new primary delivery cycle, you will create a new record in the date file. The date file contains nothing but an id (primary key) and a date. Most of our users create one new date record per week.

The issue file is a child of both the publication and the date files. In addition to a pub id and a date id, it contains specific information about that issue (page length, bundle size, cover story, cover art, etc.). This information is important to CM Assistant's auto-adjust feature. But if the auto-adjust tool does not find issue-specific data, it will use the default data stored in the pub file instead.

You will not need to import issue and date information into CM Assistant unless you wish to import your existing historical data. Some of our new licensees want to, others don't. (Most of our new licensees do not actually have historical data to import, at least not outside the file folders full of printed route sheets.) If you are content to start with a sort of clean slate, then you needn't worry about the issue and date file.

But if you do wish to import historical data into CM Assistant, then you need to be very careful to get issue records related properly to the records in the date file (date id is the match field here).

History

The history file contains data about deliveries made in the past. The crucial fields are these:

delivery prep id
delivery route id
primary route id [different from delivery route for restocks]
delivery date id
publication id
drop amount
return amount

In addition, the history file stores internal route audit data (very few of our clients bring this data to CM Assistant initially).

Massaging data prior to import

We always presume that new users are somewhat familiar with CM Assistant already from our demo. You may have noticed while you worked with the demo, or you may notice as you work on the conversion, that CM Assistant's fields don't always contain exactly the same data as the cells of your spreadsheet or the fields of your existing database. If you encounter a problem of this sort (and you probably will), you'll want to manipulate the data prior to importing it into CM Assistant.

Here are a few examples of significant problems you should try to identify and solve in advance of the import.

CM Assistant's phone number fields contain both the area code and the phone number. If your existing system stores the area code in a separate field, you'll want to concatenate area code and phone number before importing into CM Assistant.
CM Assistant v3 phone number fields are designed to hold more than one phone number, if necessary, in other words, you won't find separate fields for "main phone," "fax," etc. If your source data uses several separate fields for different phone numbers, you'll need to concatenate these fields before importing the data into CM Assistant.
In the driver file, CM Assistant distinguishes between driver last name and driver first name. If your source data stores the driver's name in a single field, and if you have both first and last name data on your drivers, you should break the data in your old driver name field apart prior to the import.
CM Assistant distinguishes between racks and pockets. Information about a multi-pocket rack gets stored in a single record, but there is a repeating field that allows you to enter specific information about the individual pockets (e.g. "top left"). If your existing data contains rack information, this may be a problem for you. Importing pocket data into a FileMaker repeating field is very awkward. You may end up having to enter pocket or rack data manually. But you should think ahead so that you don't lose rack assignment links.

The following problems are not crucial, but we thought we would mention them anyway, since it may be easier to solve them prior to import.

CM Assistant allows and indeed expects you to give your zones and routes descriptive names. Some of our existing clients come to CM Assistant with routes whose names are non-descriptive codes, like "A3." You are free to call your routes and zones whatever you like, but if you would like to take advantage of CM Assistant's flexibility in this respect, and change "A3" to "University/Southwest" or something more readily descriptive, you should consider doing this before the import.
CM Assistant displays certain important data in each file in boldface. We have found that many of our clients used CAPS originally for emphasis. If you would like to convert data in CAPS to Proper Case, you should consider doing it prior to the import. (Note: In some cases, it may be possible to use the FileMaker Pro "replace" command to do this after the import.)

How do you do the import?

Once you've prepared your data by breaking everything apart and creating the all the necessary key fields, you are ready to perform the import. This is done one file at a time. If you start with a single large source file (as is usually the case), you will start by importing just the zone data into the zone file, then you'll import driver data into the driver file, then route data into the route file, and so on.

The FileMaker import command is not available in the File menu. To get to it, pull down in the CM Assistant menu to "Special," to go to the Special layout in each file. This is a sort of utility layout that provides buttons that run scripts you will only need to use occasionally. Every file in CM Assistant has an "Import" button on this layout. Clicking on this button simply invokes the FileMaker "import..." command.

You should be familiar with the way that the import definition dialogs work; read the pertinent pages in the FileMaker Pro users guide if you need to. The most important fields in each file have CAPITALIZED field names, and they are usually found at the top of the list of fields (on the right side of the dialog). So "NAME" in the location file is the name of the location, while "DROP AMT" in the history file stores the drop amount for a given delivery date, pub and location. A file's primary key field is simply named "ID," while foreign key fields also include the name of the related file; so "id rou deliv" in the history file is a foreign key used to relate history records to their delivery routes. In every file, the majority of the fields are for CM Assistant's use rather than for your use. In the date file, for example, there are nearly 50 fields, but you only need to import data into two of them (DATE DELIV and ID).

Special problem: Multiple source files or multiple Excel worksheets

If you have been using Microsoft Excel and have a separate worksheet for each route, you'll need to combine all your route data into one big worksheet before you do the import.

Special problem: Source files are already in a relational database

Converting your data to CM Assistant from an existing relational database system presents unique problems. It is almost certain that your old system's structure will resemble CM Assistant's structure in some ways and not resemble it in others. Contact Polytrope Solutions directly for more information.

polytrope cm assistant/support: data conversion