Value Range Functions (CO, PF)

ABS: determines the absolute value of a number.

• To determine the absolute value of a number.

• The ABS function provides the absolute value of a number. The absolute value of a number is its value without the +/- sign.

• Syntax: ABS(Operand)

• The expression ABS(-2) provides the value 2.

ATOI: conversion of a string into a numeric value

• Converting a string (type X) to a numeric value.

• ATOI (<Alphanumerisches DI, Variablen>)

• If the content of the string cannot be interpreted as a number, the result of ATOI is the value 0.

CONST: constants in value ranges

• Fetching a constant name, possibly on a language-dependent basis

• CONST (Language, Constant ID)
  • CONST: Function
  • Language: @19, 0000, 1000
  • Constant ID: ID number of the constant

Example

• The constant name is automatically trimmed.

EXIST: existence check where there are no relationships

• If it is not possible to carry out a check using relationships, the existence of a data item in a data table can still be checked using this function.
• If a corresponding record does not exist in the specified data table, any user entry is rejected with the message Please check input .

• EXIST (DI, DT)
  • EXIST: Function
  • DI: target DI which is to be checked (usually a DI with VR)
  • DT: the data table in which the existence of a record is to be checked.

• The EXIST function to check for pre-existing records.

• EXIST function to check for non-existent records.

• The check
  • can be done for IDs which are compiled from a DI;
  • does not have to check ID data items themselves, as these are checked via the relationship function;
  • can only access IDs with DB type = X.
  • cannot access compound IDs.

EXP: exponentiates numbers

• Exponentiate Numbers.

• The EXP function provides exponentiated values of a number.

• Syntax: EXP(Operand 1;Operand 2)
  • Operand 1 = Basis
  • Operand 2 = Exponent

• The expression EXP(2;3) provides the value 8.
• expression EXP(4;0.5) provides the value 2.
• expression EXP(-3;3) provides the value -27.

• The exponent cannot be a rational number. Fractions such as ½ must be replaced by a value such as 0.5.
• In the case of roots of positive numbers, only the positive number is provided. The result of EXP(4;0.5), e.g., is +2 and –2. The software provides the value +2.
• Calculations with a negative basis are only permitted with whole-number exponents. Calculations with the EXP function should therefore only be carried out after conversion into an absolute value.

Expand: returns the value of a variable

• Read out the current value of a variable

• Possible variables are "@xxx", "@Gxxx" , "@Dxxx", and "@Uxxx"

FMTDATE: conversion of a date DI into a string

• Converting a date DI to a string, for example to include this in a character string

• FMT(date DI, <Format-ID>)
  • Date DI: Date data item with DB type = N4
  • Format ID:
    • ID number of the format as a character string (with "") or
    • "" (blank string): Output in the default date format for the user interface ( format datecolumn) error

FMT: conversion of a numeric DI into a string

• Converting a numeric DI to a string, so that it can, e.g., be included in a string

• FMT(DI, <Formatstring>)
• Format string: &bdquo;[-][W][.P][e|E]"
  • -: left-aligned (default: right)
  • W: min. width
  • P: max. number decimal place
  • E: Exponential representation

• Format --> result, value: 298,123
  • &bdquo;8.2" → &bdquo; 298.12"
  • &bdquo;-10" → &bdquo;298.12 "
  • &bdquo;.1" → &bdquo;298.1"

• All numeric DB types are permitted.
• Corrsponds to the UNIX function man fprintf.

FR: appends a string

• Fetching an operand value and add in strings before and/or after the fetched value

• FR(<Zeichenkette>, <DIxxxxxx oder @xx>, <Zeichenkette>)
• Operands can be data items and variables.
• The character strings
  • can be of any length.
  • are trimmed beforehand.

• This function cannot be used with I-texts.

IIFS: determines values in accordance with a condition

• Combining strings in different ways depending on conditions.

• IIFS (Condition, String1, String2)
  • Condition: logical expression, the result of which is either TRUE or FALSE.
  • String1: The value assumed by the testing DI if the condition is met.
  • String2: The value assumed by the testing DI if the condition is not met.

• The number of IIFS conditions in a value range construction has been increased to 15. Up to 15 IIFS conditions can be created.
• Applies only for X, AL fields, but not for numerical ones.

LENGTH: determines the number of characters of a character string

• Determine Number of Characters of a Character String

• The LENGTH value range functions provides the number of characters in a string.
• Blanks at the start and end are not counted.

• Syntax: LENGTH(Operand)

• The expression LENGTH("12345678") provides the value 8.

• The function can only be applied to strings ( DB type =X, Class =0). It cannot be applied to I texts or continuous texts.

NB: input check for alpha data item according to numeric rules (from, to)

• An alphanumerical data item is converted into a number.
• Subsequently it will be verified whether this number lies between from and to .

• NB (data item, from, to).

• Input is rejected with the Please check input error message when the result of the verification
  • is not a valid number, or
  • the number is not within the from/to range.

NCHILD: outputs the direct or indirect child records

• In 39 it is carried out via Python API

• This function returns the number of immediate child records from the first child data area, in N4 format.

• NCHILD()

NORMSINV: quantiles of standard normal distribution

• The NORMSINV function provides the quantiles of standard normal distribution.

• NORMSINV (Operand)
  • The operand is the probability belonging to standard normal distribution.
  • An operand <0 or >1 is not permitted.

• The expression NORMSINV (0.75) provides the value 0.674490.

OBS: overwrites object protection

• Overwriting object protection data items with the value specified.

• OBS<OS-Teil > &gt; (value for part1, value for part2, ...)
  • OBS: Function
  • OS part:
    • 1: overwrites (rwd owner, rwd group, rwd any)
      • Example: OBS1 ("r--","r--","r--")
      • Result: after they are exported, the records can no longer be edited or deleted
    • 2: overwrites (system user [1-4], system user [5-8])
      • Example: OBS2 ("z", " ")
      • Result:
        • after they are exported, the system user for the records is "z".
        • in the corresponding modules, records can be searched for "without z". This means that records with the user "z" are not displayed, and they can no longer be edited or deleted.

• This function
  • can only be used when exporting from the PLANTA software;
  • is assigned to virtual with DB type X and DB length = 2 in the data tables in question.
• The data area which contains the DIs with these VR functions may not contain data fields in any other data tables.
• The records are changed directly within the database. This ensures that
  • the object protection data fields which are to be changed are only updated after a new search;
  • any available exits are not executed.

PCT: calculation of a deviation in percent

• Calculating the percentage difference between two data items.

• PCT<DBTyp> (DI-1, DI-2)
  • PCT: Function
  • DB type:
    • DB type of the data items specified in parentheses.
    • Possible values: N2, N4, N8
  • DI-1: e.g. actual value
  • DI-2: e.g. target value

• This function can only be used with DIs which have the same DB type.
• If a % deviation is to be applied between DIs with different DB types, then
  • a DI with VR type =CS must be converted into one of the two DB types.
  • this value calculation must necessarily be carried out with VR type =CO.

PERIOD: determines a period

• Determining the start or end date of a period relative to a date.

• Function for determining a period:
  • Result:
    • The date of the day which is the first or last day of the period determined by the parameters input for the function.
  • Possible application
    • Start and end dates for date bars
    • Start and end dates for date bars
    • As dynamic variables
    • As dynamic variable in value ranges

• Starting from a given date, PPMS is to determine the start or end date of the period which contains this date, or of a preceding/following period:
  • calendar week containing the date
  • nth week before that (not implemented)
  • nth week after that (not implemented)
  • month containing the date
  • nth month before that
  • nth month after that
  • quarter containing the date
  • nth quarter before that
  • nth quarter after that
  • year containing the date
  • nth year before that
  • nth year after that

• PERIOD (<Datum>, <Raster>, <Terminlage>, <Abstand>)
  • Date
    • The date relative to which the period is to be determined.
    • This parameter can be a date data item or a dynamic, global, user or system variable which contains a date.
  • Grid
    • Defines the period of time for which the start or end date is to be found.
    • Values:
      • 3: week
      • 4: month
      • 5: quarter
      • 6: year
  • Date position
    • Values:
      • 0: the start date is determined
      • 1: the end date is determined
    • This parameter can be a data item with DB type = N2, or user variable, or a dynamic, global variable, containing the value 0 or 1.
  • Interval
    • Defines which period is determined before or after.
    • Values:
      • 0: the period containing the date itself
      • < 0: number of equal time periods before that
      • > 0: number of equal time periods after that
    • This parameter can either be a data item with DB type= N2, or user variable, a dynamic, global variable, containing an integer.

RELPOS: outputs the position of a record

• This function returns the relative position of a record in the data table, in N4 format.

• The function does not work in modules with structural sorting.

ROUND: rounds numbers

• The ROUND function rounds a number to a definable number of places, for example the expression ROUND(12,3456;2) provides the value 12.35.

• Syntax: ROUND(<Wert>;<Anzahl Dezimalstellen>).

STR: truncates a string

• Fetching the value an operand and to truncate a string at any required point.

• STR(<DIxxxxxx oder @xx>, start position, length)
  • Operands can be data items and variables.
  • Start position
    • Position within the operand string from which it is to be truncated.
    • From which the first operand position is counted incrementally.
  • Length
    • Number of characters cut off from the start position
    • Maximum length is the length of the operand

SYSTEM: opens the program from the software

• This function may no longer be used.
• This function enables another program to be opened from within the program.
• The program is executed synchronously, i.e. the system waits until the program which was called terminates.

• SYSTEM(DI<DI-Nummer>)
  • DI<Nummer>: the DI receives the command like for a shell input &bdquo;cp file1 file2 1&gt;&2 2&gt; cp.err$$".
  • To check only for errors, the comparison operand is appended after the parentheses e.g. SYSTEM( DI<DI-Nummer>) 0

• The SYSTEM function returns Zero upon success; it should therefore be linked to a DI of the internal Integer type. To specify which program is to be executed requires a text DI, which holds the program name and options.
• If this value range is linked to a Y/N-field with the name Error , the status will be displayed as a text.
• The new process adopts the standard input and output of PPMS, and is therefore directly linked to the PPMS client. Outputs would be sent to the client, and standard input, output, and errors must therefore be rerouted.
• The return value from SYSTEM corresponds with the return status (integer) of the program which is called-up.
• In order to distinguish the files produced by rerouting of the output, "$$" can be inserted into the file name. The two signs are replaced by the process number at execution.

TRIM: removes blanks

• Removing any blanks before and/or after a string.

• TRIM (Operand)
  • TRIM: Function
  • Operand: DI, variable or constant ID number

VH: number of records in a child data table

• This function displays the number of records for a superior (parent) record which exist in a child data table. This does not require that the module contains a data area for this data table.

• VH (DT, index, path, DI)
  • DT: target data table, for which the usage frequency is to be determined
  • Index: specified data table in the ID of the target DT
  • Path: dependent on "variable name" from relation
    • 0: e.g. "ID425"
    • 1: e.g. "ID425A"
    • 2: e.g. "ID425B"
    • 3: e.g. "ID425C"
  • DI: identifying DI in the data table of the superior object, using which the child object is to be searched for.

• This function can be used with the following VR types: CS, CO and CL.

• If the value range is constructed incorrectly, the Database error -52 error message will be displayed when searching in the module

IKV: EXCEL function for financial mathematical calculations

• Using the EXCEL function IKV as value range function in PPMS to realize the financial mathematical calculations with PPMS-Customizing.

• IKV (value1, value2, value3,....,value12, estimated value)
• Algorithm: MS Excel, function IKV
• Input parameter
  • Value1 to value12
    • Values of payment series as costs (negative) and benefits (positive) in sequence of years.
    • There must be at least one negative and one positive value.
    • Permissible operators for values 1-12:
      • DIxxxxxx (numerical data items, DB type = N8)
      • @DIxxx (dyn. variables on numeric data items, DB type = N8
      • numeric constants, e.g. 0.0
    • If less than 12 values are required, the remaining values can be specified constantly with 0.0 in the value range.
  • Estimated value
    • See EXCEL help
    • 0, if not used. The system then adopts &bdquo;10%" internally. For this, see EXCEL function
  • Return value
    • Result of EXCEL-IKV
    • 999999.99, if calculation cannot be carried out (in Excel: #NUMBER)

• IKV(DI299101, DI299102, DI299103, DI299104, DI299105, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)

• The value range should not be used with value range type CO because then a recalculation of the IKV takes place after the entry of every field involved in the formula, which can take time because of the iteration process used.

Distance and Date Calculation

• Calculating the data or periods between two dates using the calendar, stored in PPMS

Details: Determine the number of the working days

• Calculate the number of working days between two dates
  • In order to calculate the number of the working days between two dates use the following formula:
    • WD_BETWEEN([calendar],[date1],[date2])

• Number of working days between today and today + 100 days in the calendar "BC"
• Result:
  • WD_BETWEEN("BK",@15,@15+100)

• Number of working days between 01/01/1970 and today’s date in the calendar "BC"
• Result:
  • WD_BETWEEN("BK",0,@15)

• Determine the date using working days
  • For calculation using the number of the working days use the following formula:
    • WD_ TIMEINTERVAL ([calendar],[start date],[number])

• Today’s date + 5 working days stored in the „BC"calendar.
• Result:
  • WD_TIMEINTERVAL("BK",@15,5)

• Today’s date + number of the working days from DI000002+1 stored in the calendar from DI000001.
• Result:
  • WD_TIMEINTERVAL(DI000001,@15,(DI000002+1))