This is the third of three posts on using relational data with Dodeca, specifically on the Data Block Range that can be used with your SQL ranges in Dodeca. It is another example of how Dodeca makes it easy for you to customize your Reporting, Planning and Analysis application to deliver the exact solution for you.
In the first post on the SQL DATA BLOCK range, we examined how it works with your SQL data range and how it can be utilized in your Dodeca Views. In the second post, we added more functionality to perform validations on your SQL data. In that example, we only provided feedback to the user. We indicated that there was an issue with the data the user was trying to save. However, we did not prevent the save action from occurring if the user went ahead and hit Save. If the user did hit save with these Validation issues still applicable, they would still receive an error on the View.
In this post, we will go one step further and actually stop the Save process, and also provide a message as to why the event was cancelled. Sometimes you really need to hit users over the head to get the message across.
So how will we do that? One of the many reasons Dodeca is the best tool on the planet for your Planning and Reporting systems is the amount of control developers have with what happens in a Dodeca View. The key to this control is what the Dodeca architect team refers to as the “event driven extensibility” of Dodeca. This event driven extensibility is defined in Dodeca using what are called Workbook Scripts.
Do not let the term Script throw you though. We do not actually write scripts filled with code. That work has already been done by the Dodeca team. They have already done all the heavy lifting. We just need to define what we need to do, and then configure the workbook script to do it.
So here is what is happening in our example from the previous blog post.
- The user is attempting to perform an Insert. Specifically, the user is attempting to Save a Data Set range, which will do the insert of the data to a relational table.
- We know the table is defined such that it does not allow null fields. All the fields must have data in order to save the new row of data.
- Thus far, we have put a notification on the View to let the user know that all fields should be there before saving.
- If the user attempts to save anyway, the user will get an error. We want to stop this error.
Here is our mission:
- If the user attempts to save while there are validation errors, we will stop the “save” process to avoid the error.
- We will present a message box to let the user know that the data cannot be saved, and why.
So here is where the Workbook scripts (WBS) enter the picture. We will use a workbook script to accomplish our mission defined above.
Before we get into the WBS however, let’s create an easy way to indicate that there are Validation issues. Recall from the previous post how we set a formula in column G to evaluate to TRUE if there is an issue. We know where we need to check. Let’s do two additional things on our template. We will create a named range for the column G rows we need to check, and then use another cell to count the number of TRUE’s.
We will name the range in column G CheckFields:
And add a formula to cell G2 to count the number of TRUE’s in the range. And we will name this field ErrorCount:
Now we have an easy way to know if there are issues. If the value in the ErrorCount cell is > 0.
Back to the Workbook Script.
Remember how I said the Dodeca team has already done all the heavy lifting? Well, there are many things that can happen in a Dodeca view. These are called Events (hence, event-driven extensibility), and Dodeca has already defined over 100 of these events for us to use. Events are captured, and then Procedures are tied to the event to do get things done. What event do you think we want to capture here? That’s right, it is the Save event. Specifically, it is the Save Data Set Range event. And we want to capture the event *before* it happens. So we will look for a Before Date Set Range Save event.
Let’s open the Workbook Script editor for this View.
There are four sections: Definition, Properties, Event Links and Procedures. We are not concerned with the Definition or Properties sections today.
I can right click on “On Event…”, select New, and be presented with a list of the available events to use. Scrolling down I find “BeforeDataSetRangeSave”. Bingo. That’s the one we want.
I can select that, then hit tab twice to automatically create a procedure called OnBeforeDataSetRangeSave. The procedure name is automatically generated, but you could create one with another name for this if you want.
Now that I have the procedure, I need to specify the Methods within the Procedure. The methods are the vehicles for getting things done. We want to Cancel the Save event, so scrolling down I find the CancelEvent method:
Now to configure this event. Here are the properties:
We only want this method to be in effect if there are validation errors. And we know if there are validation errors if our ErrorCount field value is > 0. So in the MethodCondition property we enter
Also set the CancelProcedure property to TRUE
This method also provides a Property to enter the name of a procedure to run if the event is cancelled. We can use this to display a message box informing the user of the cancelled event. We will create a Procedure called ShowCancelMessage, and we can utilize the ShowMessageBox method there. Let’s enter the message as:
“The Save operation has been cancelled due to Validation errors on the data. Please check the Validation messages and correct the data before saving.”
The last bit of cleanup we need to do is reset the View status. There is method for that, called, you guessed it, SetViewStatus. The View status gets set to working when the Save button is clicked. We want to reset this back to Ready.
That’s it. One event, two procedures, and three methods used to get everything done.
Now when the user attempts to save date before it is ready to go, the Save will be cancelled, and the User will get a message as to why. Let’s see it in action. I have opened the View and entered some data. It is incomplete however. I try to save anyway:
I hit the okay button, and we are back to the View. No errors. Voila!