CMAssistant 8.1: Exporting location info for a particular route

This post assumes you're using CMAssistant 8.1. Not too many of our licensees have upgraded to 8.1 yet, so I'll make a note where you might need to make adjustments for versions of CMAssistant going back to 6.5 or so.

So, here's the problem. You want a list simply of the location names and addresses for a route in your current delivery system. Here's how you do it.

1. Find the route's current deliveries

I am assuming you want to get location info for a current route. There are several ways to do this.

Perhaps the easiest way to do it is to start in ROUTES. Find the route you want by name and go to form view. In the Run Dates tab of the details box on the right, click on the go-to-related-records (">") button in the Delivs column for the first row. Note the delivery date. That ought to be the most current delivery set for that route.

The run dates tab of the details box in ROUTES (CMAssistant 8.1).

This button (circled in the screenshot above) will jump you over to DELIVS and display just those deliveries. 

A second, alternative way to find the same records is to start on the home screen, click the "Find Current Issue Deliveries" button, then, in DELIVS, scroll down until you find a delivery for the route you're interested in. Click on any delivery in that route to select it. Then, at the top of the window, click on the find-same or equals-sign ("=") button next to the route name in the header. This is what it looks like in CMAssistant 8.1:

Portion of the DELIVS screen in CMAssistant 8.1.

In earlier versions of CMAssistant going back to somewhere around 6.5, there is a find-same ("=") button next to every single route name in the list. There were also find-same buttons next to every single date, and every single drop name. The result was that the screen was rather busy with equals signs. In 8.1 the find-same buttons are displayed just once, at the top, as shown above.

There is, of course, a third and final way to find all the deliveries for a route on a given delivery date. That is to go directly to DELIVS, click Find, and define a find with these criteria:

• Route name = the name of the route you're looking for
• Delivery date = the delivery date you're interested in

Both of the two methods described earlier should be a little faster, but you can use whatever method makes sense to you. The one that makes sense to you is usually the one you'll remember.

2. Jump from DELIVS to LOCS

Once you've found the deliveries you want — say, all 67 deliveries in the Downtown East route — you'll want to jump from those deliveries to their related location and address info. To do that, go to the DELIVS menu (top of screen), and pull down to Go to Related Loc Records for N Found Deliveries. "N" of course will actually be the number of deliveries found. In the screenshot above, it's "67". This will jump you from DELIVS over to LOCS and display the location records for those deliveries.

NOTE 1: The count of found deliveries displayed in the menu item will occasionally be incorrect. This is a benign screen refresh bug. Check the standard CMAssistant display for the count of found records. If that sounds right, ignore the number in the menu item.

NOTE 2: Remember, in CMAssistant pretty much since forever, a location record basically just stores the location name and address. A drop record is a location that has been linked to a publication; the drop record stores info about the rack, the first and final delivery dates plus pause and resume dates, and the delivery memo for the route sheet. If you wanted to export drop info rather than loc info, you'd use the command Go to Related Drop Records for N Found Deliveries.

NOTE 3: You could actually export the location info directly from DELIVS, without jumping over to the LOCS table. But you're going to have to select fields to export in the export field selection dialog, and it's much easier to select fields if those fields belong to the table you're currently looking at. 

3. Exporting location names and addresses

We're now in the home stretch. This is basically a standard CMAssistant export process.

You should be in LOCS, looking at the locations that belong to the route you started with. Now go to File > Export Records... to start the export.

In the first dialog you are shown, you will select three things: 

1. a location for your export file (on your hard disk, in your Dropbox account, on a remote server, wherever you like); 
2. a file format for the export file (I recommend .xlsx, but tab-delimited and comma-delimited work well, too, and of course so does .fmp12); 
3. and a name for the export file (whatever you want to call it).
4. Optionally you may wish to put a check in the "open file" checkbox. This will cause the export file to be opened (say, in Excel or Numbers) automatically when the export is complete.

The save-file dialog that appears as the first step in the export process. It's a good idea to select the options in the order shown by the red numbers.

When you've completed this dialog, click "Save".

NOTE: If you are running FileMaker Pro in a recent version of Mac OS X, and you see a choice between .xlsx and .xls, be sure to select Excel Workbooks (.xlsx).  The older .xls option probably won't work.

If you're saving an Excel workbooks file, you'll next see an "Excel options" dialog. I normally just click Continue to get through this.

Finally, you find yourself looking at the field selection dialog. CMAssistant 8.x has its own idea of a likely selection of fields, given the table you're exporting in, and those fields are already displayed in the selection list on the right. If you're exporting from LOCS, the location name, street address and city, state and zip code are already in the field list on the right. If you want other fields, you need to find them in the listing on the left, then double-click them to move them to the list on the right. Note that you can change the order of the fields in the list on the right; doing so will change the order of the columns in your output file.

Here's the default field selection in LOCS in CMAssistant 8.1:

Specify Field Order for Export dialog in LOCS, in CMAssistant 8.1.

NOTE 1: The field "xtstreetaddress" is a calc field. In CMAssistant at least through version 8.1, calc fields are usually prefixed with the letter "x" and then a second letter indicating the data type (xt = calc with text result, xn = calc with number result). You probably want this calc field because it has combined the street number, street name and suite number (if there is one). If you wanted the cross streets, too, you could double-click "Cross Streets" in the list on the left to move it into the list on the right.

NOTE 2: If you had jumped from DELIVS over to DROPS instead of LOCS, the fields displayed in the export field selection dialog would be different.

When you finally have all the fields you want in the list on the right, click the "Export" button to export the data. And you're done.

4. Why doesn't CMAssistant just give me a button to do this?

There are a handful of "canned" exports built into CMAssistant. You click a button or pull down to an item in a menu, and a few seconds later, there's an output file on your desktop. CMAssistant only does this when (a) the export is so simple that there really are no options for you to choose from and/or (b) when the export is so complicated that I don't expect my users to be able to figure it out.

But it's much better for you to roll your own exports, and with just a little practice it's easy. The entire process above takes an experienced user less than thirty seconds to complete — from start to finish. And when you know how to do this, you know how to start modifying your exports, so you can do anything you can think of. There's no way that CMAssistant could provide a button with a canned export for the nearly infinite number of possibilities. 

If it's in your data in CMAssistant, you can get to it. If you have trouble, contact us and we'll help!