Validation

Column constraint to specify minimum and maximum values for a date or numerical column

CREATE TABLE orderhead;
  (ord_week INT RANGE 1,52,;
  ord_date DATE RANGE date(), gomonth(date(),12))

https://www.lianja.com/doc/index.php/RANGE_Column_Constraint

Column constraint to set a default value for the specified column

ALTER TABLE customer;
  ADD COLUMN dateref DATE DEFAULT date()

https://www.lianja.com/doc/index.php/DEFAULT_Column_Constraint

Column constraint to validate a change to the value of a column

ALTER TABLE customer;
  ADD COLUMN timeref CHAR(8);
  CHECK validtime(timeref);
  ERROR "Not a valid time string"

https://www.lianja.com/doc/index.php/CHECK_Column_Constraint

Table constraint activated when an operation to insert, update or delete records in the table is called

CREATE TABLE purchase_order;
  (POid i PRIMARY KEY, SuppId i, POtotal n(10,2),;
  CHECK callauth(); 
  ERROR "Not authorized")

https://www.lianja.com/doc/index.php/CHECK_Table_Constraint

Column constraint to validate a change to the value of a column

ALTER TABLE customer;
  ALTER COLUMN timeref;
  SET CHECK validtime(timeref)

https://www.lianja.com/doc/index.php/SET_CHECK_Column_Constraint

Column constraint to specify input mask

ALTER TABLE "customers" MODIFY CONSTRAINT CUSTOMERID SET PICTURE "@^"

https://www.lianja.com/doc/index.php/PICTURE_Column_Constraint

Column constraint to specify a choice list for a character column

open database southwind
// Static choices
ALTER TABLE "example" MODIFY CONSTRAINT TITLE SET CHOICES "Mr,Mrs,Ms"
// Dynamic choices @tablename,expression
ALTER TABLE "orders" MODIFY CONSTRAINT CUSTOMERID SET CHOICES "@customers,customerid"
// Dynamic choices SQL Select
ALTER TABLE "orders" MODIFY CONSTRAINT CUSTOMERID SET CHOICES "select customerid from customers"

https://www.lianja.com/doc/index.php/CHOICES_Column_Constraint

Column constraint to allow NULL values

ALTER TABLE customer;
  ADD COLUMN custref CHAR(5) NULL
ALTER TABLE customer;
  ALTER COLUMN custref NOT NULL

https://www.lianja.com/doc/index.php/NULL_Column_Constraint

Column constraint to disallow NULL values

ALTER TABLE customer;
  ADD COLUMN custref CHAR(5) NULL
ALTER TABLE customer;
  ALTER COLUMN custref NOT NULL

https://www.lianja.com/doc/index.php/NOT_NULL_Column_Constraint

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")

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");
    }
);

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.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 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" });

https://www.lianja.com/doc/index.php/Data_Validation

I’m tryng to use the classic “Browse” command
I I wryte:

Code:

open database southwind
use example
browse field title:c="MR.Mrs,Miss,Mr",;
last_name:w=10:V=Validate(),;
first_name

proc validate()

endproc

in the validate, how can I test the value just insert in the “last_name” field?

if I test “last_value” I see the previous value..

Pass “{}” to the validation proc.

Code:

valproc("{}")

I’m trying to do validation for a Textbox field on a canvas.

Suppose I set Validation to !empty(“{}”) and Error to “Field must not be empty”.

1. When I press the tab key, the error message appears in the yellow error dialog, but the cursor moves to the next field. Is this expected behavior? I would expect the focus to be kept on the offending field.

2. If the field is empty to begin with, tabbing through the field does not generate the error dialog. Is this expected behavior? The field fails the validation, so I would expect the error dialog to appear and focus to remain on the offending field.

3. What is the recommended approach if the validation for a field has multiple components, requiring varying error messages for the field?

4. Are there other preferred alternatives (different delegates, etc.) for doing field level validation?

The Validation expression is checked on exiting the field when the field value has been modified.
This expression can also be a call to a function/procedure/prg, so you could handle your varying error messages their, e.g.

Validation: myfunc(“{}”)

Code:

//myfunc.prg
param fld_value
do case
case fld_value = "something"
...

You also have Section and Page level ‘Valid when’ delegates and the Section ‘Before data update’ delegate where data validation can be carried out:

Category: Validation

Data validation and integrity [examples]

Cross Table Lookups

Range Checks

Contained within a static List

Referencing data in an existing cursor

Providing visual feedback to the user

Server-side Validation in Web/Mobile Apps

Lianja.evaluate()

Lianja.evaluateJavascript()

exports.conf