Happy #MapInfoMonday!
In today's article, I wanted to show how you can use the SQL Window to create a small script to update a few columns in the current selection.
Often you may have to work your way through your dataset, check some values, and then update some columns so you can see how far you have come. You can change the value in the status column via the Info window, or you can run an Update statement. Often users are using the
Update Column dialog to execute the Update statement for a column.
The
Update Column dialog works fine if you only need to update one column with the same value each time. Imagine you work your way through many records and need to assign the value
GOOD
,
AVERAGE
, or
POOR
to the
Status
column for each. In this case, there is a big chance that you will have to change the value in the
Update Column dialog every time.
And then add that you also need to update the
CheckedDate
column with the date when you changed the value. That doesn't make things easier, eh?
But there is another way. You can update your table via the
SQL Window. Let me show you some of the benefits.
As an example, I have a table with Cell Towers that I need to inspect and update. I have selected a single cell tower from the map below. You can also have a browser window of the cell tower table and select from this and so easily run through the list.
To use the
SQL Window, I need to open it from the
SQL dropdown on the
Table,
Map, or
Spatial tab.
Typically, I have the
SQL Window open on the right side of the MapInfo Pro window. You can also have it floating if you prefer. The window stays on the screen when you execute the statement. This makes it easy to use the statement over and over again as you don't have to open a dialog all the time.
From the
Create New Script list at the upper right side of the
SQL Window, I can create a new script from a template. I pick
Update Statement. Notice how the text in the
Edit field changes.
I'm looking to update the current
Selection
. So make sure that you select a record from the table you want to update. This makes it possible for MapInfo Pro to show you the column names.
Here's the first basic statement to update the column
STATUS
with the value
GOOD
.
Update Selection
Set STATUS = "GOOD"
I can extend this to update multiple columns by separating the individual column assignments by a comma. I also want to update the column
CheckedDate
with the current date. I get the current date from the MapBasic function called
CurDate()
.
Update Selection
Set STATUS = "GOOD", CheckedDate = CurDate()
Now my small script will update two columns in the current selection. It looks like this in the SQL Window. Notice that I also unchecked the Browse Result at the bottom of the SQL Window as I don't want to see the result in a browser.
I can click the
Rename Script button in the
Scripts List to rename the current script. This makes it easier for me to later find it again. Let me rename it to
Update Status GOOD. I hit the Enter key when I'm done typing the new name.
I now hit the
Save Script button and the
Mark as Favorite button in the
Scripts List to save the script to a file and to make it a favorite script that I often use. Notice how the
Favorite star gets yellow when you click it.
I can now use the script from the
SQL Window simply by clicking on the
Run button. This will update the selected record(s).
As I marked my script as a favorite script, I can also find and run it from the
SQL dropdown. When I click on the script in the dropdown, it will be executed. This means that I can run the
Update
statement without seeing any dialog.
Using Variables
As I mentioned at the beginning, I have to update the records with three different values for the STATUS. The solution above updates all with the value
GOOD
. So I could now create additional two: one for
AVERAGE
and one for
POOR
. This could work as long as I only have three options but what if I had 5, 10, or 50?
Let's look at another approach where the value we use, can be selected by the user.
From the
Variables list, select
Edit Variables. This is often the only option you see in the list. When you click this option, the dialog
Edit Variables will appear.
Variables allow you to add a level of flexibility to your scripts. They allow you to prompt the user for input. From the
Edit Variables dialog, you can control the questions you ask the user.
The dialog starts by presenting you with a variable named
v1 of type
String. Let's change this to match our needs.
I start by changing the name of the variable to match what it does. In my example, it holds the Status value to use. I name it
sStatus
. The s prefix tells me that it is of type
String
.
For the
Variable Type, I select
List from the
Type List in the
Edit Variables dialog. I could also have stuck to the
String type. But by using a
List, I can control the values that the user can choose from. As you can see below, there is a long list of possible variable types to choose from.
For
Prompt I enter
Status
and as the
Default Value I enter
selection.STATUS
. This will read the value from the selection and use this as the default value selected.
And finally, in the field
Values I enter the values that I want the user to select from. Separate the values with semicolons (;) and make sure the entire string is quoted. I wrote
"GOOD;AVERAGE;POOR"
.
Now hit the
OK button to save this variable with your script.
Back in the
SQL Window, I can now use my variable. I can either select it from the
Variables dropdown.
Or if I remember the name, I can just type it in. Notice that I have replaced the hardcoded status with my variable name
sStatus
. Make sure to save the changes to your script. The small Asterix (*) next to the script name tells you that you have unsaved changes.
When I run the script now, it will prompt me to select the status for the selected records.
I have only used one variable in my example. If I had used more, the dialog shown would show more controls allowing you to specify the value for the other variables, too.
If you have selected more than one record or if the current value for the selected record isn't found in the list, the dialog will warn you to select a valid value from the list. You will not be able to continue until you meet the requirements for all the controls.
I hope this has given you some ideas on how to improve the way you update columns, especially when you are updating multiple columns with variable values many times.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------