A lot of the systems I work on require some type of dynamic end user configurable screen. On a recent project the end users could setup a list of “groups” that a subject could belong to. And for each of these groups they needed to be able to specify a list of valid values for that group. For instance a Group called Sex, with valid values of Male and Female. Another example is Age Range with valid values of Child, Preteen, Teen, 21-35, 36-45, 46-55, 55+.

There is a lot of ways to design the structure for this:

1. Two database tables, (Group and a Child Table called Group Values)
2. Store an array in the database
3. One database table, with a field that contains the list of values.

For this project I chose Option 3, and we stored the values as a comma separated list in a string field. This article will look at why I chose #3, the user interface and code for entering and storing the values, as well as the eventual dynamic screen that this configuration sets up.

Over the years I have used all of the above options and likely several others not listed. Many factors play into the decision each time, to name just a few:

1. Development Language
2. Backend Database
3. Complexity of the Data Stored
4. Number of Items Stored
5. How often the data will be changed
6. How often the data will be accessed
7. Size and Complexity of the Project
8. Number of end users.

As with just about every decision in our industry there is never just one answer and it becomes a series of compromises to select the best option for that specific requirement. So lets look at a couple of options that I did not use for this project.

1. Two Database Tables

In this option you would have a Parent Table (Group) to hold the information about the Group (Sex, Age Range) and then a Child Table (GroupValue) to hold the available options for that group. This option is definitely the most traditional option and would likely make most academics happy as it follows the letter of the law for a normalized database. However for this project I felt that a second table was over kill since the values to be stored were simple strings and there would rarely be more than 10 values for a group. If you have followed my blog at all I am sure you know I know and follow traditional database normalization most of the time, but 30+ years of experience allows me to “break” the academic rules when it makes sense and this is one of those times.

2. Store an Array in the Database

I rarely use this option. Because I work on some many different projects with many different backend databases, I am also concerned about portability of my code. Many (read that as almost all) backend databases do not support storing arrays. After all remember your academics, an array breaks the rules of a normalized database, and who designed most database backends? Most database backends can trace their origins to a University. There are a few that do support arrays and Hyperfile is on of the ones that does, so it is was an option for this project. But a little digging will tell you that each database vendor that does support arrays implements them in their own special way. And at times you can find yourself,doing twice as much work, to access the array values as you would had you went with a more traditional design. And many 3rd party tools will not support or know how to handle the array column so if any external tools such as Report Writers, Analytic Suites, etc. are going to access your data it could also be an issue. Even simple SQL statements become more cumbersome. In short I have yet to find a situation where I have justified using an Array column type in a database. But just in case you are still tempted, lets look at Hyperfiles implementation.

You define the column as you normally would, in this case as a String of 50. Then you turn on the array check box and specify the Dimension. That last part is my issue with Hyperfiles implementation, to store an array it has to have a fixed dimension. Remember for my requirements I said there would rarely be more than 10 values for a group. Well if I were to use an array in Hyperfile that would change to never! As soon as I user comes up with a need to store 11 values, its back to the drawing board with a database modification and update of the program. You might be tempted to just use a value greater than they would ever use, say 100. But that 30+ years of experience has taught be that somewhere, somehow, somebody will come up with 101 values! And it seems very wrong to define 100 values when many of them will only store 2 or 3 values.

The requirement of a fixed array gives us some insight into how Hyperfile has implemented arrays internally. I am sure this is a gross over simplification, but I suspect what they are really doing is the same old school thing we use to do. They are creating 10 columns (Value1, Value2, Value3, etc) and then just isolating us from that design. So basically they are doing it the exact way that would have gotten you an F in your database design course in college!

I know your sill not completely discouraged, so lets look at external tools, it this case really tools from pcSoft. First up the WDMap tool. Right click on the table in the analysis and chose “Edit the data file with WDMAP”

Which gives us this screen, further proof that what they have really done is defined 10 columns.

Using the WDSQL tool and a little playing I discovered that to access element 2 of the Values array via SQL I had to use syntax similar to this

SELECT "VALUES"[2] FROM groups
UPDATE groups SET "VALUES"[2] = 9

Fairly similar to how we would access an array in WX code, but by no means a SQL standard and not likely to port to another database. And likely to cause issues if I bring in a 3rd party SQL tool or developer that isn’t specifically Hyperfile knowledgeable.

Ok, Ok, if you still want to use an array in your database, knock yourself out, just don’t come crying to me when 6 months from now one of the gotcha, gets ya!

3. One database table, with a field that contains the list of values.

So now let’s look at the solution that I choose for this project. Here is the actual table definition GGValues is the text field we will be storing the values in. And before you ask the table definition already existed before I got involved so not my naming convention 🙂

Notice this is just an everyday normal string field. In our case we have defined it as 120 characters, which supports the type of values we are storing, but it could be a memo field if you were storing lots of values, etc. But then again if that is the case this might not be the right solution in the first place.

Let’s look at the interface I designed for updating the list of values, then we will look at the code behind the interface.

You have seen this type of interface before (I try not to reinvent the wheel if I don’t have to). To add an entry type something into the Value field and press the Add button.

To edit a value, select it in the table, it shows up in the value edit field, where you can change its values, then press the Change button.

And to delete an item select it in the table and press the Remove button.

There are other ways to do this interface, such as just using an Edit in Place table, but for this project this was the interface that made the most sense.

So lets look at how this interface is put together in the code.

In the init of the window I declare an array to hold the values. You didn’t think I was anti array did you? I am just anti array in the database :-). Then I use the really nifty StringToArray function to move the values from our database field into the array.

As we saw GGValues is the string database field that is holding our comma separated list. StringToArray takes that string and splits it into individual entries in an array based on the delimiter specified, in this case a comma. That’s it no hand coded parsing logic, nothing. Its functions like this that make WX so productive and why this option was a no brainer decision for this project. As a side note don’t get to excited about StringToArray it does not properly handle Excel formatted CSV files (Quotes around strings) as I have spoken about in the past. Not an issue here since we are in control of the string we will be reading, but if you are needing to parse Excel formatted CSV files you might want to take a look at this article

Next I added a table control to the screen with its content being the array just defined.

I added the edit control to the screen. It is not linked to anything that it is only used as a temporary editing area.

I added the 3 buttons Add, Change, and Remove to the screen. I set the Change and Remove button to be grayed initially.

The code behind the add button makes sure that something was entered into the edit field so we don’t add blank entries, and it uses the TableSeek function to make sure the value doesn’t already exist, as we don’t want duplicate entries. As long as both of those conditions pass the entry is added to the table control. Notice just to the table control, not the array. And finally ExecuteProcess is used to run the select row code of the table, which fills the edit control and determines which buttons to enable/disable, we will look at that code shortly.

Only one line of code behind the Change button, which simply changes the value in the table control.

The code behind the Remove button, deletes the row from the table control and then executes the table selection code.

The code in the Selecting a Row of table control, sets the edit control to the value in the table and then enables or disables the Remove and Change buttons if there is a value

And finally the code behind the OK button uses another one of those nifty WX functions. TableToText turns a table control into a delimited string field. In this instance I told it I don’t want a heading row, I don’t need a column separator since there is only one column, and I want a line separator of a comma. This may seem a little backwards to you, you may have been expecting a column separator of comma and a line separator of a carriage return. If I had multiple columns and was exporting for some external purpose that would likely be the case but I want the result to be

Male,Female

not

Male
Female

So now we have given the use the ability to edit the list of values and we are storing them in the database, the only thing left to do is use them on our dynamic screen. So let’s take a quick look at that. Here is a screen shot of a subject that has had the Age – Children group added to it, and you can see our list of values being displayed as combo entries.

The code to accomplish this is very similar to the code we used to load the values on our edit screen.

First we declare the array.

Then we load that array.

Line 12 uses a Query to select the group that should be displayed for this subject.

Line 13 stores that Group ID in the Subject Table for future reference

Line 14 sets the caption of the combo to the Group Name, part of how we achieve a dynamic user customizable solution.

Line 15 Uses the same StringToArray function that we used earlier on the edit screen

Line 16 Inserts a Value of “Unknown” as the first selection, to indicate that a value was not selected for the group.

Line 17 Reinitializes the combo Control so that the new array values will be displayed.

Line 18 Checks to see if the value stored is in the list. If the value matches Line 19 Selects it, otherwise Line 21 Selects the first row (“Unknown”)

And that is it we have a simple way to create a dynamic end user configurable screen option and store the values in a single database field as a comma separated list.

Be sure to go over to wxLive.us and watch the Uncle Pete’s corner webinar that is the companion to this article. 

Uncle Pete’s Corner is webinar series on all things WX, to watch the recorded version of this webinar, many other WX related webinars or to watch future ones live go to wxLive.us

[suffusion-the-author display=’author’]

[suffusion-the-author display=’description’]

• WX