Data Validation

From Lianjapedia
Jump to: navigation, search
Bm-noteicon.png
Quick Start Guide

Data validation is the process of checking that the data entered by a user is valid and that the data gathered from different data sources is clean, accurate and in the correct format before it is stored in the database.

Data validation prevents the saving of data if it breaks any of the validation rules.

It is easy for an end user to make a mistake when they are entering data or even to leave out important details that must be entered, so we need to check this data when it is being entered into the database.

In a business application its not only users who perform CRUD (Create Read Update Delete) operations on data but also external processes such as loading tariffs, prices etc into tables periodically in batch.

There must be protections in place to prevent data corruption and invalid data being stored in a database.

This is where validation rules come into play.

Validation Rules in the UI

Data Types

Each value manipulated by the Lianja Database engine has a data type. The data type of a column in a database table associates a fixed set of properties with the value. These properties cause Lianja to treat values of one data type differently from values of another. For example, you can add values of numeric data type, but not values of logical data type.

When you create a table, you must specify a data type for each of its columns. These data types define the domain of values that each column can contain. For example, any attempt to store a character string into a numeric column will be rejected by the database engine and an error reported.

This behavior extends into the UI when a column is data bound to a UI Control e.g a TextBox. For each data bound UI Control, Lianja will restrict the domain of data based on the data type of its controlsource.

Bm-noteicon.png
Pro Tip

You can create and alter tables and their columns in the Data Workspace.

Choice Lists

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

UI Controls can have an associated choicelist which allows the user to select from a list of values and prevents data being input which is not in the domain of the controlsource e.g. only allow valid month names for a UI Control bound to a character column of months.

Additionally, choice lists are key to the operation of Data Mapping in Lianja.

The Choices choice list can be based on a SQL SELECT statement, e.g. returning the product names from the products table. It can also include an ORDER BY clause to sort the names.

select productname from mydb!products order by productname

You specify the Choicelist attribute declaratively in the App Inspector or Programatically by assigning a value to the appropriate object property.

Bm-noteicon.png
Pro Tip

You can define the choice list for a database/table/column using the Metadata Editor.
Everywhere that column is used its choice list is associated with it.

Input Masks

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

Using an Input mask types requires a bound data source: a field from a table, or a memory variable (m.memvar) with a default expression to specify the width of the corresponding picture.

Input Masks can be used to restrict what data a user can enter into a field. They can also make data entry easier.

Use an Input Mask to control data entry in a character field. For example, you can force users to enters a phone number with an area code or an employee identification code with 2 upper case letters followed by 3 digits.

You specify the Input Mask attribute declaratively in the App Inspector or Programatically by assigning a value to the appropriate object property.

A complete list of input masks can be found here.

Bm-noteicon.png
Pro Tip

You can define the input mask for a database/table/column using the Metadata Editor.
Everywhere that column is used its input mask is associated with it.

Validation Expressions

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

The Validation and Validation Error Message Attributes define an expression to evaluate when a field loses focus after data entry. If the expression returns false then the Validation Error Message is displayed and the focus remains on the field.

You specify these attributes declaratively in the App Inspector or programatically by assigning a value to the appropriate object property.

The special macro {} can be included in the validation expression. When the expression is to be evaluated any macros are substituted. The {} macro substitutes the current text value in the UI Control being validated. The use of {} in validation expressions is very effective in NoCode App development.

Bm-noteicon.png
Pro Tip

You can define the validation expression for a database/table/column using the Metadata Editor.
Everywhere that column is used its validation is associated with it.

Validation in Custom Delegates

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

You can validate the data entered in the change Custom Delegate.

Required/Mandatory input

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

You specify the Mandatory attribute declaratively in the App Inspector or Programatically by assigning a value to the appropriate object property.

Bm-noteicon.png
Pro Tip

You can define mandatory for a database/table/column using the Metadata Editor.
Everywhere that column is used this is associated with it.

Cross Table Lookups

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

Use the sqlLookup() or keyLookup() function in your validation attribute.

sqllookup("southwind!employees","lastname","'{}'","employeeid","not found")!="not found"
keylookup("southwind!employees","employeeid","{}", lastname, "not found")!="not found"

You specify the Validation attribute declaratively in the App Inspector or programatically by assigning a value to the appropriate object property.

Range Checks

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

Specify these in your validation attribute.

between({}, 0, 9999)

You specify the Validation attribute declaratively in the App Inspector or programatically by assigning a value to the appropriate object property.

Contained within a static List

Available in Form Section fields, Canvas Section fields, Grid Section columns and Custom Section UI Controls.

Specify these in your validation attribute.

inlist("{}", "apples", "pears", "oranges")

You specify the Validation attribute declaratively in the App Inspector or programatically by assigning a value to the appropriate object property.

Autosuggestions

Autosuggestions provide filtered choices as the user types in a Form or Canvas Section field.

The choices are displayed in a dropdown under the control. Clicking on a choice loads its value into the field.

Autosuggestions can be:

  • Static list with or without a dropdown (autocomplete)
  • Dynamic table-based list
  • SQL Select based list
  • User defined function based list

Full documentation for autosuggestions can be found here.

You specify the Autosuggestion attribute declaratively in the App Inspector or programatically by assigning a value to the appropriate object property.

Bm-noteicon.png
Pro Tip

You can define the autosuggestion for a database/table/column using the Metadata Editor.
Everywhere that column is used its autosuggestion is associated with it.

Client-Side Validation in Web/Mobile Apps

Writing client-side JavaScript code is made easier by the fact that Lianja has many VFP compatible functions available for you to use. The complete list can be found here.

The Lianja HTML5 Client maintains local data cursors for tables that are actively being used in sections within pages. Each local data cursor has the concept of an Active Record. As you navigate data and the sections are refreshed the Active Record for these local data cursors is kept in sync with what is displayed in the UI. In desktop apps, the Lianja/VFP database engine provides direct access to these using the familiar alias.fieldname notation.

In your Web and Mobile database Apps you can also get access to this data using Lianja.getCursor("tablename") and then use the getData() and setData() methods on the cursor object that is returned from Lianja.getCursor().

Additionally, seeing as the cursor state is known at any time you can use {alias.fieldname} macros in your validation expressions and any URLs that you have associated with WebView sections. Note that if you create a dynamic local cursor using Lianja.createCursor() then macros will not be evaluated against this cursor, you need to do that manually in your custom JavaScript code.

Local cursors provide the ability to dynamically query and refresh section contents based on the values of fields in other sections.

Referencing data in an existing cursor

You can get a reference to an existing cursor in desktop, web or mobile apps. This is typically how you would inspect the Active record and its fields.

var orders = Lianja.getCursor("orders");
var name = orders.getData("name");
var unitcost = orders.getData("unitcost");
var quantity = orders.getData("quantity");
orders.setData("total", unitcode * quantity);
orders.update( 
    function() {    
        // onsuccess
        Lianja.showSuccessMessage("Record was updated");
        Lianja.showDocument("page:orders?action=refresh");
    },
    function() {
       // onerror
       Lianja.showErrorMessage("Record could not be updated", "Update failed");
    }
);

See the Cursor documentation for details of the properties and methods available.

Understanding macro substitution in the Lianja Web/Mobile Client

Just as you can use {expression} macros in the Lianja Desktop Client you can also use these in your Web and Mobile Apps.

Lianja.evaluate("calculateCustomerBalance('{customers.name}’)",
    function(result)
    {
        Lianja.getElementByID("mypage.mysection.myfield").text = result;
    },
    function(errormessage)
    {
        Lianja.showErrorMessage("Failed to calculate customer balance");
    }
);
// execution continues before the result is returned

Macros are evaluated from left to right:

Lianja.evaluate("calculateCustomerBalance('{customers.name}','{customers.id}’)",
    function(result)
    {
        Lianja.getElementByID("mypage.mysection.myfield").text = result;
    },
    function(errormessage)
    {
        Lianja.showErrorMessage("Failed to calculate customer balance");
    }
);
// execution continues before the result is returned

Macros can be nested so that the inner macros are evaluated before the outer macros. This provides the ability to query information from the server and have that information substituted into another call to the server.

Lianja.evaluate("calculateCustomerBalance('{customers.name}'," + "{getCustomerID('{{customers.custid}}')}")),
    function(result)
    {
        Lianja.getElementByID("mypage.mysection.myfield").text = result;
    },
    function(errormessage)
    {
        Lianja.showErrorMessage("Failed to calculate customer balance");
    }
);
// execution continues before the result is returned

This will result in the following macro substitutions being performed in this order.

 {{customers.custid}} lets call this result3
 {customers.name} let's call this result1
 {getCustomerID("result3")} let's call this result2
 Lianja.evaluate("calculateCustomerBalance('result1', "result2")

Bear in mind that just as in the Lianja Desktop Client you can specify validation expressions that use {...} macros and also use {} to be substituted for the current value of the UI control being validated.

Providing visual feedback to the user

In Desktop Apps it is typical for dialogs to be popped up to inform the user when invalid data has been entered or when validation fails generally.

In Lianja Apps It is better to use the built-in notification methods on the Lianja system object as these are much more modern and they animate in and out on the top right of the UI.

Lianja.showWarningMessage("You are about to be logged out");
Lianja.showSuccessMessage("Record was updated");
Lianja.showErrorMessage("Record could not be updated");

Server-side Validation in Web/Mobile Apps

The following methods can be used to call server side procedures (JavaScript or Lianja/VFP).

Lianja.evaluate()

The Lianja.evaluate() method can be used to call a Lianja/VFP function/procedure.

var result = Lianja.evaluate("myproc()");

To call a procedure in a library, prefix the procedure name with the library name and '::'.

var result = Lianja.evaluate("mylib::mylibproc()");

Lianja.evaluatePython()

The Lianja.evaluatePython() method can be used to call a Python procedure.

var result = Lianja.evaluatePython("myproc()");

To call a procedure in a library, prefix the procedure name with the library name and '::'.

var result = Lianja.evaluatePython("mylib::mylibproc()");

Lianja.evaluateJavascript()

The Lianja.evaluateJavascript() method can be used to call a JavaScript function.

var result = Lianja.evaluateJavascript("myfunc()");

The file 'myfunc.js' should be in the app or library directory and contain a function with the same name as the file. If the 'myfunc.js' file does not exist in the app or library directory and the file 'server_functions.js' exists then the function 'myfunc' is assumed to be defined in the 'server_functions.js' file.

To call a function in a library, prefix the function name with the library name and '::'.

var result = Lianja.evaluateJavascript("myjslib::mylibfunc()");

An alternative syntax using the Lianja.evaluate() method is also available:

var result = Lianja.evaluate("javascript:myfunc()");
var result = Lianja.evaluate("javascript:myjslib::mylibfunc()");

exports.conf

A function can be called directly if it is declared in exports.conf.

var result = myfunc();

The exports.conf file enables a simplified approach for calling server-side business procedures directly from the JavaScript client.

This is accomplished by defining the libraries, functions and scripting language for the functions in the exports.conf file which can reside in the app and/or the library directory.

Example exports.conf file:

# This is an example exports.conf file
#
# Each line describes a function that will be available directly from 
# JavaScript code in the client
#
# library, function, type
# (source library, name of the function, type: vfp, python or javascript)
# (php and python will be added later)
#
# or
#
# library, function 
# (implied type of vfp)
#
# or
#
# function 
# (file expected to exist with a .dbo extension)
#
mylib,myfunc1,vfp
myjslib,myfunc2,javascript
myfunc3

In the Lianja JavaScript Web/Mobile App the server-side functions can be called directly:

// Note that arguments are automatically converted into the correct format for the 
// remote function call and the function call is proxied
// using a synchronous  Lianja.evaluate() call
var result = myfunc1("hello world", 2015, true);
var result2 = myfunc2();
 
// When calling remote functions with objects as parameters the object is automatically 
// converted to JSON and sent base64 encoded
// This allows you to send and receive complete JSON encoded objects between the 
// client and the server
var result3 = myfunc3( {"name":"barry", "company":"Lianja" });

Validation Rules for Columns

Column constraints are rules and restrictions applied on a column such that unwanted data can't be inserted into it. This ensures the accuracy and reliability of the data in the database. Column constraints maintain the data integrity and accuracy of data stored in a specific column.

You can define column constraints that are maintained by the database engine in the Data Workspace.

Details on column constraints can be found here.

Validation Rules for Tables

Table constraints are rules and restrictions applied on a table such that unwanted data can't be inserted into it. This ensures the accuracy and reliability of the data in the database. Table constraints maintain the data integrity and accuracy of data stored in the table.

You can define table constraints that are maintained by the database engine in the Data Workspace.