Page tree
Skip to end of metadata
Go to start of metadata


XIH Helper functions

Main Idea

While regular functions in the Formula Operator allow only calculations within the row, XIH functions enable calculations beyond.
These include:

  • calculations with other rows
  • setting values in other columns of the same or a different row
  • setting parameters
  • getting information about TIS Tables, the current data node and users

Wiki

Most of the functions can be found in the XIMES Wiki (German):
http://wiki.ximes.com/index.php?title=TIS:Formeloperator_(zeilenweise)#tab=Hilfsfunktion_XIH
A few other XIH functions are described below.

See alsoFormula operator (row-by-row) (4.0) = Formeloperator 

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.
The input for the TIS table information is:

XIH.TISTable("string TISTablePath", "string TISTableName") 

Result

Additional possibilities

You can also get the information for one or more TIS tables

  • by using TIS Parameter for the path and/or table names.
  • or by using the operator "Read TIS Table information" as parent operation

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
helps you to properly build SQL statements in the formula operator that may be used Importing data from SQL sources 3.1 via Parameters

Function

(XIH.DatabaseInfo).IndexOf("(ORA") > 0 ... Oracle
(XIH.DatabaseInfo).IndexOf("(PGSQL") > 0 ... PostgresSQL
otherwise it is MSSQL

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()
SyntaxNote that you must omit the brackets in the Formula operator (row-by-row) (4.0) = Formeloperator: XIH.AccountId

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#".

 

NameFunction  Description / ExamplesResult  
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.
The last row always has the value 'false'.

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.
The first row always has the value 'false'.

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.
The first row always has the value 'true'.

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. 
The last row always has the value 'true'.

 
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


XIH.GetDouble(0) 

Returns the value of column 0 = first column as Double


XIH.GetDouble("Umsatz", 2)

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


XIH.GetLong("Huge_Integer", 2)

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. (string)XIH.GetParam("par1")

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!

(warning) Note that column captions are cached in the operator and only updated when editing the operator.

string
Columns
XIH.Columns
Returns number of columns as integer (excluding resulting column) with start 1int
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:

  • XIH.CleanString("Bedarf_XYZ1", "XYZ") leads to "Bedarf_1"
  •  the following command is useful to delete special characters "!,;. _-#~+*?=}{[]!§$%&/|><"
  • XIH.CleanString(#Area#,"!,;. _-#~+*?=}{[]!§$%&/|><", '_') replaces special characters.
    Please note: be careful with the spelling of the characters to be replaced.
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 XIH.Encrypt string
GetHash
XIH.GetHash(string text)
Generates a hash of the specified text using the bcrypt standard.string

 

Examples

 

Key word Formula / Example  

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
  • if the value of #course number# in the current row is 0 then take the value of the previous row

  • creates a boolean type column

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 || 
XIH.EqualsNext("Weekday") == false ||
XIH.EqualsNext("Timeofday") == false ? 1 : (XIH.GetInt("Count",-1) + 1)
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

 

Key word Resulting column Description  Code  
Merge Data=0 RecordsDelete
  • goes through rows and expands From-To
  • afterwards delete all rows with Delete = 1
 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 ...TextCreates 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 valueGoes 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

 

TypeDescription
XimesData.GenTable
Object Table
XimesData.GenRow2
Object Table row
XimesData.ColumnType
Object Table column

 

Function/PropertyResulting valueDescription
dataRow.Parent
XimesData.GenTableReturns the row of the current table.
dataRow.Parent[i]
XimesData.GenRow2Returns the i-th row of the current table.
The first row has index 0.
dataRow.Parent[i][j]
objectReturns the value of column j in row i.
Indices of rows and columns always start with 0.

Since the values are always of the type object, they need to be converted to the right type for further processing.

Example: Access to decimal number in 4th column (columns start with index 0) in the first row.
System.Convert.ToDouble(dataRow.Parent[0][3])
dataRow.Parent.CountCols
NumberReturns the number (quantity) of the columns, including newly created columns.
  • No labels