Object server calculations

Object server calculations allows complex calculations executed when request is being saved or some action is performed (see below). These calculations can be used on request (for sub-grid refer to Sub-grid server calculation. 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)
  • Periodic calculation (Scheduler)
  • Notification calculation (Before nottification is generated)
  • Printout calculation (Before printout is generated)

It is possible to define order of calculations to avoid overwriting data fields.

Server-SubTotal

Function that returns the summary of values from specific sub-grid and specific field of the sub-grid. The values of sub-grid are first converted to numbers and then summarised.

Syntax

  • idXXX,idYYY
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be summarised

Function can be used to summarise for example lines of purchase order which can be used to define purchase order threshold.

Server-SubConcat

Concatenates specific sub-grid column into one field divided by comma “,”. This function returns only unique list of concatenated values.

Syntax

  • idXXX,idYYY
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be concatenate

Maximum length of returned text can be 255 characters. All above this length is truncated.

Server-SubConcatSearch

Concatenate of strings according to search condition.

Syntax

  • idAAA,idXXX,idYYY,idZZZ
  • idAAA - Object line ID - object line with sub-grid that will be used for search
  • idXXX - ObjectSubLineID - sub-grid line to be concatenate (value1,value2,valu3,…)
  • idYYY - ObjectSubLineID - sub-grid line where to search
  • idZZZ - ObjectLineID / ObjectSubLineID - line / sub-grid line what to search

Server-SubConcatConditional

Concatenates specific sub-grid column into one field divided by comma “,” that meets a condition. This function returns only unique list of concatenated values.

Syntax

  • idXXX,idYYY,idZZZ,Text
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be concatenate
  • idZZZ - sub-grid line that is used for the condition
  • text - text without quotation marks that will be used for the condition. Text string that is used for comparison: idZZZ = text. Text value should be entered without '. It is possible to refer to part of the texts and use wild characters to replace unknown text e.g. %part of text%.

Maximum length of returned text can be 255 characters. All above this length is truncated.

Server-SubAverage

Function that is able to calculate the average value of values entered in specific sub-grid column. Only numeric values are taken into consideration. All non-numeric values are replaced with 0.

Syntax

  • idXXX,idYYY
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated

Server-SubAverageConditional

Function that is able to calculate the average value of values entered in specific sub-grid column but only for sub-grid lines that meets a condition. Only numeric values are taken into consideration. All non-numeric values are replaced with 0.

Syntax 1

  • idXXX,idYYY,idZZZ,text
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated
  • idZZZ - sub-grid line used for condition comparison
  • text - text string that is used for comparison: idZZZ = text. Text value should be entered without '. It is possible to refer to part of the texts and use wild characters to replace unknown text e.g. %part of text%

Syntax 2

  • idXXX,idYYY,idZZZ,idAAA
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated
  • idZZZ - sub-grid line used for condition comparison
  • idAAA - object line used as value for text string that is used for comparison: idZZZ = idAAA

Server-SubWeightAverage

Function that is able to calculate weighted average based on to specified columns in sub-grid. Both of the columns have to contain only numbers as all other values will be convereted to 0.

Syntax

  • idXXX,idYYY,idZZZ
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated
  • idZZZ - sub-grid line used as weight value

Server-SubCount

Function calculates the number of lines of the specified sub-grid

Syntax

  • idXXX
  • idXXX - object line with sub-grid

Server-SubMax

Function returns the biggest number of specific sub-grid column. All non-numeric values are replaced with 0.

Syntax

  • idXXX,idYYY
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated

Server-SubMaxContidional

Function returns the biggest number of specific sub-grid column. All non-numeric values are replaced with 0.

Syntax 1

  • idXXX,idYYY,idZZZ,text
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated
  • idZZZ - sub-grid line used for condition comparison
  • text - text string that is used for comparison: idZZZ = text. Text value should be entered without '. It is possible to refer to part of the texts and use wild characters to replace unknown text e.g. %part of text%.

Syntax 2

  • idXXX,idYYY,idZZZ,idAAA
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated
  • idZZZ - sub-grid line used for condition comparison
  • idAAA - object line used as value for text string that is used for comparison: idZZZ = idAAA.

Server-SubMin

Function returns the smallest number of specific sub-grid column. All non-numeric values are replaced with 0.

Syntax

  • idXXX,idYYY
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated

Server-SubMinContidional

Function returns the smallest number of specific sub-grid column according to provided condition. All non-numeric values are replaced with 0.

Syntax 1

  • idXXX,idYYY,idZZZ,text
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated
  • idZZZ - sub-grid line used for condition comparison
  • text - text string that is used for comparison: idZZZ = text. Text value should be entered without '. It is possible to refer to part of the texts and use wild characters to replace unknown text e.g. %part of text%.

Syntax 2

  • idXXX,idYYY,idZZZ,text
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated
  • idZZZ - sub-grid line used for condition comparison
  • idAAA - object line used as value for text string that is used for comparison: idZZZ = idAAA.

Server-SubTotalConditional

Function that returns the summary of specific sub-grid column where other specific columns contain predefined text value. This function has two possible inputs.

Syntax 1

  • idXXX,idYYY,idZZZ,text
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated
  • idZZZ - sub-grid line used for condition comparison
  • text - text string that is used for comparison: idZZZ = text. Text value should be entered without '. It is possible to refer to part of the texts and use wild characters to replace unknown text e.g. %part of text%.

Syntax 2

  • idXXX,idYYY,idZZZ,idAAA
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be calculated
  • idZZZ - sub-grid line used for condition comparison
  • idAAA - object line used as value for text string that is used for comparison: idZZZ = idAAA.

Server-SubCountConditional

Function returns the number of lines from sub-grid that meet specific condition.

Syntax 1

  • idXXX,idZZZ,text
  • idXXX - object line with sub-grid
  • idZZZ - sub-grid line used for condition comparison
  • text - text string that is used for comparison: idZZZ = text. Text value should be entered without '.

Syntax 2

  • idXXX,idZZZ,idAAA
  • idXXX - object line with sub-grid
  • idZZZ - sub-grid line used for condition comparison
  • idAAA - object line used as value for text string that is used for comparison: idZZZ = idAAA.

Server-SubStringConditional

Function returns the first value of specified sub-grid column that meets specific condition.

Syntax 1

  • idXXX,idYYY,idZZZ,text
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be returned
  • idZZZ - sub-grid line used for condition comparison
  • text - text string that is used for comparison: idZZZ = text. Text value should be entered without '.

Syntax 2

  • idXXX,idYYY,idZZZ,idAAA
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be returned
  • idZZZ - sub-grid line used for condition comparison
  • idAAA - object line used as value for text string that is used for comparison: idZZZ = idAAA.

If you use combobox as idZZZ then as text string is used value (bind) from reference.

Server-SubTotalSearch

This very specific function returns total of sub-grid from different object where specific column of sub-grid contains value from current request. 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 - 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. There is currently no alternative for this function. Use of lookup is not possible in this case.

Server-SubCheckDuplicity

Function returns 1 if the current value in object line idYYY contains duplicity (within current sub-grid). Otherwise the functions returns 0.

Syntax

  • idXXX,idYYY
  • idXXX - object line with sub-grid
  • idYYY - sub-grid line that needs to be compared

Server-String

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

Syntax

  • idXXX - request value for specific object line
  • idXXXr - referenced value from request (use for combo-box to return the name of the value instead of bined value)
  • idXXXp - value of previous request for specific object line
  • ‘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
    • In Server-String is possible to use CASE WHEN conditions. For more details refer to SQL server case construction
  • Possibility to use general variables in calculations eg. support use of macro “{VARIABLE}” in Server-String. For example as server calculation: CASE WHEN idxxx = ‘{GeneralVariableCode}’ then ‘1’ else ‘{SomeOtherGeneralVariable}’ end

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.

VERY IMPORTANT: In case the field is comparing with the string value, it is necessary to add N as NVARCHAR. Example: CASE WHEN id1177=N'Something to compare with' THEN blue(id1177) ELSE yellow(id1177) END

Server-Math

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

Syntax

  • idXXX - request value for specific object line
  • idXXXp - value of previous request for specific object line New
  • 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
  • {RoleID} - ID of the role for current request
  • {RequestStatus} - name of the status for current request
  • {RequestStatusID} - ID 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
  • {RequestorLogin}
  • {RequestorEmail}
  • {RequestorLanguage}
  • {RequestorAssigned}
  • {NotAssigned}
  • {RequestState}
  • {LastRoleName}
  • {LastRequestStatus}
  • {LastComment}
  • {SubLineNext}
  • {id%[0-9]}
  • {OwnerUserID}
  • {OwnerUserName}
  • {WatcherUserID}
  • {WatcherUserName}
  • {Variable,%}

Server-ReportResult

Calculation returning report result into memo object line.

Syntax

  • idXXX, idYYY
  • idXXX - Report Result ID
  • idYYY - Language code or object line ID with language code (en, sk, cz)
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 or text (optional) - ID or text of 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 1

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

Syntax 2

  • idXXX,idYYY,idZZZ,idAAA,filter
  • 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)
  • filter - filter parameter equals 0 if it is possible to find request itself. 1 if it is NOT possible. Example: In request #1 set filter=0 if you want to find request everything (#1 included). If you want to find everything except request #1, set filter=1; you get request #2

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-TotalSearch

Summarize of numbers according to search condition.

Syntax

  • idAAA,idXXX,idYYY,idZZZ,filter
  • idAAA - Object ID - object to search
  • idXXX - ObjectLineID - to be summarized
  • idYYY - ObjectLineID - line where to search
  • idZZZ - ObjectLineID / ObjectSubLineID - line / sub-grid line what to search
  • 0 / 1 - Current request exclusion - 1 exclude current request, empty - include current request

Server-ConcatSearch

Concatenate of strings according to search condition.

Syntax

  • idAAA,idXXX,idYYY,idZZZ,filter
  • idAAA - Object ID - object to search
  • idXXX - ObjectLineID - to be concatenate (value1,value2,valu3,…)
  • idYYY - ObjectLineID - line where to search
  • idZZZ - ObjectLineID / ObjectSubLineID - line / sub-grid line what to search
  • 0 / 1 - Current request exclusion - 1 exclude current request, empty - include current request

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 object line
  • idXXXp - value of previous request for specific object line
  • 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 object line
  • idXXXp - value of previous request for specific object line
  • ‘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,idAAA(optional),idBBB(optional)
  • interval - date interval as text: day, month, year, hour, minute
  • idXXX - object line of first date
  • idYYY - object line of second date
  • idAAA - object line of time related to first date (required for interval: hour and minute)
  • idBBB - object line of time related to second date (required for interval: hour and minute)

IMPORTANT: in case value of object 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 - example: day,idXXX,idYYY
  • interval - date interval as text: day, month, year
  • idXXX - object line with value to be added to the date
  • idYYY - object line with specified date

IMPORTANT: in case value of object 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

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

Server-UserIsRole

Function that returns value “1” or “0” if specific user has a specific role assigned.

Syntax

  • idXXX,role id
  • idXXX - id of an object line where user id is set (calculated)
  • role id - id of a specific role (define without the “id” - e.g., 17)

Example
You can use this calculation to manage workflow e.g., Request will not be moved to the next step unless a user with specific role has approved the request. (For the conditional movement of a request in WF use Object action and set the condition to it - condition that meets value of this calc. e.g., “1” )

Server-UserListRole

Calculation is deprecated. Use User Management with references.

Server-LinesChanged

Calculation returns if someone changed particular object line(s) or not. If so, then returns 1. If not, then returns 0.

Syntax

  • step-or-not, idXXX, idYYY, …, idZZZ
  • step-or-not - just flag if the calculation should be checked for step or save action. Example: ‘step’ or ‘save’
  • idXXX, idYYY, …, idZZZ - ids of object lines which should be checked
Server-AttachementLink

Function returns link in html format of provided attachment

Syntax

  • XXX - object line id of attachment.

Server-AttachementOCR

Function allows OCR functionality for attachment. Once the function is called, Xeelo starts with converting of provided attachment to digital format. If second parameter is used, calculation finds:

  • reading - returns reading order OCR
  • internal - returns internal order OCR (reading in blocks)
  • barcode - returns the first appearance of bar code (value)
  • qrcode - returns the first appearance of QR code (value)

Syntax

  • idXXX - object line id of attachment. Example can be found here
  • type (optional) - reading / internal / barcode / qrcode

Server-AttachmentPrintout

Function saves specific printout to attachment field. Calculation should be applied on field type Attachment.

Syntax

  • XXX - id of printout which should be saved to current attachment field. Value is number without “id” prefix.

Server-ActionUserCheck

If specific users (from provided User List) executed specific actions (from provided Action List) as last workflow action, function returns formatted response (according to the last parameter). If not, function returns empty string.

Syntax

  • idXXX, action-names-separated-by-pipe, return-format (optional)
  • idXXX - object line contains list of users separated by comma. Example: 102,1042,65
  • action-names-separated-by-pipe - workflow action names separated by pipe. Example Approve|Reject|Submit
  • return-format - optional string parameter that allows admin specify the return value. By default is set {ActionUserName} ({ActionDate}). It is possible to use placeholders {ActionUserID}, {ActionUserName}, {ActionDate}

Server-LineHistory

Feature consists of two items:

  • Enable history track for memo on request.
  • New server calculation that would allow to get object line history into memo.

Parameter is Object line ID for LineHistory.

Server-UniqueIdentifier

Returns unique identifier. More detail can be found here.

Syntax

  • No parameters needed

Server-AutonumberGenerate

Function calculates and returns autonumber based on input parameter.

Syntax:

  • idXXX - refers to field where the user can calculate which Autonumber from the standard Autonumber list should be used
    or
  • XXX - refers to id of the concrete Autonumber from the Autonumber list

Utilisation:
Good and relevant case for utilisation are the financial document number sequences. You may want to differentiate the autonumber type base on the type of document e.g. Invoice - overhead (INVOYYYY-####), Invoice - goods (INVGYYYY-####) etc… and in the same time, you don’t want to assign autonumber right at the beginning after first save, as you may need some further validation ensured via workflow till you recognise the document as relevant and only after you want to assign the sequence number in order to avoid creating unnecessary “holes” in the numbering of the documents.

declare @Out nvarchar(50)
exec dbo.spGenerateAutonumber 12, @Out output
select @Out

Warning: Calculation works only when the field itself is empty! Once the field is filled with value, the calculation will not be executed.
Another point to remember is to secure the field against editing. Unlike the fields with Autonumber feature, the field using Server-AutonumberGenerate can be edited when not disabled.

2 Likes

For Server-String, when referring to object line type Memo, syntax idXXX doesn’t work.
More info here: https://forum.xeelo.com/t/how-to-get-body-of-memo-in-server-calculation/244
and here:

In case of Memo object line we don’t store the value of memo in RequestDataXX. In RequestData is only ID that represents link RequestMemoID. If you want to work with memo value and not memo pointer you have to use the following SQL function dbo.fnRequestLineDataMemo(idXXX) in server string calculation.

For old Xeelo:
First, you have to get content of the Memo by dbo.fnRequestLineDataMemo(idXXX) and then call the required calculation.

For i.e.: dbo.fnRequestLineDataMemo(idXXX) + ’ end of the wall.’

For new Xeelo:
Use function dbo.fnRequestMemoDetails(idXXX)

New server calculation has been introduced to the family of built in Xeelo calculations.
Server-AutonumberGenerate - Function calculates and returns autonumber based on input parameter.

Syntax:

  • idXXX - refers to field where the user can calculate which Autonumber from the standard Autonumber list should be used
    or
  • XXX - refers to id of the concrete Autonumber from the Autonumber list

Server calculation can be applied on request (subgrid as well), workflow step calculation, Export calculation, Notification calculation, Printout calculation.
It is possible to utilise the calculation within Object action - Special function:

declare @Out nvarchar(50)
exec dbo.spGenerateAutonumber 12, @Out output
select @Out

Utilisation:
Good and relevant case for utilisation are the financial document number sequences. You may want to differentiate the autonumber type base on the type of document e.g. Invoice - overhead (INVOYYYY-####), Invoice - goods (INVGYYYY-####) etc… and in the same time, you don’t want to assign autonumber right at the beginning after first save, as you may need some further validation ensured via workflow till you recognise the document as relevant and only after you want to assign the sequence number in order to avoid creating unnecessary “holes” in the numbering of the documents.

Warning: Calculation works only when the field itself is empty! Once the field is filled with value, the calculation will not be executed.
Another point to remember is to secure the field against editing. Unlike the fields with Autonumber feature, the field using Server-AutonumberGenerate can be edited when not disabled.

Example:

Hope this helps.
Please include this calculation to the list of available server calculations.

2 Likes