Moving words in Fields (Replace)

---

We recently received a request for advice on how to move leading words in a field, to the end of the field, in all entries in the catalogue.

---

A school using Athenaeum recently asked if it was possible for Athenaeum to hide leading "indefinite articles" from the pop-up list attached to the Series field in the catalogue.

It is possible, because this pop-up list is one of the lists that is defined by the contents of the field it is attached to (the Series field). That means, if we found all entries in the catalogue where the Series field started with "a", "an" or "the" and then removed or moved that word, then the pop-up list will automatically change.

For some more information about pop-up lists, see Athenaeum Note 1.

Rather than doing each entry one at a time, the Replace command may be used to change a group of entries in one action.

For some more information about the Replace Command, see Athenaeum Note 9.

Here's how.

Replacing to move indefinite articles

Backup up your Data

The replace command is extremely powerful and can update entire databases in one fell swoop. Similarly, if you get it wrong, it can ruin entire databases the same way.

Find the data you wish to change

Open Athenaeum with your administrator password and then go to the catalogue and then to the detail layout. To speed up the replace function, we only need to work on records that contain indefinite articles ("a", "an" or "the), so we can limit the function by first finding these words in the series field.

Enter Find mode (Control-F on Windows, Command or Apple - F on Macintosh) and place the cursor in series field. Press the escape key once to hide the pop-up list.

Type "=the" (without the quotes) and then create a new request by pressing Control-N (on Windows) or Command or Apple - N (on Macintosh).

Now type "=an" in the series field (you will probably have to hide the pop-up list again.

Create a new request again and this time type "=a" (without the quotes).

Perform the find by pressing the "Enter" key. If there are no records that match this criteria, then you will get a message to that effect, and there is therefore no need to continue with this exercise. Otherwise, you will now be looking at just those records containing indefinite articles in the series field.

Update the data with the replace function

You are now ready to use the replace function.

With the cursor in the series field, choose "Replace..." from the Mode menu.

The replace dialog is displayed. Note that the first option is selected.

Click the third option "Replace with Calculated Result".

The Specify Calculation Dialog is displayed.

This is where all of the work is done. Enter the following formula as it is below:

If (
  (LeftWords (cat_series, 1) = "The")
  or (LeftWords (cat_series, 1) = "A")
  or (LeftWords (cat_series, 1) = "An"),
    RightWords (cat_series, WordCount (cat_series) - 1) & ", " " & LeftWords (cat_series, 1),
    cat_series)

(you should be able to copy and paste this directly from your web browser and paste directly into the Replace Dialog.)

You may format text within the calculation dialog by putting returns at the end of lines and indenting lines with spaces. This can make it easier to read and check your formulae. This is what the formula might look like formatted:

The formula looks more complex than it really is. Basically, the If statement says "if the field named cat_series starts with "the", "an" or "a" then take all of the words of the field except the first one, put a comma and a space after it and then move the first word to the end. If it is not, then leave it as is.

Click the OK button and then the replace button. After a few moments, the contents of the field will be re-ordered, just as desired.

If you make a mistake, then you always have your backup to go back to.

This technique can be applied in a number of ways. For example, you may wish to perform the above operation to the title field (though Athenaeum Pro and Athenaeum Express ignore indefinite articles when sorting, anyway).

If you are having problems with this or other commands, please don't hesitate to contact SumWare Consulting at library@sumware.co.nz for help.

New Zealand users of Athenaeum can get pay as you go telephone support by dialling (0900) LIBRARY - i.e. (0900) 54 272 - during normal business hours.