Built-in Procedures

add_toolbar_option

add_toolbar_option(button_string_literal)

Adds a report toolbar icon. It has the same Awesome Icon as the button. Icon has the same tooltip as the description of the button. Pressing the icon fires buttons “BUTTON_PRESSED” trigger of the button. More detailed description of building menus can be found in the Programmer's Guide.

Parameters:

  • button_string_literal – name of a button
add_toolbar_option('FORMS.SEND_VAT_FORM_TO_IRS');

add_menu_option

add_menu_option(button_string_literal)

the button contributes to the menu. Pressing the menu option fires buttons “BUTTON_PRESSED” trigger. More detailed description of building menus can be found in the Programmer's Guide.

add_menu_option('MENU.Companies');

change_password

change_password(oldPassword, newPassword)

changes current users password to newPassword, after verification of the old password

clear_record

clear_record

Clear (remove) current record from the screen without posting changes to the server

close_form

close_form

close current form

close_report

close_report

closes report/blob preview window.

confirm

confirm(display_text, ok_button_selected)

display confirmation message box displaying display_text. If ok_button_selected is true, then the Yes button is the default button, and otherwise No is the default button

display_blob

display_blob(tableName, columnName, idColumnName, idValue, filename, mimeTypeColumn)

Display blob in preview. The trigger does not wait until the the display completes. The file comes from the column of a table specified by parameters. The row is specified by the value of the id of the row

Parameters:

  • tableName – text, name of the blob's table
  • columnName – text, name of the blob's column
  • idColumnName – text, name of the table's id Column
  • idValue – oidn(encrypted number), value of the row's ID
  • MimeTypeColumn - text, name of the column containing MIME type code of the blob's content

If uppercased tableName contains string TEMP then the row containing the BLOB will be deleted after the download completes.

download_data

download_data(block, format)

download block data in specified format

Parameters:

  • block – block name
  • format – download format. Currently only html is supported
download_data(PRODUCTS, html);

download_from_blob

download_from_blob(tableName, columnName, idColumnName, idValue, filename, mimeTypeColumn)

Start to download file from blob. The trigger does not wait until the download completes. The file comes from the column of a table specified by parameters. The row is specified by the value of the id of the row

Parameters:

  • tableName – text, name of the blob's table
  • columnName – text, name of the blob's column
  • idColumnName – text, name of the table's id Column
  • idValue – oidn(encrypted number), value of the row's ID
  • MimeTypeColumn - text, name of the column containing MIME type code of the blob's content

If uppercased tableName contains string TEMP then the row containing the BLOB will be deleted after the download completes.

download_from_filesystem

download_from_filesystem(path, filename, content_type)

Start to download file from server. The trigger does not wait until the download completes

Parameters:

  • path – encrypted text containing the path to the file (including file name)
  • filename – text,file name of the downloaded file
  • content_type – text MIME type of the file content. If null the server will report application/octet-stream

duplicate_record

duplicate_record

copy all data of the previous record into a new record

enter_query

enter_query

put the current block in the enter_query mode

execute_query

execute_query

execute query in the current block

begin enter_query; :COMPANY.NAME:='Brothers'; execute_query; exception when others then message_error(ERROR_MESSAGE); end; -- the current block (COMPANY) will be looked for records containing the 'Brothers' word. -- The trigger logic assumes, that the COMPANY is the current block.

go_item

go_item(item_name)

moves cursor to the item_name item, performing necessary processing. Sets success to true if the navigation was successful

go_record

go_record(record_number)

move cursor to record_number record. Records in the block are numbered staring from 1

list_values

list_values

list values (call LOV) in the current item

message_error

message_error(text)

display text with error decoration. Set the value of success code to false

message_info

message_info(text)

display text with info decoration

message_warning

message_warning(text)

display text with warning decoration

next_item

next_item

moves cursor to the next item (as though the return or key tab was pressed). Sets the value of success code according to the result of the possible validations, that might happen as a result of the key press

next_record

next_record

Move cursor to the next record in the current block

open_form

open_form(form_name[[,par_value]...])

open the formName form. Set form parameters to parValue...; the calling routine is suspended until the called form initialization. Then it runs till the completion. It is good practice not to put any code in a trigger after opening another form.

open_form(Firma); open_form(Ledger, :COMPANY.ID, :COMPANY.NAME, to_text(:TOOLS.MONTH,'yyyy-MM-dd'), :COMPANY.VAT_PAYER, :TOOLS.MONTH <=:FIRMA.CLOSED_MONTH);

post

post

post current record to the database. Set the success system variable according to the post operation result

protect_tree

protect_tree(block, protect)

set the block and all the blocks down the master-detail tree updatable and insertable properties to boolean protect value. Used to bulk lock or unlock all blocks depending on particular record

-- To protect the data of orders from change from the user interface one might issue the following call: protect_tree('ORDERS',:ORDERS.STATE!='Open');

raise

raise(exception_type_ID, exception_number, message)

raise exception

Parameters:

  • exception_type_ID - exception type Identifier
  • exception_number – number of the exception
  • message – message of the exception
raise(AccountError, 5, 'insufficient funds');

refresh

refresh

retrieve the current record from the database with current values, forgetting all client changes. Refresh all dependent master/detail tree.

refresh_tree

refresh_tree

refetch all blocks down the master-detail tree from the database (current block exclusive)

-- AFTER_DUPLICATE_RECORD z_old_id oidn; begin z_old_id:=:INVOICE.ID; post; INVOICE_P.COPY_LINES(z_old_id,:INVOICE.ID); refresh_tree; exception when others then message_error(ERROR_MESSAGE); end;

This trigger assumes, that the Invoice is comprised of the INVOICE ane INVOICE_LINES blocks. INOICE_LINES is a detail of INVOICE.

  1. The current ID of the record is saved for later use
  2. The post call is performed. ID of the inserted record is set by the framework
  3. The INOICE_P.COPY_LINES stored procedure is called to copy INVOICE lines from the original invoice in the database
  4. The copied lines are retrieved (refresh_tree;) from the database to synchronize UI and the database

run_report

run_report(report_name[[,par_value]....]) [criteria_block block_name]

run the report_name report. Set all the parameters. Parameters have to be of the text data type. If the criteria_block is set then add a p_where parameter, that contains the last query criteria from the block

run_report(ledger,:parameter.company_id,:parameter.month);

send_mail

send_mail(addressTo, addressFrom, subject, messageText)

sends email

set_block_CRUD

set_block_CRUD(block_name, create, retrieve, uptext, delete)

set the block_name insertable, queryable,updatable and deleteable properties to the respective values. If an argument is null then do not change the property

--To set LEDGER block to query only mode issue the following call set_block_CRUD('LEDGER', false, true, false, false);

set_block_property

set_block_property(block_name, property_name, property_value)

set the block_name property_name property to the property_value

Properties:

  • visible – set the visible property of the block
  • changed – set the changed property for all items of the the block to the specified boolean value

set_breadcrumb

set_breadcrumb(breadCrumbText)

Add breadcrumb option to breadcrumb menu. If breadCrumbText equals null then remove the option.

set_breadcrumb('Orders');

set_context_parameter

set_context_parameter(par_name, par_value)

set the server parameter par_name to par_value value. Datatypes have to agree

set_context_parameter(COMPANY_ID,:parameter.company_id);

set_item_property

set_item_property(item_name, property_name, property_value[, property_value2])

set the item_name property_name property to the property_value

Properties:

  • default_value – set the default value of the item to the property_value
  • next_item - set the next_item property of an item to the property_value
  • valid – set the valid property of an item to the property_value. Property_value has to be of the boolean datatype. After invalidating an item, the item will be verified within the next validation event
  • visible – set the visible property of the item. If the current item becomes invisible then the property_value2 item becomes the current item of the block. It is the programmer role to assure that the alternative item is visible
  • changed – set changed property of an item to the property_value. Property_value has to be of the boolean datatype
  • insert_allowed – set whether the item can be entered on new records
  • update_allowed – set whather the item can be updated on old records
set_item_property('INVOICE_LINE.VAT_RATE',default_value,null); set_item_property('INVOICE.VAT_NO',next_item,'INOICE_LINE.LINE_NUMBER'); set_item_property('COMPANY.STARTING_MONTH',valid,false); set_item_property('INVOICE.VAT_NO',visible,false);

set_order

set_order(block_string_literal, order_string_literal)

set "order by" phrase of the block

set_order('INVOICE','"issue_text" desc');

set_success

set_success(boolean_expression)

sets status code of the trigger to boolean_expression. Useful in validation triggers to specify trigger outcome without raising exceptions or error messages

set_title

set_title(text)

sets title in the browser's tab (HTML title element)

set_where

set_where(block_string_literal, where_string_literal)

set the default where phrase of the block. The where phrase can contain items, globals and parameters in bound variable syntax

set_where('INVOICE','customer_id=:parameter.customer_id');
The set_where and set_order calls are protected from a SQL – injection attack. The phrases are stored on the web server. Web server receives the block and the number of the phrase and parameter name-value pairs, that are passed to a SQL select statement via binding variables. In the whole framework no SQL statements are passed from the client to the server.

validate_item

validate_item

performs item validation. Sets the value of success code according to the result of validation

Arithmetic Functions

abs

abs(number)

absolute value of number

coalesce

coalesce(val1, val2[,..., valn])

first not null of the val1..valn or null. All arguments have to be of the same datatype

days_between

days_between(text1, text2)

days between two texts. If text1 is later then text2 then the result is positive

highest

highest(val1,val2[,..., valn])

maximal of the val1..valn or null (if any value is null). All arguments have to be of the same datatype

least

least(val1, val2[,..., valn])

minimal of the val1..valn or null (if any value is null). All arguments have to be of the same datatype

length

length(text)

length of text in characters

mod

mod(number, number)

modulo division

months_between

months_between(text1, text2)

months_between returns number of months between texts text1 and text2. If text1 is later than text2, then the result is positive. If text1 is earlier than text2, then the result is negative. If text1 and text2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise system calculates the fractional portion of the result based on a 31-day month and considers the difference in time components text1 and text2.

round

round(number1, number2[, rounding])

rounds number1 to number2 scale, according to a rounding algorithm. Default algorithm is HALF_UP.

Rounding algorithms

  • CEILING - Rounding mode to round towards positive infinity. If the number1 is positive, behaves as for UP; if negative, behaves as for DOWN. Note that this rounding mode never decreases the calculated value
  • FLOOR - Rounding mode to round towards negative infinity. If the number1 is positive, behave as for DOWN; if negative, behave as for UP. Note that this rounding mode never increases the calculated value.
  • HALF_DOWN - Rounding mode to round towards "nearest neighbour" unless both neighbours are equidistant, in which case round down. Behaves as for UP if the discarded fraction is > 0.5; otherwise, behaves as for DOWN.
  • HALF_EVEN - Rounding mode to round towards the "nearest neighbour" unless both neighbours are equidistant, in which case, round towards the even neighbour. Behaves as HALF_UP if the digit to the left of the discarded fraction is odd; behaves as HALF_DOWN if it's even. Note that this is the rounding mode that minimizes cumulative error when applied repeatedly over a sequence of calculations.
  • HALF_UP - Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up. Behaves as for ROUND_UP if the discarded fraction is ≥ 0.5; otherwise, behaves as for ROUND_DOWN. Note that this is the rounding mode that most of us were taught in grade school. DOWN Rounding mode to round towards zero. Never increments the digit prior to a discarded fraction (i.e., truncates). Note that this rounding mode never increases the magnitude of the calculated value.
  • UP - Rounding mode to round away from zero. Always increments the digit prior to a nonzero discarded fraction. Note that this rounding mode never decreases the magnitude of the calculated value.

sign

sign(number)

returns:

  • 1 if number is greater than 0
  • 0 if number equals 0
  • -1 if number is less than 0

to_number

to_number(text)

converts the text to decimal following Java BigDecimal(String) constructor

utf8

utf8(text)

UTF8 character code

ERROR_CODE

error number of the last raised exception

Text Functions

chr

chr(number)

utf8 character of given code (number)

coalesce

coalesce(val1, val2[,..., valn])

first not null of the val1..valn or null. All arguments have to be of the same datatype

highest

highest(val1, val2[,..., valn])

maximal of the val1..valn or null (if any value is null). All arguments have to be of the same datatype

least

least(val1, val2[,..., valn])

minimal of the val1..valn or null (if any value is null). All arguments have to be of the same datatype

lower

lower(text)

converts text to lower case

lpad

lpad(text_to_pad, length, padding_text)

left pad text_to_pad upto length with padding_text

md5hash

md5hash(text)

returns md5 haxedecimal representation (lower case) hash of supplied text

replace

replace(text, replace_what, replace_by)

replace every occurrence of replace_what in text by replace_by

rpad

rpad(text_to_pad, length, padding_text)

right pad text_to_pad upto length with padding_text

server_parameter

server_parameter(text)

returns settings of server parameters

Properties:

  • MAIL_CONFIGURED – if mail is configured then 'Y' else 'N'

substr

substr(text, start_position[, length])

substring length characters of text, starting from start_position. If there is no length then till the last position

to_text

to_text(text | date | number[, format_str])

convert text, date or number to text according to Java format string

trim

trim(text)

trim whitespace on boths sides

upper

upper(text)

convert text to upper case

url_parameter

url_parameter(text)

returns text url parameter. Example: if the application was called using http://app.com/?param=value#place url then url_parameter('param') returns 'value'.

ERROR_MESSAGE

error message of the last raised exception

ERROR_TYPE

error type of the last raised exception

Date Functions

add_days

add_days(date, number)

adds number days to the date. Number does not have to be an integer. Allows fractions of a day

add_months

add_months(date, number)

returns the date text plus integer months. The number argument will be truncated to integer. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date

coalesce

coalesce(val1, val2[,..., valn])

first not null of the val1..valn or null. All arguments have to be of the same datatype

highest

highest(val1, val2[,..., valn])

maximal of the val1..valn or null (if any value is null). All arguments have to be of the same datatype

last_day

last_day(date)

date of the last day of the month

least

least(val1, val2[,..., valn])

minimal of the val1..valn or null (if any value is null). All arguments have to be of the same datatype

sysdate

sysdate

current client date and time

to_date

to_date(text, format_str)

converts text to date according to Java format string

trunc_to_day

trunc_to_day(date)

the date with time part set to zero

trunc_to_month

trunc_to_month(date)

date of the first day of the month

trunc_to_year

trunc_to_year(text)

text of the January 1, of the text argument

Boolean Functions

in_enter_query

in_enter_query()

form is in enter query mode (user enters query)

if in_enter_query() then message_info('You are entering query'); end if;

is_list

is_list()

returns true if the current form was called as list

on_last_row

on_last_row()

returns true if cursor is on the last row of the data in the current block

regex_test

regex_test(expression, pattern, modifiers)

true if expression matches pattern with modifiers

success

success

returns the success code of the last executed procedure