Validating SQL Data Before an Insert in Dodeca

This is the second of three posts on relational data in Dodeca.  The first post discussed the Data Block range and how it can be used to add functionality to your SQL Views in Dodeca.

This post will extend that idea by discussing how you can use Excel formulas in the Data Block range to test input data before saving data on a SQL View.

As in the last post, I will not go into detail on setting up the Data Set range, or the basics of setting up your SQL Views.  Please see the last post for links on those topics.

We are going to have a look at a SQL View in Dodeca of a simple employee table that allows the inserting of additional rows.  Aside from the ID which is auto-generated, there are only three fields in the table: LastName, FirstName and Position.

We would like to ensure all the fields are there before sending to the relational database.  Checking for blank fields is a rather simple validation, but the method discussed could be used for other validations as well, such as proper data types, valid codes, a proper numerical range, correct dates, etc.

Here is a look at the columns and data in the table called HR.

HR table columns and data

There are only four fields, and, as stated above, the ID column is auto generated on insert.  The important thing to know about this table is that all fields are required.  Nulls are not allowed in any field, so all fields must be entered in order to properly save the data.

We have a Dodeca View called “HR Update” to view this data, and also allows users to add to this data, i.e., perform an Insert to the table.  The DataSet range will allow Adds, but not updates or deletes.  Our SQL Passthrough dataset will have a Select Statement and an Insert Statement only, not a delete or update statement.

The Insert statement of the SQL Passthrough Dataset

Here is the View in Dodeca:

The HR Update View in Dodeca

Suppose there is a new entry to add to the table, Leslie Knope.  But the individual entering the data does not know what Position to enter so it is left blank.  When attempting to save the data, we get an error.  Oops.

HR Update View with error on Save

Clicking on the details button of the error notice we see:

Error message on Save attempt.

Hmmm.  This is the information returned from the relational database, and it is not exactly helpful.  I’m sure most users could figure out that all fields need to be entered in this simple example.  But if there were more advanced validations needed, wouldn’t it be better to somehow verify the data before sending it to the server?  Perhaps we could enter a message on the View before sending to indicate that something is missing or wrong.

We can do this by adding some Excel formulas since they are valid in a Dodeca View, and then include the formula cell in the Data Block range.  Recall when I wrote that Dodeca brings together the best of Essbase and relational data?  Well, add Excel to that mix as well.  It brings together the best of Essbase, relational data, AND Excel.

We will enter a formula in the View to check if any of the three required fields are blank, but we will only check this if one field has been entered.  If any of the three fields has been entered, check the other two for blanks.  We are checking each field individually using OR statements.  The formula we need can condense down to this:

=AND(OR(ISBLANK(C6),ISBLANK(D6),ISBLANK(E6)),OR(NOT(ISBLANK(C6)),NOT(ISBLANK(D6)),NOT(ISBLANK(E6))))

The formula looks a bit long-winded, but basically checks any of the three fields for blanks if any of the three fields has data.  If this resolves to TRUE then we have an issue.

For now, we are just going to inform the user if there is a potential issue with saving the data.  We will do this by presenting a Validation message on the View.  In the next blog post, we will actually stop the SQL save process if there are any Validation errors.  We will do this by adding a bit of workbook scripting.

Here is the current Data.Range:

The Data.Range named range in the View

It is three rows long to include the SQL Column names, and to include a blank line for inserts.

Now we will add the validation formula above to the template.  We will add it to cell G4.

Validation check field in the View

Let’s also add a formula to display a message if this field resolves to TRUE.  The formula will be entered in H4 and looks like this:

=IF(G4,”Please enter all fields before saving”,””)

Yes, we could have combined the two formula fields, but let’s just keep them separate for now.  We will also format cell H4 as bold and red.

Field to display message if there is a Validation issue

Now we name the range on the View as DataBlock.Range.

The DataBlock.Range defined range in View

We will hide column G in the View template since it does not need to be seen in the View.  And, as I highlighted in the last post, we include this range in the Data Table definition of the View.

The Data Table definition in the View

All set.  Now let’s run the View and try to add a new row of data.  We will only enter the first two fields.

Validation message displayed

The message gets displayed after the first field is entered.  It is there to notify the user of an issue.  It will only go away after all the fields are entered:

All fields entered, no validation message needed

And because the range follows all the fields, the validation message will appear on any and all lines that are missing fields:

Message appears only where appropriate

All set.  Our user now gets a helpful message before saving.  But the user might still go ahead and hit save, resulting in the same error message we saw earlier.  In the next blog post, we will add a bit of workbook scripting to stop the save event, adding even more control to the process and eliminating errors on the View.

 

Leave a Reply

Your email address will not be published. Required fields are marked *