Formula operator: XIH Helper Functions
XIH Helper functions
Main Idea | While regular functions in the Formula Operator allow only calculations within the row, XIH functions enable calculations beyond.
|
Wiki | Most of the functions can be found in the XIMES Wiki (German): |
See also |
TIS Table Info
Getting started | The name of TIS tables in the database is normally not the same as in TIS table directory. To get the database name you have to use the XIH function. |
Preparation | Create a data node without reference to another node. Add the operation "Create Table" with one column and one row. Header and input for this table are temporary and don't influence the operation itself. |
Formula Operator | Add an operation to the node and choose formula operator with an Text as result column type. XIH.TISTable("string TISTablePath", "string TISTableName")
|
Result |
|
Additional possibilities | You can also get the information for one or more TIS tables
|
Generating Passwords
Getting started | XIH allows generating random passwords with given parameters, such as length, necessary characters. |
Function | string XIH.Password(int length) string XIH.Password(int seed, string alphabet, int length) string XIH.Password(int seed, int length) string XIH.Password(int seed, string alphabet, int length) length (int): length of the password alphabet (string): characters that should be included in the password (eg: 123, abc, ABC, !"§, …) seed (int): number that defines which password should be generated |
Encryption and Decryption
Getting started | This function encrypts a text string. The result can be decrypted later again. |
Function | string XIH.Encrypt(string text) string XIH.Decrypt(string encryptedText) |
Getting database info
Getting started | This XIH functions informs you, which database (Oracle, MSSQL, POSTGRES) is actually used by the system which again |
Function |
|
Check Email Address
Getting started | This XIH functions checks whether an email address is formally correct. |
Function | bool XIH.IsValidEmail(string emailAddress) |
User Name of Account User
Getting started | If a higher number of users get access to the [TIS]Board Account Users are generated. The normally get activated only if they are logged in to the [TIS]Board. If you need to find out the account user for a calculation this XIH function is essential. |
Function | string XIH.AccountId() |
Syntax | Note that you must omit the brackets in the Formula operator (row-by-row) (4.0) = Formeloperator: |
Single-Sign-On Parameter
Getting started | In context of a single sign on (SSO) a list of parameter, value tuples are transferred. Note that you must define a Logon-Event on the TIS-Board, which recalculates a data node using this function. Later the parameters are not available anymore. See also Event Handling with Portlets. |
Function | string XIH.GetLogOnParam(string name, string default_value) |
XIH functions (TIS 5.0 and up)
Please note: XIH functions do NOT allow # in the notation of variables. Example: write "Identifier" instead of "#Identifier#".
Name | Description / Examples | ||
|---|---|---|---|
EqualsNext | XIH.EqualsNext(string colcaption) | XIH.EqualsNext("From") Checks if a value in the column "From" is identical with the value in the next row of the same column. | bool |
XIH.EqualsNext(string colcaption, int offset) | XIH.EqualsNext("From", 4)Checks if a value in the column "From" is identical with the value of the same column 4 rows later. (Compares row 1 with row 5, row 2 with row 6, and so on) | bool | |
EqualsPrev | XIH.EqualsPrev(string colcaption) | XIH.EqualsPrev("From")Checks if a value in the column "From" is identical with the value in the previous row of the same column. | bool |
XIH.EqualsPrev(string colcaption, int offset) | XIH.EqualsPrev("From", 4)Checks if a value in the column "From" is identical with the value of the same column 4 rows earlier. (compares row 5 with row 1, row 6 with row 2, and so on) | bool | |
EqualsFirst | XIH.EqualsFirst(string colcaption) | XIH.EqualsFirst("From")Checks if a value in the column "From" is identical with the value in the first row of the same column. | bool |
EqualsLast | XIH.EqualsLast(string colcaption) | XIH.EqualsLast ("From") Checks if a value in the column "From" is identical with the value in the last row of the same column. |
|
IsFirst | XIH.IsFirst(null) | XIH.IsFirst(null) returns 'true' for the first row | bool |
IsLast | XIH.IsLast(null) | XIH.IsLast(null) returns 'true' for the last row |
|
Get | XIH.Get<TYPE>(string colcaption) XIH.Get<TYPE>(string colcaption, int offset) XIH.Get<TYPE>(int colIndex) XIH.Get<TYPE>(int colIndex, int offset) | Returns the value of the specified column and converts it to the specified type. | |
GetDouble | XIH.GetDouble(string colcaption) XIH.GetDouble(string colcaption, int offset) XIH.GetDouble(int colIndex) XIH.GetDouble(int colIndex, int offset) | XIH.GetDouble("Umsatz") Returns the value of column "Umsatz" as Double
Returns the value of column 0 = first column as Double
Returns the value of the same column offset by 2 rows as Double. Returns 0 after reaching the end of the table due to the offset.. Also converts Int32, Bool | double |
GetInt | XIH.GetInt(string colcaption) XIH.GetInt(string colcaption, int offset) XIH.GetInt(int colIndex) XIH.GetInt(int colIndex, int offset) | XIH.GetInt("Umsatz") Returns the value of column "Umsatz" as Integer XIH.GetInt("Umsatz", 2) Returns the value of the same column offset by 2 rows as Integer. Returns 0 after reaching the end of the table due to the offset. Also converts Double, Bool. Returns an even number if a values lies exactly between two integers (e.g., 4.5 turns to 4 and 5.5 to 6). | Int32 |
GetLong | XIH.GetLong(string colcaption) XIH.GetLong(string colcaption, long offset) XIH.GetLong(int colIndex) XIH.GetLong(int colIndex, long offset) | Use this function for numbers greater than 2.1 billion (max. of 32-Bit number, see also MSDN) XIH.GetLong("Huge_Integer") Returns the value of the column "Huge_Integer" as Long
Returns the value offset by 2 rows as Long. Returns 0 after reaching the end of the table due to the offset. | Int64 |
GetString | XIH.GetString(string colcaption) XIH.GetString(string colcaption, int offset) XIH.GetString(int colIndex) XIH.GetString(int colIndex, int offset) | XIH.GetString("Id") Returns the value of column "Id" as string. XIH.GetString("Id", 2) Returns the value offset by 2 rows as string. Returns an empty string after reaching the end of the table due to the offset. Converts all data types | String |
GetDateTime | XIH.GetDateTime(string colcaption) XIH.GetDateTime(string colcaption, int offset) XIH.GetDateTime(int colIndex) XIH.GetDateTime(int colIndex, int offset) | XIH.GetDateTime("starttime") Returns the date value of the column "starttime" XIH.GetDateTime("starttime", 2)Returns the date value offset by 2 rows. Returns default value of DateTime after reaching the end of the table due to the offset. (01.01.0001 00:00:00) converts nothing For the case you want the current datetime use e.g. XIH.RowNumber() >= (XIH.Rows-2) ? System.DateTime.Now : XIH.GetDateTime("starttime",2) | DateTime |
GetBool | XIH.GetBool(string colcaption) XIH.GetBool(string colcaption, int offset) XIH.GetBool(int colIndex) XIH.GetBool(int colIndex, int offset) | XIH.GetBool("Holiday") Returns the boolean value of the column "Holiday".
XIH.GetBool("Holiday", 2) Returns the boolean value of the column "Holiday" offset by 2 rows. Returns 'false' after reaching the end of the table due to the offset. Converts Double, Int32, String | bool |
SetDouble | XIH.SetDouble(string colcaption, double value) XIH.SetDouble(string colcaption, int offset, double value) XIH.SetDouble(int colIndex, double value) XIH.SetDouble(int colIndex, int offset, double value) | XIH.SetDouble("Sales", #Value# * 100.00)Writes into the column "Sales" the value of column "Value" multiplied by 100.
XIH.SetDouble("Sales", 2, #Value#)Writes the current value of column "Value" offset by 2 rows into the column "Sales". Returns 'false' after reaching the end of the table due to the offset. | bool |
SetInt | XIH.SetInt(string colcaption, int value) XIH.SetInt(string colcaption, int offset, int value) XIH.SetInt(int colIndex, int value) XIH.SetInt(int colIndex, int offset, int value) | XIH.SetInt("Sales", #Value# * 100.00)Writes into the column "Sales" the value of column "Value" multiplied by 100. XIH.SetInt("Sales", 2, #Value#)Writes the current value of column "Value" offset by 2 rows into the column "Sales". Returns 'false' after reaching the end of the table due to the offset. | bool |
SetLong | XIH.SetLong(string colcaption, long value) XIH.SetLong(string colcaption, int offset, long value) XIH.SetLong(int colIndex, long value) XIH.SetLong(int colIndex, int offset, long value) | XIH.SetLong("Sales", #Value#* 100.00)Writes into the column "Sales" the value of column "Value" multiplied by 100. XIH.SetLong("Sales", 2, #Value#)Writes the current value of column "Value" offset by 2 rows into the column "Sales". Returns 'false' after reaching the end of the table due to the offset. | bool |
SetString | XIH.SetString(string colcaption, string value) XIH.SetString(string colcaption, int offset, string value) XIH.SetString(int colIndex, string value) XIH.SetString(int colIndex, int offset, string value) | XIH.SetString("Id", #Key#)Writes into column "Id" the value of column "Key".
XIH.SetString(0, #Key#) Writes into column 0 = first column the value of column "Key".
XIH.SetString("Id", 2, #Key#)Writes into column "Id" the current value of column "Key" offset by 2 rows. Returns 'false' after reaching the end of the table due to the offset. | bool |
SetDateTime | XIH.SetDateTime(string colcaption, DateTime value) XIH.SetDateTime(string colcaption, int offset, DateTime value) XIH.SetDateTime(int colIndex, DateTime value) XIH.SetDateTime(int colIndex, int offset, DateTime value) | Writes the value as DateTime, offset by the chosen offset value. | bool |
SetBool | XIH.SetBool(string colcaption, bool value) XIH.SetBool(string colcaption, int offset, bool value) XIH.SetBool(int colIndex, bool value) XIH.SetBool(int colIndex, int offset, bool value) | Writes the value as Bool, offset by the chosen offset value. | bool |
IsNull | XIH.IsNull(string colcaption, int value) XIH.IsNull(string colcaption, int offset, int value) | Checks if the value in the column is Zero. | bool |
Count | XIH.Count(string colcaption, string value) | Checks how often a value occurs. Example: if column A contains "Column A 1x2x3x4x5", the result will be 5. XIH.Count("A", "x") Also supports "TAB” and “CR“ as input values (Tab = tabulator, CR = carriage return) | int |
HasRole | XIH.HasRole(string roleName) | XIH.HasRole("PowerUser")Checks if the TIS user has the specified role. | bool |
GetParam | XIH.GetParam(string paramName) | XIH.GetParam("parStartTime")Identifies the current value of the TIS-Parameters parStartTime. Note: must be cast to the desired data type, e.g. | object |
GetParamCaption | XIH.GetParamCaption(string paramName) | Returns the caption of the specified TIS parameter. | string |
GetParamDescription | XIH.GetParamDescription(string paramName) | Returns the description of the specified TIS parameter. | string |
SetParam | XIH.SetParam(string paramName, object value) | XIH.SetParam("parStartTime", new System.DateTime.Parse("01.01.2012")Sets the value of TIS-Parameter parStartTime to 01.01.2012. | void |
ProjectName | XIH.ProjectName | Identifies the name of the current project as text. | string |
ProjectID | XIH.ProjectID | Internal ID of the current project as integer. (Note: may differ for each project copy if template project) | int |
FolderName | XIH.FolderName | Identifies the name of the current folder as text. | string |
NodeName | XIH.NodeName | Identifies the name of the current data node as text. | string |
ClientName | XIH.ClientName | Identifies the name of the current client as text. | string |
DatabaseInfo | XIH.DatabaseInfo | Identifies the name of the current data base, the data base server, and the data base type as text. | string |
UserName | XIH.UserName | Identifies the user currently working on a TIS-Project. | string |
AccountId | XIH.AccountId | Identifies the employee currently working on a TIS-Project. (Needs EmployeeLogon or AnonymousLogon with UserPool) | string |
ColumnIndex | XIH.ColumnIndex(string colcaption) | (5.4 and up) Determines the number of the column with a certain name. If the name is not found, -1 will be returned. XIH.ColumnIndex(“Column 001“) != -1 checks if a column exists at all. | int |
ColumnCaption | XIH.ColumnCaption(Int Nr) | (5.4. and up) Returns the column caption as String. If the index does not fit then it will produce an error message. The first column has the number 0!
| string |
Columns | XIH.Columns | Returns number of columns as integer (excluding resulting column) with start 1 | int |
Rows | XIH.Rows | Returns number of rows as integer (former command rows from interflex) | int |
RowNumber | XIH.RowNumber | Returns number of the current row, starts with 0 (former command rowNumber from interflex) | int |
TISTable | XIH.TISTable(string tisTablePath, string tisTableName) XIH.TISTable(string tisTablePathTableName) | (5.5. and up) Returns the name of the internal table in the database system for a given TIS-Table (path and name or combined). Allows to dynamically generate accesses or to allow access for third parties directly to the database system. | string |
IsNumber | XIH.IsNumber(string numberAsText) XIH.IsNumber(string numberAsText, NumberStyles numberStyle [2], CultureInfo culture [3]) | (5.5 and up) Checks if the string is a number. | bool |
CleanString | XIH.CleanString(string text, string charactersToBeReplaced) XIH.CleanString(string text, string charactersToBeReplaced, bool trim) XIH.CleanString(string text, string charactersToBeReplaced, char replaceWith) XIH.CleanString(string text, string charactersToBeReplaced, char replaceWith, bool trim) | (5.5 and up) Deletes/replaces the characters within a text and eliminates (truncates) blanks at the beginning and end of the text. Examples:
| string |
GetPart | XIH.GetPart(string text, string delimiter, int i) | (5.5. and up) Returns the i-th substring of a delimited string text. Please note: i=0 returns the substring up to the first delimiter. | string |
Set | XIH.Set("Columnname", null) | Sets column name to zero. It is important to use Set and not SetDouble etc. | bool |
Encrypt | XIH.Encrypt(string text) | Returns an encrypted text. | string |
Decrypt | XIH.Decrypt(string text) | Decrypts a text which has been encrypted with | string |
GetHash | XIH.GetHash(string text) | Generates a hash of the specified text using the bcrypt standard. | string |
Examples
Same ID: Calculate time to previous | XIH.IsFirst(null) == false &&
XIH.GetString("Identifier",-1) == #Identifier# ?
(XIH.GetDateTime("To",-1) - #From#).TotalHours
: 0
|
Overwrite data | <condition> ? <return_value_if_true> : <return_value_if_false> #Course number# == 0 ? XIH.SetInt("Course number", XIH.GetInt("Course number", -1)) : true
|
Do week day, time or ID change with regard to the previous row? | XIH.EqualsPrev("Identifier") == false ||
XIH.EqualsPrev("Weekday") == false ||
XIH.EqualsPrev("Timeofday") == false ? 1 : (XIH.GetInt("Count",-1) + 1)
|
Do week day, time or ID change with regard to the following row? | XIH.EqualsNext("Identifier") == false || |
Several calculations and help with looking for errors | #Quantity# == 0 ? 0 :
((XIH.SetDouble("Pause in h", #Pause in h#/#Quantity#) ? 0:9) +
(XIH.SetDouble("Netlength in h", #Nettolength in h#/#Quantity#) ? 0:99) +
(XIH.SetDouble("Net/Gross in %", #Net/Gross in %#/#Quantity#) ? 0:999) +
(XIH.SetDouble("Length in h", #Length in h#/#Quantity#) ? 0:9999))
If errors occur, a value unequal to zero will be returned. Depending on the variables, 9, 99 ... |
Snippets
Merge Data=0 Records | Delete |
| XIH.EqualsPrev("Id") && #Data# == 0 && XIH.GetDouble("Data", - 1) == 0 ?
(XIH.SetDateTime("From", XIH.GetDateTime("From",-1))
&& XIH.SetDouble("Delete",-1,1) ? 0:0 ) : 0
|
Categories - eg 000-050; 050-100 ... | Text | Creates categories with three digits out of values with Steps (Variable available in formula operator) | #Data# == 0 ? "-" :
((System.Math.Floor((#Data# - 0.00000001)/#Step#)*#Step#)
.ToString("000") + "-" +
((System.Math.Floor((#Data# - 0.00000001)/#Step#)+1)*#Step#)
.ToString("000"))
|
Conversion of "", "8h20", "12h30" ... into numbers 0.00, 8.33, 12.5 and standardisation of text into "00h00", "08h20", "12h30" | Double & change of original value | Goes through, changes original text, and calculates the number | (XIH.SetString("Column-name", #Column-name#.Trim()) ? 0:0)
+ (XIH.SetString("Column-name",
XIH.IsNull("Column-name") ||
#Column-name# == "" ? "00h00" :#Column-name# ) ? 0:0)
+ (XIH.SetString("Column-name",
(#Column-name#.Length == 4 ? "0" : "") +#Column-name#) ? 0:0)
+ System.Convert.ToInt32(#Column-name#.Substring(0,2))
+ System.Convert.ToInt32(#Column-name#.Substring(3,2))/60.0
|
Access table for programmers
Type | Description |
|---|---|
XimesData.GenTable | Object Table |
XimesData.GenRow2 | Object Table row |
XimesData.ColumnType | Object Table column |
Function/Property | Resulting value | Description |
|---|---|---|
dataRow.Parent | XimesData.GenTable | Returns the row of the current table. |
dataRow.Parent[i] | XimesData.GenRow2 | Returns the i-th row of the current table. |
dataRow.Parent[i][j] | object | Returns the value of column j in row i. Since the values are always of the type object, they need to be converted to the right type for further processing. System.Convert.ToDouble(dataRow.Parent[0][3]) |
dataRow.Parent.CountCols | Number | Returns the number (quantity) of the columns, including newly created columns. |