Friday, August 11. 2017

Everyone knows how to put things in alphabetical order; it’s one of the first things we learn in grade school. Well, just because it’s easy doesn’t mean we want to do it ourselves sometimes! 13-F’s Information Table, for example, would be pretty time-consuming to have to alphabetize manually. Using the SortTable function, we can sort any table quickly and easily. While there’s no SEC requirement to do so, it makes it a lot easier for readers to view if the information table is alphabetized by Issuer Name. Legato provides powerful tools to make this mundane task simple and automatic.

The script this week is an excerpt from code that will be deployed in GoFiler, and adds two functions to the Tools menu of the 13-F view: “Alphabetize List ASC”, and “Alphabetize List DESC”. They alphabetize the list in ascending and descending order, respectively.

Before we get into the script, a quick word on code re-use. We will have two very, very similar functions here, to sort a list alphabetically in ascending order and to sort a list in descending order. It would be very easy to simply write a function that does one of the two tasks and then write a second function by copying the first one and modifying it slightly. The problem there is that we’re restating a lot of the same code over again because most of what we create is going to be identical. Whenever possible, you want to re-use code, so if we have to go back and change something later, we only need to change it in one place. To re-use the code here, instead of writing two run functions, we have three. The generic run function here takes a third parameter, the string ord. The other two run functions, run_sort_asc and run_sort_desc, simply call the generic run function and pass an appropriate value for ord to it. This way it knows the desired sort order.

We begin with some function declarations. We declare the generic run function, as well as our two new run functions, run_sort_asc and run_sort_desc. In addition, we have two setup functions: setup_sort_asc and setup_sort_desc. Again, these functions perform essentially the same operation but for the “Alphabetize List ASC” and “Alphabetize List DESC” menu options, respectively. Our main function simply calls the generic setup function, which then calls both setup_sort_asc and setup_sort_desc in succession. Let’s take a look at the setup_sort_asc function.

This function performs many of the same operations we’ve covered in previous blogs. We set up a string array entitled lookup to hold information about the menu item we are trying to add. We then check to see if the menu item has already been added or if the ID is otherwise in use with the MenuFindFunctionID SDK function. If there is an error, we exit. If not, we set up the menu item and the hook with the MenuAddFunction and the MenuSetHook functions respectively. Since this is the ascending menu item, we hook the item into our run_sort_asc function.

Our setup_sort_desc function operates in nearly the exact same way as its ascending counterpart, save for adding a alphabetize descending menu item and hooking that item into the run_sort_desc function. Let’s take a look at that function now:

All this function does is pass our “desc” string to the generic run function as we mentioned above. The ascending version passes in the “ord” string instead. Now, with our two menu items set up and properly hooked into ascending and descending operations, let’s examine the workhorse of this script: the run function.

As we usually do, if the mode passed to the run function isn’t preprocess, we exit. After that we begin building a message to the user indicating that the Information Table is about to be sorted. We use the OkCancelBox function to present the user with a choice to continue. As long as the result is not IDCANCEL (the user pressing the Cancel button), we continue script execution by beginning to collect the data we need to alphabetize.

To retrieve the Information Table, we first need a handle to the active edit window, which we can retrieve with the GetActiveEditWindow function. It’s important to check if this handle (and all handles) is valid, and the IsError and GetLastError functions can indicate if there was an error and what the error was. If there was, we report it to the user and exit. With the handle to the edit window, we can then retrieve the Data View with the DataViewGetObject function. Again, we need to check if the handle is valid and report any issues.

rx = 0; /* Clear Row */
items = DataViewGetListRow(hDataView, "InfoTable", rx); /* Get first row of items */
cols = DataViewGetListColumns(hDataView, "InfoTable"); /* Get the number of cols in the gfp */
rows = DataViewGetListRows(hDataView, "InfoTable"); /* Get the number of rows in the gfp */

Now that we have the Data View, we can begin processing the information. We set our row counter, rx, to zero. We also retrieve the first row with the DataViewGetListRow function using the hDataView handle, the “InfoTable” parameter, and the position (zero). Finally, we get the number of columns (cols) and the number of rows (rows) with the DataViewGetListColumns and the DataViewGetListRows functions, respectively.

After, we create a log with the LogCreate function and clear it with the LogClearProperties function. Now we test to ensure our Information Table has twelve columns, which it must have to be correct and compliant for Form 13-F. If there are twelve columns, we proceed with our sort. If not, we alert the user of the issue and no sorting is performed.

We sort so long as the total number of rows is larger than the current row number using a while loop. First we add a message to the log using the AddMessage function to indicate which row we are currently sorting. We copy this row to our temporary table string array and increment our counter. We then retrieve the next row with the DataViewGetListRow function, store it in items, and iterate through the loop.

Once all the items are stored in the temporary table, we can sort them. We do so by setting the sort_mode parameter to the SORT_ASCENDING flag by default. If the ord parameter that has been passed into the function is “desc”, we flip the parameter to the SORT_DESCENDING value. After that, sorting is as easy as calling the SortTable function. We combine the SORT_ALPHA flag with our sortmode value. Once we do that, the DataViewSetList function sets the Data View to the new, sorted table.

Once we’re finished, we use the MessageBox function to alert the user, and we display the log to show how the sorting process went. After that, we return with no error.

So, just like that, we can easily sort the Information Table on form 13-F to be alphabetized in either ascending or descending order. Legato makes tasks like this simple to implement with minimal coding, and to the user, it’s as easy as clicking a menu item.