Sub-grid server calculations

Xeelo Knowledge Base Admin Section
, , , ,
1

Sub-grid server calculations allows complex calculations executed when sub-grid line is being saved or on specific action (see below). It is very important to keep the following syntax for each calculation in order to ensure proper calculation. Server calculations are supported in the following areas (not all of the calculations are available in all areas):

  • Object template (Save)
  • Workflow step (Workflow action)

Server-String

Custom server string function that allows various string operations and use of SQL string functions.

  • Syntax
    • idXXX - request value for specific sub-grid line
    • idXXXr - referenced value from request (use for combo-box to return the name of the value instead of binded value)
    • {idXXXm} - request value for specific object line from current request for Default value
    • idXXXm - request value for specific object line from current request for Calculations
    • ‘text’ - string text
  • Operators
    • Concatenate of the strings +: idXXX + idYYY + ‘text’ + idYYYr
    • SQL string function: left(), right(), upper(), lower(), substring(), … For more details refer to SQLserver string functions
  • Logical operations

IMPORTANT: All fields that participate in Server-String calculation are considered as strings. Please note that numeric operations even in logical operations will not be evaluated properly and will lead to calculation error.

Server-Math

Custom server math function that allows various mathematical operations and use of SQL string functions.

  • Syntax
    • idXXX - request value for specific sub-grid line
    • {idXXXm} - request value for specific object line from current request for Default value
    • idXXXm - request value for specific object line from current request for Calculations
    • 9999.888 - number with dot as decimal separator without any thousand separator
  • Operators
  • Logical operations

IMPORTANT: All fields that participate in Server-Math calculation are converted to numbers. Please note that string operations even in logical operations will not be evaluated properly and will lead to calculation error.

Server-RequestInfo

Function that returns specific request information in various formats.

  • Syntax
    • {Today} - today’s date in application format (dd-mm-yyyy)
    • {TodayTime} - current time in format (hh:mm:ss)
    • {TodaySerial} - today’s date in serial format (yyyymmdd)
    • {RequestorID} - user ID of requestor
    • {RequestorName} - user name of requestor
    • {ObjectName} - name of object for current request
    • {CompanyName} - name of company for current request
    • {ObjectTypeName} - name of object type for current company
    • {RequestID} - request ID of current request
    • {RoleName} - name of the role for current request
    • {RequestStatus} - name of the status for current request
    • {RequestType} - type of the request (create, update , update empty)
    • {RequestPriority} - Id of request priority (1, 2, 3, 0)
    • {ObjectDefaultName} - name of the template for current request
    • {LastActionDate} - date of last workflow action
    • {CreatedDate} - date of request creation
    • {ModifiedDate} - last date of request modification
    • {RequestTag} - new / overdue request tag
    • {ActionDuration} - time difference in minutes between last workflow date-time and current date-time
    • {ModifiedByID} - user ID of last modification (please note that this can contain user: system)
    • {ModifiedByName} - user name for last modification (please note that this can contain user: system)
    • {CreatedByID} - user ID of creator
    • {CreatedByName} - user name of creator
    • {LastWorkflowUserID} - user ID of last action user
      *{LastWorkflowUserName} -user name of last action user
    • {AssignedUserName} - list of users currently assigned to the ticket (inbox)
    • {AssignedUserID} - list of user IDs currently assigned to the ticket (inbox)
    • {LastActionName} - Last WF Action name that was used
    • {LastActionID} - Last WF Action ID that was used
    • {ObjectDefaultID} - ID of the template for current request

Server-Lookup

Function returns the value from lookup. Works the same way as client lookup with difference that this value is calculated on the server side.

  • Syntax
    • idXXX,idYYY
      • idXXX - ID of lookup to be searched in
      • idYYY - object line that should be used as source value for lookup
      • idZZZ - Optional parameter for lookup filter

Server-DateRange

Function returns if the specific date is in the interval. If the condition is true, returns 1. If not, returns 0.

  • Syntax
    • idXXX,idYYY,idZZZ
      • idXXX - date which is tested
      • idYYY - interval start date
      • idZZZ - interval end date

Server-Search

Function returns defined value of different object with predefined condition / search value.

  • Syntax
    • idXXX,idYYY,idZZZ,idAAA
      • idXXX - ID of object to be used for search
      • idYYY - object line to be return as result of search (from object idXXX)
      • idZZZ - object line to be used for search condition (from object idXXX)
      • idAAA - object line to be used as search value (from current object)

IMPORTANT: Please note that this function is using non-indexed fields in search criteria. Due to this reason you may experience some performance implication if this function is used very often on high number requests. In this case it is recommended to replace this with server lookup.

Server-UserSearch

Function that returns UserID from User login.

  • Syntax
    • idxxx - id of an object line where user login is set (or you can write particular log-in as a text)

Server-LogicalMath

Function returns value 0 or 1 based on logical mathematical condition.

  • Syntax
    • idXXX - request value for specific sub-grid line
    • idXXXm - request value for specific object line from current request
      • 9999.888 - number with dot as decimal separator without any thousand separator
  • Operators
  • Comparators
    • > < != <= >= =

Server-LogicalString

Function returns value 0 or 1 based on logical string condition.

  • Syntax
    • idXXX - request value for specific sub-grid line
    • idXXXm - request value for specific object line from current request
    • ‘text’ - string text
  • Operators
    • Concatenate of the strings +: idXXX + idYYY + ‘text’ + idYYYr
    • SQL string function: left(), right(), upper(), lower(), substring(), … For more details refer to SQLserver string functions
  • Comparators
    • = != like not like

Server-DateDiff

Function returns the date difference in days, months or years.

  • Syntax
    • interval, idXXX,idYYY
      • interval - date interval as text: day, month, year
      • idXXX - sub-grid line of first date
      • idYYY - sub-grid line of second date

IMPORTANT: in case value of sub-grid lines specified for input does not contain the date then value 01-01-1990 is used instead.

Server-DateAdd

Function returns new date value with added specified number of days, months or years.

  • interval, idXXX,idYYY
    • interval - date interval as text: day, month, year
    • idXXX - sub-grid line with value to be added to the date
    • idYYY - sub-grid line with specified date

IMPORTANT: in case value of sub-grid lines specified for input does not contain the date then value 01-01-1990 is used instead.

Server-UserInfo

Function returns the information about specific user(s) such as name, email and login name. Object line that is used as parameter have to have the valid user ID or multiple user IDs separated by comma.

  • Syntax
    • info,idXXX
    • info - predefined key values: email, name, login
    • idXXX - object line that should be used as list of users

Hint: Can be used to get email of specific application user email,idxxx

Server-SubTotalSearch

This very specific function returns total of sub-grid from different object where specific column of sub-grid contains value from current sub-grid. This function was specifically developed to check the match of invoice lines with purchase order lines and ensure that total value of allocated invoice is exceeding the total value of invoice.

  • Syntax
    • idXXX,idYYY,idZZZ,idAAA
      • idXXX - object line with sub-grid where search should be done (this can be object line from different object than current request)
      • idYYY - sub-grid line to be summarised (from sub-grid on object line idXXX)
      • idZZZ - sub-grid line to be used for search condition (from sub-grid on object line idXXX)
      • idAAA - sub-grid line to be used as search value (from current sub-grid)

IMPORTANT: Please note that this function is using non-indexed fields in search criteria. Due to this reason you may experience some performance implication if this function is used very often on high number requests. There is currently no alternative for this function. Use of lookup is not possible in this case.

Server-SubLineNumber

Function returns the next available sub-grid line numbers where numbering is starting from 1. Once the line number is assigned to sub-grid lines it will not change by this calculation.

  • Syntax
  • no input parameters

Server-SubLineHistory

Feature consists of two items:

  • Enable history track for memo on sub-grid.
  • New server calculation that would allow to get sub-line history into memo.

Parameter is Object sub-line ID for SubLineHistory.

1 Like