The validation check functions allow additional validation checks to be carried out at various stages. These can be customised through the Maintenance menu → Database Functions option in the IQX User Interface by users with appropriate rights.

All these functions have a similar method of working. If the function returns an empty string the validation step is considered passed. Any other return value will prevent the user from completing the step in question and the return message will be displayed in a dialog box, optionally relevant window can be opened to allow the user to correct the problem.

In the case of the Company and Person State Change validations, the function must reset the state to the original state.

An example of such a function (for company state change) is:

(IN compid CHAR(20),IN oldstatus CHAR(1),IN newstatus CHAR(1))
RETURNS long VARCHAR
-- If change is disallowed you must set status back to oldstatus before returning an error message
BEGIN
  DECLARE rv long VARCHAR;
  DECLARE @name CHAR(60);
  DECLARE @addr1 CHAR(100);
  DECLARE @postcode CHAR(50);
  DECLARE NewLine CHAR(2);
  SET NewLine="char"(13)+"char"(10);
  SET rv='';
  IF newstatus <> 'C' THEN RETURN(rv)
  END IF;
  SELECT name,addr1,postcode INTO @name,@addr1,@postcode FROM Company WHERE CompanyID = compid;
  IF isnull(@name,'') = '' THEN SET RV=RV+'Name, '
  END IF;
  IF isnull(@addr1,'') = '' THEN SET RV=RV+'Address, '
  END IF;
  IF isnull(@postcode,'') = '' THEN SET RV=RV+'Postcode, '
  END IF;
  IF NOT EXISTS(SELECT* FROM employment WHERE CompanyID = CompID) THEN SET RV=RV+'at least one Company Contact, '
  END IF;
  IF NOT EXISTS(
    SELECT* FROM phone WHERE Who = 'C' AND WhoID = CompID UNION ALL
    SELECT* FROM phone JOIN employment ON WhoID = Employment.PersonID AND Who = 'CP' WHERE Employment.CompanyID = CompID) THEN
    SET RV=RV+'at least one telephone or other contact number, '
  END IF;
  IF NOT EXISTS(SELECT* FROM TagValue WHERE TagLocation = 'C' AND TagID = 'IN' AND ID = compid) THEN SET RV=RV+'Industry, '
  END IF;
  IF rv <> '' THEN
    SET rv='The following compulsory field(s) are incomplete'+NewLine+RV;
    UPDATE Company SET STATUS = oldstatus WHERE CompanyID = compid
  END IF;
  RETURN(rv)
END

If you want to open a relevant screen to allow the user to correct any issues the return message should be formatted in the way:
[Record Type][RecordID]>>ViewID[tab character][Message Text]
opening a specific view by specifying » followed by the ViewID is only supported from IQX v2.16.10 onwards.

If no tab character is included, the whole message is displayed and no additional screens are opened. The I type requires the DocumentID as the RecordID parameter.
An example of such a validation function (for timesheets) is:

(IN persid CHAR(20),IN vacid CHAR(20),IN placid CHAR(20))
RETURNS long VARCHAR
BEGIN
  DECLARE rv long VARCHAR;
  DECLARE P45 SMALLINT;
  SET rv='';
  SELECT P45Requested INTO P45 FROM Pay_Employee WHERE PersonID = persid;
  IF isnull(P45,0) = 1 THEN SET RV=string('P',PersID,"char"(9),'No timesheet can be entered as P45 has been requested. Please abandon timesheet and contact accounts.')
  END IF;
  RETURN(rv)
END

Note:If the return string beings with a caret character ^ then the warning is advisory not absolute.

Back to Technical Help Section list
Back to 'How To' guides for IQX system administrators