«

»

Mass-changing customer records

Mass changing customer records is one of the most powerful, and dangerous, functions in DDMS. Here’s an example of a simple mass-change.

Mass-changing records in DDMS is a very powerful tool. There are numerous occasions when you need to change a lot of records – when tax rates are updated, or when heavily-departmentalized companies move or change names, for example. The ability to change hundreds or thousands of records at once is enormously useful on many occasions.

Incidental to something else I was working on, I discovered not all my customers in Oregon have the same Region and District set. My dealership uses Region for tax districts and typically uses District as the tax rate. That is, for a city with a tax rate of 8.50%, I make the district 8.50. Keeps things simple and prevents a problem if you use generic tax district names and change the rates themselves. Namely, OE History doesn’t write out the tax amount as part of history, just the district. It looks up the tax district at the point the invoice is queried and returns the current rate in place now. Changing the district itself set on the customer record avoids this issue. For my out-of-state customers, I set Taxable as ‘N’ and make the District ‘OS’.

One thing I’ll mention at the very start: until you have a very good handle on mass-changes, and even afterwards, I recommend you always back up the files you’ll be changing. Mass-change is a no-takebacks sort of process. Screw it up without backups, and you’re looking at potentially a very tedious time finding and fixing the mistakes. The easy way to back files up is to simply copy them out of their folders on teh /ddms share, and stash the copies somewhere safe. I keep a folder on the desktop of my DDMS server and put the backup files in subfolders by date. If I have a mass-change go sideways on me, it’s simply a matter of stopping TBL and pasting the backup copies back into the appropriate location on your DDMS server. On that note, it’s also a good idea to perform mass-changes during off-hours. In case it goes wrong, you won’t be affecting customers while you fix the issue.

Mass-changing customer records is done in Special Programs, DDMS’ + screen. Specifically, we’re using +W for this:

+W

In +W, we’ll be using a canned selector provided by DDMS, N-CUS1. To access it, it is Action Code B, then press Enter. At the Selector Name prompt, enter N-CUS1, which will then prompt to ask if we’ve chosen the right selector. We have, so we Enter past the prompt. This brings us to the Limit, Modify, or Delete? prompt:

limit, modify, or delete

First, you’ll be prompted for Location. All my customer setup is done in Location 1, and I shouldn’t have customer records in any other location, but I’ll leave this blank to be safe:

no limit on locations

Enter past the Display Detail on if Loc. Exist? prompt. Now we’re ready to set limits on the customers we’re changing. In my case, I need all customers with a ship-to in Oregon. To get to Ship-to, Space twice to see the rest of the available files:

space twice to see rest of files

We’re going to select on file 2, C-SHIPTO. We need to set a starting limit, and we need to limit on state, so Tab through the prompts until you reach that field. We enter ‘OR’ in State and the cursor will jump to Zip.

Start limit

Since this is the only starting limit we’re setting here, we can hit Enter until we reach the To selection, where we’ll again Tab until we reach State and again enter ‘OR’. At this point, the cursor returns to the file selection box. If we had additional limits to enter, we would specify the next file. Since this is the only limit we need to set, we Escape back to the Limit, Modify, or Delete? prompt.

Next we’ll set what we’re modifying. When we hit M to modify, the file selection returns to the top of the list. First, we need to modify C-DISC, which is where the District field resides. Notice this time the fields aren’t filled with question marks. This is also one of the trickier bits of mass-change. We first need to tell the program what fields we want to change. In this case, we want to change Dist, so we fill it with ‘1’s:

modify dist

When we hit Enter, it then jumps back to the first field. Now we set what we want set in the Dist field:

set dist

Notice that, because we didn’t fill the field with characters, what we set in the field jumped to the right, due to DDMS using right-aligned fields for nearly everything.

Now that we have our limits set, we Escape back to the Limit, Modify, or Delete? prompt. While we’re here, we’ll update our Region, as well, since those aren’t entirely consistent, either. Region resides in C-INFO, so we’ll set our limits the same way for this file. First fill Region with ‘1’s. Be careful when you do this, as it’s not always easy to tell how many characters a field will take, and you don’t want to spill over to the next field:

modify region

Notice the ‘OS’ I set in region didn’t move. Region is one of the few left-aligned fields.

set region

With that set, we hit Enter to return to the file selection prompt. We’re done setting our changes, so we’ll Escape back to the Limit, Modify, or Delete? prompt. Now the moment of truth: we’re ready to Execute. If you have a password set on this function, you’ll be prompted for the password:

enter password

We want to change all records in one shot, so we enter N at the Verify Changes record by record? prompt. Then we are ready to execute, so Y at the Are you ready to Execute? prompt:

execute change

The screen will rapidly scroll through all the customer records until it reaches the end, where it will show you how many customer records is has changed:

change completed

There we have it. In short order, we’ve updated 707 records. I started with Oregon having 14 different combinations of District and Region:

prior to change

Now I can jump onto my DDMS server and force an update on Customer:


update customer

Click for full size

Rerun the query, and we’ll see how it looks now:

post change

It seems we missed a couple. This happens occasionally, for whatever reason. Now that we can see we have ‘OS’ set on all these customers, we’ve got a couple options at this point: We could re-run the mass-change, limiting again on a ship-to state of Oregon and modifying the Region. Alternately, we could just find these two records and fix them manually. If you’ve got a proper ship-to query set up, it’s easy enough to find the few offenders and find out why they didn’t update and fix them as needed. Since we didn’t make any mistakes with our mass-change, it seems reasonable to assume re-running the mass-change won’t have any affect, and so we’ll go with the latter option. Looking into the two that didn’t update, one is a secondary ship-to to and the other had an empty C-INFO file, both of which are posts for another time.

This raises another point about mass-change: it’s a good idea to have an idea of how many records should be affected by the change you’re making. As we saw above, we only see the tail end of the records that we updated. Had we not run our first query with our counts, we wouldn’t have any idea how many records should have been changed by this process. Totaling up our counts before we made the change comes out to 710, so we know our counts are on as far as how many records we’ve changed.

Here we’ve had an example of a simple mass-change, limiting on one field and changing two. They are fairly easy to do, as long as you keep a few things in mind. Always back up your files and preferably only perform mass-changes during off-hours. Get some idea of how many records you’ll be changing, and make sure that you have thought through your limits and the changes you’ll be making. Go slowly and be careful and you will have soon mastered one of the most powerful tools DDMS has available.

As ever, I remain