User Defined Actions

File-> Customisation Configuration -> User Defined Actions

The concept here is that you can define an Insert Action to be triggered after a condition is met. Typical examples of this could be when the condition is met:

• Create a new record in a table
• Initiate an Email to be sent
• Create a Call Ticket
• Create a History Note and much more......

The Action is defined as an Insert SQL Statement along with the Conditional Clause which triggers the Action.

Note:

• When defining a 'From Clause' and 'Conditional Clause' that relate to more than one table, you must prefix each field name with the Table Name 

eg: JobHeader.SalesPerson (aliases may be used to abbreviate table names 'OTHER'  than Primary Table Name)

• These rules are run whenever the Conditional Clause is met

• Ostendo runs User Defined Defaults, Validations and Actions in the following sequence

1. User Defined Defaults
2. User Defined Validations
3. User Defined Actions

Hints:

• When defining a User Defined Action with 'From and Conditional' Clauses it is suggested you first test your syntax using a select statement in the Data Spreadsheet (General -> Data Spreadsheet) feature of Ostendo prior to implementing the Action rule.

• When checking for a blank entry (eg: Null or blank) it is suggested you use the "coalesce" function and condition this with two single quotes, then condition based upon the 2 quotes. This caters for fields that may contain a space or a null (uninitialised value). A coalesce is not needed if the field you are conditioning is a Lookup value or a Domain value.

eg: (coalesce(CUSTOMERMASTER.CUSTOMEREMAIL, '')  = '')       (The coalesce guards against the possibility of a 'blank' existing in the email field)

• Only Insert SQL Statements can be defined. When defining the fields to be inserted as Field Values, always ensure these fields are enclosed in square brackets. Any literals can simply be enclosed in a single quote.

• For Technical Users Only: Whilst the Insert Statement field only allows for the INSERT SQL function, you can also call a Store Procedure from an Action. Instead of the standard 'INSERT INTO' syntax, you would write the following statement if you were calling a procedure (ProcedureName) that requires parameters eg: the field name CUSTOMER and CREDITTERMS AND a literal of 'ABC'

EXECUTE PROCEDURE PROCEDURENAME [CUSTOMERMASTER.CUSTOMER],[CUSTOMERMASTER.CREDITTERMS],'ABC'

Fields:

• Table Name: Drop Down and select the Table Name of the Field you wish to define the Action condition against
• From Clause: Specify the Table Name(s) to be used in the Conditional Clause eg: ITEMMASTER
• Conditional Clause: Specify the conditioning syntax from the 'where' clause in the SQL. eg: ITEMMASTER.ONHANDQTY < 0
• Insert Statement: INSERT INTO OSTDEF_NEGQTYS (ITEMCODE,ONHANDQTY) values ([ITEMMASTER.ITEMCODE], [ITEMMASTER.ONHANDQTY])
• Action When: This is when the Action will be applied from the stated Table Name
• On Insert and Update (Default)
• On Insert Only
• On Update Only
• Rule Status: These options allow you to control the Status of this rule.
• Restricted to User: (Default) Select this only to allow a specific user to test this Action before it is released to all Users. (This is used in conjunction with the Test User Option)
• Active For All Users: This Rule is now available to all users in this database and is effectively 'Live'
• Not Active: This Rule is no longer Active, therefore despite it remaining in the Actions, it is never triggered. Use this to temporarily turn off this rule.
• Restricted User: (Must be specified if Rule Status is set to 'Restricted User') Specify a User that this rule will only be Active for. This allows you to test this rule before committing it to the 'Live' environment.

Examples:

Suppose we have a custom table that records instances of items where the Quantity on Hand was less than Zero at the point the Item Master was Updated. When that condition is met, the Action rule will insert a record into the Custom Table.

Suppose we wish to create a new Call Ticket when a new Customer is created. Note: In this example there is no Conditional Clause as we want this to occur for every new customer that is created.