The Basic Meeting List Toolbox

PROTIP: A Quick Geeky Way to Update NAWS World IDs

This entry is part 4 of 4 in the series Root Server Admin

ABSTRACT

If you have used the NAWS Export Functionality of the BMLT to send synchronization dumps to NAWS, you have received a spreadsheet in reply, with a series of meetings that have revised or newly-assigned Committee Codes (what we call “World ID” in the BMLT).

These are fairly long sequences of numbers, prefixed by one or two letters, like so:

  • Regions are listed as “RGXXX“, with “XXX” being 3 digits (with leading zeroes). For example: “RG001”, “RG123”.
  • Areas are listed as “ARXXXXX“, with “XXXXX” being 5 digits (also with leading zeroes). For example: “AR00001”, “AR00123”, “AR12345”.
  • Groups are listed as “GXXXXXXX“, with “XXXXXXX” being 7, leading-zero-padded digits. For example: “G0000001”, “G0012345”, “G1234567”.

These need to be assigned back to your BMLT database in order to ensure continued synchronization with NAWS.

It should be noted that NAWS tracks GROUPS, not MEETINGS.

Groups are Service entities. They are the driving force behind meetings, and often a single Group can have multiple meetings (like clubhouses, or lunchtime meetings).

In these cases, you may apply the same World ID (NAWS Committee Code) to multiple meetings.

IT IS EXTREMELY IMPORTANT TO DO THIS! DON’T IGNORE THE SPREADSHEET RETURNED TO YOU BY NAWS!

It is very much worth it to keep NAWS in sync with your database, so this operation is important.

Thankfully, NAWS has agreed to do a certain thing that makes this process fairly easy to automate.

This post will spell out a somewhat “high geek factor” way of doing it. I will be working on a tool to abstract the complexity, but that will be a little while in coming.

THE FIRST STEP IS A DOOZY

The first time that you synchronize a new BMLT database with NAWS, your Groups will not be likely to have accurate committee codes, so the entire database will probably need to have NAWS assign Committee Codes. This can be a fairly sizable operation.

After that, if you are regular about updating NAWS, the operation will be much smaller, and probably won’t require this tip.

THIS REQUIRES DIRECT DATABASE ACCESS

This operation is not done via the main login panel. It is done through your server administrator control panel.

The examples shown here will use phpMyAdmin, a very common MySQL administration tool. If you have a different database administration tool, this will still be a useful post, but you’ll need to translate it to your tool.

The goal of this exercise is to produce a set of SQL commands that will be applied to your database to quickly update the meetings.

Several documents will be attached to this post in order to illustrate an example (from the New York database).

THE PROCESS

We’ll go through this operation step by step.

Step One: Generate Your NAWS Dump

This is where you create the CSV file to be sent to NAWS.

Here are the instructions to do this.

Step Two: Send This NAWS Dump to NAWS

You do this by emailing the dump as a CSV file in the format returned by Step One, to the folks at NAWS.

Step Three: Get A Spreadsheet In Response

You will receive an Excel spreadsheet, similar to this one (Downloads an Excel spreadsheet).

This will be in basically the same format as the one you sent them, but will only contain rows for meetings with new Group Committee Codes, or meetings that have had their Committee Codes updated (or fixed).

This data is considered “the gold standard.” You want to completely replace any data you have in the server with this data.

Step Four: Filter To Only Two Columns

This spreadsheet will have the data for every field in the meetings. However, you are only interested in two of the columns: “Committee” and “bmlt_id”.

Remove all the columns except for these two. An example from the previous spreadsheet is here.

Step Five: Export This to A Simple Text File

You should exprt this spreadsheet to a simple text file, in either CSV (Comma-Separated Values) or TSV (Tab-Separated Values) format. An example TSV file is here.

Step Six: Do A Search And Replace With SQL Code

We will now generate a series of discrete SQL commands that will update one of the tables in the BMLT database with the new data. The template SQL that we will use will look like this:

UPDATE `na_comdef_meetings_main` SET `worldid_mixed`='\1' WHERE `id_bigint`='\2';

Where \1 will represent the first column (“Committee”), and \2 will represent the second column (“bmlt_id”).

The \t represents a tab character (Assuming this is a TSV, or Tab-Separated Values file). This could also be a “,” (comma), if it is a CSV (Comma-Separated Values) file.

The “na_” may not be there in your case, if you chose a different table prefix when you created the database.

I use an app called BBEdit to do most of my text work, and here is what the operation looks like for me:

Remember that the file has a header column, so the top line command will be bogus.

The result of this operation is available here. This is a series of direct SQL commands. Copy this into your clipboard.

Step Seven: Log Into Your phpMyAdmin Page

Next, you need to log into your site control panel, and go to phpMyAdmin. In there, select the BMLT database, then choose the “na_comdef_meetings_main” table (If “na_” is your tabe prefix), like so:

04-SQLQueries

Step Eight: Open the SQL Editor for That Table

Open the “SQL” tab for the table, like so:

Step Nine: Replace the Default SQL with Your SQL

You’ll be replacing the SQL in that edit text box. select the SQL, and paste in your SQL, like so:

Hit “GO”, and Bjorn Stronginthearm’s your uncle.