AllFunctions

From SMT-X
Jump to: navigation, search
IfEmpty (returns first arg that is not NULL or empty)
IfZero (returns first arg that is not 0)
Iif (When arg1 true then return arg2, else return arg3)
Concat (Concat all args)
Format (format args (arg2, arg3, ...) according to specification in arg1)
ToNumber (converts arg1 to a decimal. When that is not possible and arg2 is provided then return arg2)
ToNumber Example: tonumber('#REPLACE-FORMFIELD-CalcFields||Value1||#',0) * tonumber('#REPLACE-FORMFIELD-CalcFields||Value2||#',0)
Round (rounds decimal in arg1 to integer, or to a decimal with arg2 decimals if arg2 is provided. Arg3 is optional and can contain "floor" or "ceiling" as rounding types, but ONLY works if arg2=0.
Replace (searches in arg1 for arg2, and replaces all occurences of it with arg3. Does the same as string.replace
example: REPLACE('#REPLACE-FORMFIELD-Granular Access : form 1||textfield1||#', ' ', '%20')    
RegexReplace (searches in arg1 for regular expression pattern in arg2, and replace all occurences of it with arg3)
Works as Replace but now with regular expression pattern. Also see Regex.Replace
SubString  (Returns sub string from arg1 starting at position (0-based) in arg2 for the length of arg3 (or till the end of the string when arg-3 is empty)
IndexOf (Returns position (0-based) in arg1 of arg2, or -1 if not found)
LastIndexOf (Returns position (0-based) in arg1 of last occurence of arg2, or -1 if not found)
Length (Returns length (1-based) of arg1)
Filename (Returns the filename of the path in arg1)
FileExtension (Returns the file extension of the path in arg1)
IsValidPath (Returns true if arg1 is a valid uri (url or local path))
IsSspFile (Returns true if arg1 is a ssp file (uploaded via ssp))
GetLocalPathForSspFile (Returns the local absolute path of the ssp file in arg1)
PadLeft (leading zeros) (Adds the character (or the first character of the string) in arg3 in front of the string in arg1 until the length is arg2)
Trim (Removes all leading and trailing occurrences of arg2 from arg1. If arg2 is empty then spaces are used)
ToString (converts arg1 to a string) 
ToJSON (converts arg1 to a json object) 
ToDate (converts arg1 to a date, according to user culture, in case arg2 is provided that format is used. In case arg3 is provided this will be the return value in case of errors. 
DateDiff (shows difference between arg1 (earliest) and arg2 (latest), in units specified in arg3 (D,H,m,s)
DateAdd (adds arg2 units to a date provided in arg1. Arg3 = unit: Y,M,D,h,m,s. Use Format to return in a string)
Now (returns current datetime as a datetime variable. Use Format to return in a string)
UtcNow (returns current datetime in UTC as a datetime variable. Use Format to return in a string)
IsDayLightSavingActive (Returns Boolean containing True if daylight saving is active in the current server timezone. Arg1 is a datetime to check on, so you can also use NOW() as arg1)
GetUtcOffset (Returns number containing difference in hours between local server timezone and UTC time for datetime in Arg1. Eg for Brussels, the result will be 2 for a datetime in May, and 1 for a datetime in December)
WebserviceLookup (arg1 is the webservice name, arg2 is the field you want to retrieve (use empty string when fetching scalar), arg(n)-arg(n+1) are the filter fields)
WebserviceLookupExtended Works similar to WebServiceLookup, but has 2 additional parameters:
Arg3: maximum number of records to use (with WebserviceLookup this is 1)
Arg4: separator to use between the multiple results
ExecuteApplication (arg1 is the path to the application on the server, arg2 are the arguments)
HtmlEncode (HTML Encodes the string in arg1)
ForEachString (Returns a concatened string (separated by arg4) of the results of the expression in arg3, which is run on each part of the string in arg1, split by separator in arg2. The placeholder {0} in the expression arg3 replaces each part.) Tip: To use quotes in the expression in arg3 escape the quotes as: \'
Arg1: String to be searched
Arg2: Seperator for search in Arg1
Arg3: Expression. {0} can be used
Arg4: Seperator for result
Arg5: Optional Boolean argument: Evaluate result
ForEachString example:  The following example searches in the string "a,b,c,d" for all occurences seperated by a comma and adds an underscore before and after each string. All new strings are then concatenated by "||":
ForEachString('a,b,c,d', ',' , 'concat(\'_\',\'{0}\',\'_\')','||')  result: _a_||_b_||_c_||_d_
Arg5 explained: 
Without Arg5 this example ForEachString('1, 5, 6', ', ' , '{0}','+') results in 1+5+6
With Arg5 set to true (or 1, yes, ok, ja) : ForEachString('1, 5, 6', ', ' , '{0}','+', 1) results in 12
ForEach (Returns the result of the function calls of the expression in arg4, which is run on each part of the string in arg1, split by separator in arg2, using the seed in arg3. The placeholder {VALUE} in the expression arg4 replaces each part, {SEED} replaces the seed for the first call and the result for the others.) Tip: To use quotes in the expression in arg4 escape the quotes as: \'
Arg1: String to be searched
Arg2: Seperator for search in Arg1
Arg3: The initial seed
Arg4: Expression. {SEED} and {VALUE} can be used
ForEach example:  The following example searches in the string "a,b,c,d" for all occurences seperated by a comma and adds them to an array in a JSON object:
ForEach('a,b,c,d', ',' , '{"thearray": []}', 'AddToJsonArray(\'{SEED}\', \'$.thearray\', JsonEncode(\'{VALUE}\'))')  result: {"thearray":["a","b","c","d"]}
ToBase64 (Returns the base64 string of the contents of the file represented by the URL in arg1)
URLEncode (Encodes the string in arg1 for proper use as URL)
For Example: a space is substituted with %20
XMLEncode (Encodes the string in arg1 as propoer XML)
JSONEncode (Encodes the string in arg1 as proper JSON)
JSONDecode (Decodes the string representaton of a JSON object in arg1 to a JSON object)
UpdateJSON (manipulate the content of a JSON)
Arg1: the JSON
Arg(n): the selector to the field to update (see https://www.newtonsoft.com/json/help/html/SelectToken.htm )
Arg(n+1): the value to use
Example:
Normal JSON: { "title": "the example title", "amount": 5 }
The JSON is now updated:
UpdateJSON('{ "title": "the example title", "amount": 5 }', '$.title', 'the updated title', '$.description', 'the new description')
Result:
{"title":"the updated title","amount":5,"description":"the new description"}
In the example above a new property was added (description).
OnlyUpdateJSON (modify the content of properties in a JSON)
Example: OnlyUpdateJSON('{ "title": "the example title", "amount": 5 }', '$.title', 'the updated title', '$.description', 'the new description')
Result: {"title":"the updated title","amount":5}
OnlyAddJSON (modify the content of properties in a JSON)
Example: OnlyAddJSON('{ "title": "the example title", "amount": 5 }', '$.title', 'the updated title', '$.description', 'the new description')
Result: {"title":"the example title","amount":5,"description":"the new description"}
AddToJSONArray (add an element to a JSON array)
Example: AddToJsonArray('{"thearray": []}', '$.thearray', '"a"')
Result: {"thearray":["a"]}
PrettyJSON (Format JSON in such a way that it is easy to read)
Arg1: the JSON
Example: Normal JSON: { "title": "the example title", "amount": 5 }
PrettyJSON('{ "title": "the example title", "amount": 5 }')
Result:
{
 "title": "the example title",
 "amount": 5
}
SmallJSON (Format JSON in such a way that it is minified)
GetJSONProperty / GetJSONPropertyFromPath (Get a property value from a JSON object or JSON file)
Arg1: the JSON or the location to the JSON file
Arg2: the selector to the property you want to get (see https://www.newtonsoft.com/json/help/html/SelectToken.htm)
GetJSONProperty('{ "title": "the example title", "amount": 5 }', 'amount')
Result: 5
GetXMLProperty / GetXMLPropertyFromPath (Get a property value from a XML object or XML file)
Arg1: the XML or the location to the XML file
Arg2: the selector to the property you want to get (see https://www.w3schools.com/xml/xpath_syntax.asp)

GetXMLProperty('<?xml version="1.0" encoding="UTF-8"?>
	<bookstore>
		<book>
		  <title lang="en">Harry Potter</title>
		  <price>29.99</price>
		</book>
		<book>
		  <title lang="en">Learning XML</title>
		  <price>39.95</price>
		</book>
</bookstore>', '/bookstore/book[1]/title/text()')

Result: Harry Potter
Transpose (Change columns to rows)
Arg1: Seperator to use to split arg(n)
Arg2: Column separator in result
Arg3: Row separator in result
Arg(n): the current values
Example data:
Colors: "Red||Green||Black"
Sizes: "XL||XL||S"
Example 1:
TRANSPOSE('||', ', ', '<br />', 'Red||Green||Black', 'XL||XL||S')
Result:  Red, XL<br />Green, XL<br />Black, S
Example 2:
'<table><tr><th>Color</th><th>Size</th></tr><tr><td>' + TRANSPOSE('||', '</td><td>', '</td></tr><tr><td>', 'Red||Green||Black', 'XL||XL||S') + '</td></tr></table>'
Result: <table><tr><th>Color</th><th>Size</th></tr><tr><td>Red</td><td>XL</td></tr><tr><td>Green</td><td>XL</td></tr><tr><td>Black</td><td>S</td></tr></table>
PrepareForNCalc (escapes in the string in arg 1 a backslash by doubling it and a single quote by adding a backslash in front - in that order) - only used in advanced cases
PrepareAsParameter (escapes in the string in arg 1 a single quote by adding a backslash in front and a backslash by doubling it - in that order) - only used in advanced cases