Formular language

With formulas, rules and actions up to complex business processes can be anchored in the data sheets.

There are two different forms of formulas in REWOO Scope:

Allocations:

= <expression>

Conditions:

? <conditon>

with action

? <condition> : <action>

or with multiple actions

? <expression> : <action> ; <action> ; <action>

Introduction to the Formula Language

The following chapter provides a basic insight into the possibilities of the formula language using simple practical examples.

Data fields that are created in this way for an aspect type in the Form Designer and can then be referenced have the respective data field type in brackets below.

Field name (type: String)

The steps are listed in their sequence under Usage.

Note that formulas for data fields can be introduced either with an = for assignments or with a ? for conditions. References to other data fields are introduced with a preceding @.

= DAY ( @Party on - TODAY )

Date

Output date in a string field

Problem: The date of a date field is to be converted into a string field for further processing.
Prerequisite: date field, string field
Solution:

1. Reference of the date field
2. DDate format as text in square brackets (type: string)

   = @Date[DD.MM.YYYY]

Explanations: The formula returns the date in the format specified in square brackets as a string. The following placeholders are available: D (day), M (month), Y (year), E (day of the week). Special characters like dot, space or slash can be inserted as desired. For higher levels of detail, the respective placeholder is placed several times in succession, e.g. M = 1, MM = 01, MMM = Jan, MMMM = January.

Date functions

Story: In how many days will the celebration take place?
Usage:

1. today is 26.09.2015
2. Celebration on (type: date)

   15.10.2015

3. Number of days until the celebration (Type: Number)

   = DAY ( @celebration on - TODAY )

4. Result: 20

Remark:

• Instead of DAY, it could also be MONTH or YEAR.
• Also important: TODAY always refers to the current date and is therefore evaluated every day.
• If you want to add a certain number of days to a date, a number is given the unit and added: +5 day

Numbers

Round numbers

Story: What is my party spending per capita, rounded to whole euro amounts?
Usage:

1. Number of guests (Type: Number)

   15

2. Expenditure (Type: Number)

   184,32 €

3. Expenditure per head (rounded)) (Type: Number)

   = ROUND ( ( @expenditure / @number of guests ) | 0 )

4. Result: 12 €

Remark:

• The number of decimal places to which rounding is performed can only be whole positive numbers, including 0. Indicated after the | symbol.

Output numbers in a string field

Problem: An ID is to be used for further processing in a string field. The ID always has 5 digits, e.g. 00032 or 01234.
Prerequisite: Number field, String field
Solution:

1. Reference of the number field
2. Number format in square brackets

   ={Invoice }@ID[5]

Explanations: The formula returns the number in the format specified in square brackets as a string. Digits before and after the decimal point can be specified. Unused digits are filled with zeros. [5.2] formats the number with 5 digits before and 2 digits after the decimal point, [4] with 4 digits before and any digits after the decimal point, [.3] with 3 digits after and any digits before the decimal point.

Minimum and maximum

Story: What is the largest single donation?
Usage:

1. For each guest, a form with a "firm pledge" field (type: Boolean) exists on an aspect sublevel. Out of 15 guests, 12 have pledged with a check mark and entered an amount in their "donation" field.
2. largest donation (Type: Number)

   = MAX ( CHILDREN@Donation  )

Remark:

• MAX returns the highest value and MIN returns the lowest value.
• Fields of type Date and Number can be referenced..

Divide and number rest

Story: A canapé platter has 26 pieces. To divide them fairly, calculate how many pieces each guest gets and how many are left over.
Usage:

1. Number of guests (Type: Number)

   8

2. Number of appetizers (Type: Number)

   26

3. Appetizers per head (Type: Number)

   = @number of appetizers DIV @number of guests

   Result: 3
4. Number of remaining appetizers (Type: Number)

   = @number of appetizers MOD @number of guests

   Result: 2

Remark:

• Both operations refer to integers and in turn return only integers.

Choice

Story: How many guests selected accept in their invitation, how many selected decline?
Usage:

1. For each guest there is a subform on which a field "Status" (Type: Choice) is created. The choices "Acceptance" and "Rejection" are stored there.
2. Number of acceptances (Type: Number)

   = COUNTIF ( CHILDREN | @Status = {Acceptance} )

3. Number of rejections (Type: Number)

   = COUNTIF ( CHILDREN | @Status = {Rejection} )

Remark:

• The COUNTIF function counts the number of forms for which the condition is true. This can be used to read how often a particular value is set in choice fields on child forms.
• A setting for the Choice field can be stored as the default value, so that when the form is created, the choice defaults to Reject.
• Instead of using the exact choice value, a set value can also be queried by its position in the choice list. This is possible with @Choice[index], where the count starts with 0 for the first option (e.g. @Choice[1] returns true if the second possible choice of the choice field is selected). The advantage of this method is that it is independent of the terms of the choice; the disadvantage is that it depends on the order and number of choices.

Emails

Story: When was the last email sent about a support case?
Usage:

1. For each support case there is a form on which a field "E-Mails" (Type: EmailFiles) is created. All sent e-mails are stored there.
2. Date on which the last e-mail was sent (Type: Date)

   = MAX ( @Emails[date] )

The following values can be read from EmailFiles::

• sender: list of senders, Strings
• receiver: list of recipients, Strings
• cc: list of recipients in copy, Strings
• subject: list of subjects, Strings
• date: list of send times, Date values
• priority: list of priorities, Strings
• filesize: List of file sizes, Numbers, unit byte

Traffic light

Story: When the cash level becomes critical (less than 40 €), a traffic light should show yellow. If it is less than 20 €, it should show red.
Usage:

1. Cash level (Type: Number) with respective cash level
2. Check cash level (Type: TrafficLight)

   ? [ @cash level < 40; @cash level < 20 ]

Remark:

• The red traffic light condition dominates the yellow one. A green condition results when neither the first nor the second condition is true.

Defeatable traffic light

Story: A time control for a project: A traffic light should indicate if a project ends today, but it is not finished yet. In addition, it should be possible to check 7 days in advance that the project is about to end, so that any necessary measures can be taken in advance. If the project has already ended, this check should not be performed.
Usage:

1. Planned project end (Type: Date)

   05.11.2015

2. Project done (Type: Boolean)

   = {false}

3. Time Control (Type: TrafficLight)

   ? [ AND ( DAY ( @Planned project end - TODAY ) < 7 ; NOT(@Project done) ) ; AND ( DAY ( @Planned project end - TODAY) <= 0 ; NOT(@Project done ) ]

Remark:

• The single conditions are completed using AND with a check of the "Project Done" field. If this field is checked, both the first and second parts of the traffic light condition always result in false. This means that the traffic light is always green.

Messages

Story: I want the system to send me a message when my cash level drops below 50 € .
Usage:

1. Cash level (Type: Number)

   40

2. Check cash level (Type: Condition)

   ? ( @Cash level >= 50 ) : MESSAGE ( compliance )

Remark:

• Boolean type fields can also be referenced. It is only important that the round bracket ( cash level >= 50 ) contains a boolean expression.
• Predefined message types are STAFF, COMPLIANCE, FINANCE, TIME, SIGNATURE.

Messages with predefined text

Story: a message ("Attention logical conflict") will be sent if my party was marked as "cancelled" and "will take place" at the same time.
Usage:

1. cancelled (Type: Boolean)

   = {true}

2. will take place (Type: Boolean)

   = {true}

3. logical conflict (Type: Condition)

   ? AND ( @cancelled  ; @will take place ) : MESSAGE ( compliance ; {Attention logical conflict} | * ; true )

Remark:

• A message is sent if both checkmarks are set.
• All users with access to the data sheet receive the message.
• The text in the curly brackets is freely selectable.
• The optional addition after the | symbol describes in which cases the message is sent. In this case, when the condition is changed from any state * (true or false) to true.
• Here you can choose any combination of false, true and *. If nothing is specified, the default is true after false.

Triggering a message

Story: The system should send an alert message (a special kind of message) to all users who are allowed to see the cash value, if the cash level falls below 50 € . Once the cash balance is back in order, the message shall be automatically removed from the users' inbox.
Usage:

1. Cash level (Type: Number)

   40

2. Check cash level (Type: Condition)

   ? ( @Cash level >= 50 ) : ALERT ( compliance )

Remark:

• When an alert is sent, the same message goes to all users who are allowed to see the field. Since all users see the same message, a single user cannot simply acknowledge it. It will not disappear until the condition is met again (but then automatically).
• The comments for "MESSAGE" and "MESSAGE with predefined text" also apply to ALERT, since the syntax is identical for both (except for the ALERT keyword).

Datasheet versions

Story: How much has the cash level changed?
Usage:

1. Cash level (Type: Number)

   60

2. Change cash level to

   70

3. Difference to last cash level (Type: Number)

   = @Cash level - VERSION ( @Cash level | -1 )

4. Result: 10

Remark:

• If the cash balance was changed from 60 to 70, the value 10 is output in the Difference from last cash balance field because €10 was added.
• The version of the data field referenced is specified after the | symbol. Negative values indicate which relative previous version is being considered, a positive value indicates the absolute version of the data field. If 0, the current value is returned.

Evaluating lists

Story: Is Anna on the guest list?
Usage:

1. Guest List (Type: String)

   Jennifer, Petra, Herta, Anna, Stefan, Paul

2. V.I.P. invited? (Type: Condition)

   ? CONTAINS ( @Guest List | Anna )

Remarks:

• The function can only refer to fields of type String. It will search for the exact word described.
• Upper and lower case is respected.
• The answer to the question is always whether the term was found or not. So the function returns a boolean value.

Conditional counting and listing

Story: For a party, a form was created in the subaspect for each guest to record their RSVP. Who RSVP'd and how many people are coming in total?
Usage:

1. Commitment (Type: Boolean) on the guest forms.
2. 13 guests have been created, 12 of whom have checked the box Commitment.
3. List of participants (Type: ElementLinks)

   = LINKLISTIF ( CHILDREN | @Zusage )

4. Number of participants (Type: Number)

   = COUNTTRUE ( CHILDREN@Zusage )

5. Result: 12

Remarks:

• The ElementLinks field displays a list of all guests who have committed.
• The COUNTTRUE formula can only process Boolean statements.
• In addition to a reference, semicolon-separated, verbose statements are also possible, which are checked for truth and then counted:

  = COUNTTRUE ( 1 < 2 ; 1 < 3 ; 1 < 4 ; 1 > 4 )

  Result: 3

  = COUNTTRUE ( YEAR ( TODAY ) > 1900 ; YEAR ( TODAY ) < 2000 )

  Result: 1

Logical chains

Story: I want a message to remind me 2 days before my party if it is cancelled due to not enough RSVPs (less than 4). I also want to receive a message if the number of attendees is just over.
Usage:

1. Number of invited guests (Type: Number)

   10

2. Number of cancellations (Type: Number)

   2

3. Celebration on (Type: Date)

   01.01.2015

4. everything ok (Type: Condition)

   ? [ @Number of invited guests - @Number of cancellations <= 6 ; AND ( @Number of invited guests - @Number of cancellations < 4 ; DAY ( @Celebration on - TODAY ) <= 2 ) ] : MESSAGE ( staff )

Remarks:

• A traffic light function is defined by [ and ] in which there are two conditions separated by semicolons.
• If the first condition is true, the traffic light is yellow. The second switches the state to red. If both conditions are false, the traffic light is green.
• Red has priority over yellow. If the second condition is true, the traffic light is red, regardless of the status of the first condition.

Evaluation of Excel tables

Story: The cash book is available in an Excel file. The cash balance of 138,42 € determined in it is to be used as the budget for the party.
Usage:

1. An Excel file cashlevel.xls with the workbook year 2015. The cash balance is in cell A12.
2. Cash book in Excel (Type: SpreadsheetFile)

   cashlevel.xls

3. Cash level (Type: Number)

   = EXCEL ( @Cash book in Excel ; Year 2015 ! A12 )

4. Result: 138,42 €

Remarks:

• Files with the extension *.xlsx can also be used.

Conditional summarization

Story: For a good cause, guests at a party will donate a discretionary amount. What is the total amount expected from guests who have made firm pledges? On average, how much will be donated?
Usage:

1. For each guest, a form with a "firm pledge" field (type: Boolean) exists on an aspect sublevel. Out of 15 guests, 12 have pledged with a check mark and entered an amount in their "Donation" field.
2. Donation (Type: Number)

   = SUMIF ( CHILDREN | @fixed pledge )

   Result: 452 €
3. Donation per capita (Type: Number)

   = @Donation  / COUNTIF ( CHILDREN | @fixed pledge)

   Result: 37,67 €

Results:

• The CHILDREN reference automatically reads the same field on child forms. Thus, explicit referencing of the donation field is not necessary.
• Only the donation amounts of the guests for which the boolean field "firm commitment" has a check mark are summed up.
• The difference between COUNT and SUM is that COUNT counts the number of elements (i.e. how many forms the condition applies to), whereas SUM adds up the contained values.

Project Status

Story: show all subforms whose status is ACTIVE.
Usage:

1. All active subforms (Type: ElementLinks)

   = LINKLISTIF( CHILDREN | STATE=ACTIVE )

Remarks:

• This formula word is only used in conditions.
• Valid states are PLANNING, ACTIVE, ONHOLD and CLOSED.

Change project state

Story: change the current state of an item to ACTIVE when it is created.
Usage:

1. Hidden field with action and condition that always returns false (Type: Boolean)

   ? 1<0 : SETSTATE(ACTIVE)

Remarks:

• Valid states are ACTIVE, ONHOLD, CLOSED, ARCHIVED, REJECTED.
• Since SETSTATE is an action comparable to MESSAGE or ALERT it is also possible to define exactly in which cases the action is triggered by adding the parameters true, false and * to the formula, example:

  ? 1>2 : SETSTATE ( ACTIVE | f ; t )

Calculation functions

COLLECT

Functionality: Collects the values of the referenced fields. The only use case for this function is to fill table columns.
Permitted references:

• entry1, entry2, ...
  • Boolean
  • Choice
  • Date
  • Number
  • String

Return value:

• List of Boolean, Choice, Date, Number, String (depending on the referenced field)

Syntax:

• = COLLECT ( <entry1> ; <entry2> ; ... )

• = COLLECT ( <referenceList> )

Examples:

• = COLLECT ( CHILDREN@Amount )

• = COLLECT ( @Net amount ; @Tax )

COLLECTIF

Functionality: Collects the values of the referenced fields. The only use case for this function is to fill table columns.
Permitted references:

• entry1, entry2, ...
  • Boolean
  • Choice
  • Date
  • Number
  • String
• condition
  • Boolean field
  • Condition field
  • Condition
  • Boolean expression

Return value:

• List of Boolean, Choice, Date, Number, String (depending on the referenced field)

Syntax:

• = COLLECTIF ( <entry1> ; <entry2> ; ... | <condition> )

• = COLLECTIF ( <referenceList> | <condition> )

Examples:

• = COLLECTIF ( CHILDREN@Amount | @Age >= 18 )

COUNT

Functionality: Counts the elements of the referenced lists. Entries in the parameter list that cannot be evaluated are ignored in this process, unless the evaluation is set to STRICT by the first entry in the list.
Permitted references:

• listFormula
  • LINKLIST
  • LINKLISTIF
  • ROLES
  • ROLESIF
  • ElementLinks field
  • NextLinks field
  • PrevLinks field
  • Signature field
• evaluation
  • STRICT
  • LAZY

Return value:

• Number

Syntax:

• = COUNT ( <listFormula> )

• = COUNT ( <evaluation> ; <listFormula> )

Example:

• = COUNT ( @MyList )

• = COUNT ( @Signature field )

• = COUNT ( STRICT ; @MyList )

COUNTIF

Functionality: Counts the elements of the referenced lists for which the condition is true. All references in the condition refer to the forms of the elements to be counted; fields of the form that contains the formula can be referenced in the condition by prefixing it with THIS.
Permitted references:

• listFormula
  • LINKLIST
  • LINKLISTIF
  • ROLES
  • ROLESIF
  • ElementLinks field
  • NextLinks field
  • PrevLinks field
  • Signature field
• condition
  • Boolean field
  • Condition field
  • Condition
  • Boolean expression

Return value:

• Number

Syntax:

• = COUNTIF ( <listFormula> | <condition> )

Example:

• = COUNTIF ( @MyList | @Order approved )

• = COUNTIF ( @MyList | @Created on < TODAY )

COUNTTRUE

Functionality: Counts the number of true conditions. Entries in the parameter list that cannot be evaluated are ignored here, unless the evaluation is set to STRICT by the first entry in the list.
Permitted references:

• booleanField1, booleanField2 ...
  • Boolean field
  • Condition field
  • Condition
  • Boolean expression
• evaluation
  • STRICT
  • LAZY

Return value:

• Number

Syntax:

• = COUNTTRUE ( <booleanField1> ; <booleanField2> ; ... )

• = COUNTTRUE ( <evaluation> ; <booleanField1> ; <booleanField2> ; ... )

Example:

• = COUNTTRUE ( CHILDREN@Boolean )

• = COUNTTRUE ( STRICT ; CHILDREN@Boolean )

DIV

Functionality: DIV calculates the integer result of a division of two numbers.
Permitted references:

• Number field

Return value:

• Number (integer)

Syntax:

• = <numberField1> DIV <numberField2>

Examples:

• 58 DIV 2 = 29
• 59 DIV 2 = 29
• 60 DIV 2 = 30
• 3,8 DIV 1,2 = 3

FILESIZE

Functionality: Returns the sum of the file sizes of all files within a file container in bytes.
Permitted references:

• EmailFiles field
• FileLinks field
• Image field
• SpreadsheetFile field

Return value:

• Number

Syntax:

• = FILESIZE ( <field> )

Examples:

• = FILESIZE ( @Invoices )

Remarks:

• The result is always in bytes. If it is to be returned in another unit (for example megabyte), this can be done with the following formula:

  = ( FILESIZE(@FileLinks) ) megabyte

JOIN

Functionality: Concatenates text and inserts the specified separator between each text.
Permitted references:

• field1, field2 ...
  • Choice field
  • Email field
  • MultipleChoice field
  • Number field
  • String field
• separator - Free text

Return value:

• String

Syntax:

• = JOIN ( <field1> ; <field2> ; ... | <separator> )

Examples:

• = JOIN ( CHILDREN@Email | , )

• = JOIN ( @Orders@Order1@Title ; @Orders@Order2@Title | {, } )

Remarks:

• No types may be mixed in the references.

MAX, MIN

Functionality: Returns the extremum of referenced fields: MAX the maximum value and MIN the minimum value. Entries in the parameter list that cannot be evaluated are ignored here, unless the evaluation is set to STRICT by the first entry in the list.
Permitted references:

• field1, field2 ...
  • Date field
  • Number field
• evaluation
  • STRICT
  • LAZY

Return value (depending on the referenced field):

• Date
• Number

Syntax:

• = MAX ( <field1> ; <field2> ; ... )

• = MAX ( <evaluation> ; <field1> ; <field2> ; ... )

Examples:

• = MAX ( CHILDREN@Start date )

• = MIN ( STRICT ; @Orders@Order1@Amount ; @Orders@Order2@Amount )

MAXIF, MINIF

Functionality: Returns the extremum of referenced fields if the condition is true. Entries in the parameter list that cannot be evaluated are ignored here, unless the evaluation is set to STRICT by the first entry in the list. All references in the condition refer to the forms of the referenced elements of the front part. If the condition is to refer to a field that is on the same data sheet as the MAXIF/MINIF formula, the referenced field must be preceded by THIS.
Permitted references:

• entry1, entry2, ...
  • Date field
  • Number field
• condition
  • Boolean field
  • Condition field
  • Condition
  • Boolean expression
• evaluation
  • STRICT
  • LAZY

Return value (depending on the referenced field):

• Date
• Number

Syntax:

• = MAXIF ( <entry1> ; <entry2> ; ... | <condition> )

• = MAXIF ( <evaluation> ; <entry1> ; <entry2> ; ... | <condition> )

Examples:

• = MAXIF ( CHILDREN@Start date | @Order approved )

• = MINIF ( STRICT ; CHILDREN@Amount | YEAR ( @Start date ) = 2015year )

MOD

Functionality: MOD calculates the remainder of a division of two numbers.
Permitted references:

• Number field

Return value:

• Number

Syntax:

• = <number1> MOD <number2>

Possible results:

• 5 MOD 3 = 2
• 6 MOD 3 = 0
• 7 MOD 3 = 1
• 12,3 MOD 3,8 = 0,9

ROUND

Functionality: Rounds a number reference to the specified number of digits.
Permitted references:

• numberEntry
  • Number field
• decimal_place (Decimal places)
  • Number field
  • Integer

Return value:

• Number

Syntax:

• = ROUND ( <numberEntry> | <decimal_place> )

SMA

Functionality: Simple moving average over the period of the specified number of days.
Permitted references:

• numberEntry
  • Number field
• number_of_days
  • Number field
  • Integer

Return value:

• Number

Syntax:

• = SMA ( <numberEntry> | <number_of_days> )

SUBSTRING

Functionality: Determines a substring.
Permitted references:

• entry
  • Choice field
  • Email field
  • MultipleChoice field
  • String field
• begin_index, end_index - Integer

Return value:

• String

Syntax:

• = SUBSTRING ( <entry> | <begin_index> ; <end_index> )

Examples:

• = SUBSTRING ( @File name | 0 ; 3 )

• = SUBSTRING ( @File name | -3 ; 0 )

Remarks:

• Indexes start at 0.
• The end index is not included in the substring.
• Positive indices denote a position relative to the beginning of the string, negative indices denote a position relative to the end of the string. (0;3) references the first three characters of the string, (-3;0) references the last three.
• If MultipleChoice is used directly in SUBSTRING, the list is converted to a string, i.e. if the options are 1,2,3 and the first and third options are selected, the list becomes the string "[1, 3]" and SUBSTRING(0,2) i.e. the first two characters equals "[1"

SUM

Functionality: Summation of the contents of the referenced fields. Entries in the parameter list that cannot be evaluated are ignored unless the evaluation is set to STRICT by the first entry in the list.
Permitted references:

• entry1, entry2, ...
  • ExternalLinks field
  • FileLinks field
  • Number field
  • NextLinks field
  • PrevLinks field
  • RichText field
  • String field
• evaluation
  • STRICT
  • LAZY

Return value (depending on the referenced field):

• ExternalLinks
• FileLinks
• Number
• NextLinks
• PrevLinks
• RichText
• String

Syntax:

• = SUM ( <entry1> ; <entry2> ; ... )

• = SUM ( <evaluation> ; <entry1> ; <entry2> ; ... )

• = SUM ( <referenceList> )

• = SUM ( <evaluation> ; <referenceList> )

Examples:

• = SUM ( CHILDREN@Amount )

• = SUM ( @Net amount ; @Tax )

SUMIF

Functionality: Summation of the contents of the referenced fields if the condition is true. Entries in the parameter list that cannot be evaluated are ignored here, unless the evaluation is set to STRICT by the first entry in the list. All references in the condition refer to the forms of the referenced elements of the front part. If the condition is to refer to a field that is on the same data sheet as the SUMIF formula, the referenced field must be preceded by THIS.
Permitted references:

• entry1, entry2, ...
  • ExternalLinks field
  • FileLinks field
  • Number field
  • NextLinks field
  • PrevLinks field
  • RichText field
  • String field
• condition
  • Boolean field
  • Condition field
  • Condition
• evaluation
  • STRICT
  • LAZY

Return value (depending on the referenced field):

• ExternalLinks
• FileLinks
• Number
• NextLinks
• PrevLinks
• RichText
• String

Syntax:

• = SUMIF ( <entry1> ; <entry2> ; ... | <condition> )

• = SUMIF ( <evaluation> ; <entry1> ; <entry2> ; ... | <condition> )

• = SUMIF ( <referenceList> | <condition> )

• = SUMIF ( <evaluation> ; <referenceList> | <condition> )

Examples:

• = SUMIF ( CHILDREN@Amount | YEAR ( @Order date ) = YEAR ( TODAY ) )

• = SUMIF ( @Service costs ; @spare parts  | @Order approved)

USERNAMES

Functionality:Translates signatures into usernames.
Permitted references:

• signatureField
  • Signature field

Return value:

• String

Syntax:

• = USERNAMES ( <signatureField> )

Logical functions

AND

Functionality: An AND operation of Boolean expressions. Only returns TRUE if all partial conditions return TRUE. Entries in the parameter list that cannot be evaluated are ignored here, unless the evaluation is set to STRICT by the first entry in the list.
Permitted references:

• booleanEntry1, booleanEntry2, ...
  • Boolean field
  • Condition field
  • Condition
  • Boolean expression
• evaluation
  • STRICT
  • LAZY

Return value:

• Boolean expression
• Boolean
• Condition

Syntax:

• ? AND ( <booleanEntry1> ; <booleanEntry2> ; ... )

• ? AND ( <evaluation> ; <booleanEntry1> ; <booleanEntry2> ; ... )

• ? AND ( <referenceList> )

• ? AND ( <evaluation> ; <referenceList> )

Examples:

• ? AND ( @Order approved ; @Amount > 0 )

CONTAINS

Functionality: Checks whether the specified text is contained in a referenced field.
Permitted references:

• entry
  • String field
  • Choice field
  • MultipleChoice field
• text (text to be included)
  • String field

Return value:

• Boolean expression
• Boolean
• Condition

Syntax:

• ? CONTAINS ( <entry> | <text> )

Examples:

• ? CONTAINS ( @Name | Peters )

HAS_FILELOCKS

Functionality: The HAS_FILELOCKS function can be used to check whether a file is locked in any FileLinks field of this element.
Return value:

• Boolean expression
• Boolean
• Condition

Syntax:

• ? HAS_FILELOCKS

HAS_VALUE

Functionality: The HAS_VALUE function can be used to check whether a form field contains a valid value.
Permitted references:

• Choice field
• Condition field
• Date field
• ElementLinks field
• Email field
• ExternalLinks field
• FileLinks field
• Image field
• NextLinks field
• Number field
• PrevLinks field
• RichText field
• Signature field
• SpreadsheetFile field
• String field
• TrafficLight field

Return value:

• Boolean expression
• Boolean
• Condition

Syntax:

• ? HAS_VALUE ( <entry> )

• ? HAS_VALUE ( <entry> | STRICT )

Examples:

• ? HAS_VALUE ( @Ende date )

Remarks:

• If a formula is stored in a field and the result is empty, HAS_VALUE normally returns true, but false in strict mode.

IF

Functionality: If the condition is true, the first value applies, otherwise the second. All references in the condition refer to the forms of the referenced elements of the front part. If the condition is to refer to a field that is on the same data sheet as the IF formula, the referenced field must be preceded by THIS.
Permitted references:

• entry1, entry2, ...
  • ExternalLinks field
  • FileLinks field
  • Number field
  • NextLinks field
  • PrevLinks field
  • RichText field
  • String field
• condition
  • Boolean field
  • Condition field
  • Condition

Return value (depending on the referenced field):

• ExternalLinks
• FileLinks
• Number
• NextLinks
• PrevLinks
• RichText
• String

Syntax:

• = IF ( <entry> | <condition> )

• = IF ( <evaluation> | <condition> )

• = IF ( <entry1> ; <entry2> | <condition> )

• = IF ( <evaluation> ; <entry> | <condition> )

Examples:

• = IF ( (@net * 1.07) ; (@net * 1.19) | @reduced tax)

NOT

Functionality: A negation of boolean expressions. Inverts the result of the included condition (TRUE returns FALSE and reversed).
Permitted references:

• Boolean field
• Condition field
• Condition
• Boolean expression

Return value:

• Boolean expression
• Boolean
• Condition

Syntax:

• ? NOT ( <booleanEntry> )

• ? NOT ( <condition> )

Examples:

• ? NOT ( @Order approved )

OR

Functionality: An OR operation of Boolean expressions. Results in TRUE if one of the partial conditions results in TRUE. Entries in the parameter list that cannot be evaluated are ignored here, unless the evaluation is set to STRICT by the first entry in the list.
Permitted references:

• booleanEntry1, booleanEntry2, ...
  • Boolean field
  • Condition field
  • Condition
  • Boolean expression
• evaluation
  • STRICT
  • LAZY

Return value:

• Boolean expression
• Boolean
• Condition

Syntax:

• ? OR ( <booleanEntry1> ; <booleanEntry2> ; ... )

• ? OR ( <evaluation> ; <booleanEntry1> ; <booleanEntry2> ; ... )

• ? OR ( <referenceList> )

• ? OR ( <evaluation> ; <referenceList> )

Examples:

• ? OR ( @Order approved ; @End date < TODAY )

Links

CONNECTIONS

Functionality:Can access data fields of the adjacent connection, where the referenced field must be of the same type as the target field.
Permitted references:

• connection_type (name of the connection type)
• entry (name of the referenced field)

Return value:

• Contents of the referenced field

Syntax:

• = CONNECTIONS ( <connection_type> ) <entry>

Remark:

• Since this formula returns a quantity, it must be combined with a quantity formula (SUM, SUMIF, MAX, MAXIF, MIN or MINIF).

  = SUM ( CONNECTIONS ( @connectionType ) @entry )

LINKLIST

Functionality: Builds a list from the referenced elements.
Permitted references:

• Elemente (semicolon-separated list)
• THIS
• CHILDREN
• PARENT
• PREV
• NEXT

Return value:

• ElementLinks
• NextLinks
• PrevLinks

Syntax:

• = LINKLIST ( <elements> )

Examples:

• = LINKLIST ( CHILDREN )

• = LINKLIST ( THIS )

• = LINKLIST ( @all employees ; @all customer contacts )

LINKLISTIF

Functionality: Builds a list from the referenced elements if the condition is true. All field references in the condition refer to the forms of the referenced elements of the front part. Fields of the form containing the formula can be referenced in the condition by prepending a THIS.
Permitted references:

• elements
  • Elemente (semicolon-separated list)
  • THIS
  • CHILDREN
  • PARENT
  • PREV
  • NEXT
• condition (refers to the referenced data sheets in each case)
  • Boolean field
  • Condition field
  • Condition

Return value:

• ElementLinks
• NextLinks
• PrevLinks

Syntax:

• = LINKLISTIF ( <elements> | <condition> )

Examples:

• = LINKLISTIF ( CHILDREN | @Amount > 0 )

• = LINKLISTIF ( @employee on vacation ; @employee sick | @department head )

PARENTLINK

Functionality: References the respective aspect nodes on the form of a relation as a navigation aid. With IN and OUT you can further define whether you want to output the source data sheet of the relationship or the target data sheet. Without definition both are returned. Only works on dependency data sheets.
Permitted references:

• direction (IN, OUT)

Return value:

• ElementLinks
• NextLinks
• PrevLinks

Syntax:

• = PARENTLINK

• = PARENTLINK ( <direction> )

ROLES

Functionality:Forms a list of all elements to or from which a relationship exists from the referenced element. If only a certain relationship type is to be considered, ROLESIF.
Permitted references:

• ElementLinks field
• NextLinks field
• PrevLinks field
• LINKLIST
• LINKLISTIF
• THIS
• CHILDREN
• PARENT
• PREV
• NEXT

Return value:

• ElementLinks
• NextLinks
• PrevLinks

Syntax:

• = ROLES ( <reference> )

Examples:

• = ROLES ( THIS )

• = ROLES ( CHILDREN )

ROLESIF

Functionality: Forms a list of the elements to which a relationship of a certain relationship type or direction exists.
Permitted references:

• reference
  • ElementLinks field
  • NextLinks field
  • PrevLinks field
  • LINKLIST
  • LINKLISTIF
  • THIS
  • CHILDREN
  • PARENT
  • PREV
  • NEXT
• connection_type (name of the connection type)
• direction (direction of the connection)
  • IN
  • OUT

Return value:

• ElementLinks
• NextLinks
• PrevLinks

Syntax:

• = ROLESIF ( <reference> | <direction> )

• = ROLESIF ( <reference> | <connection_type> ; <direction> )

Examples:

• = ROLESIF ( THIS | @employee2department )

• = ROLESIF ( THIS | @departmenthead ; IN )

Date functions

CREATIONDATE

Functionality: Is replaced by the date on which the respective form was created. Is not evaluated on templates.
Return value:

• Date (Creation date)

Syntax:

• = CREATIONDATE

DAY

Functionality: Extracts the day from the referenced date or converts a time span into the number of days. Note that a date field must always be used as the date. Entering a date directly into the formula is currently not possible.
Permitted references:

• Date field
• Number field (with time unit)
• Number with time unit

Return value:

• Number (with unit day)

Syntax:

• = DAY ( <dateEntry> )

• = DAY ( <dateEntry> - <dateEntry2> )

• = DAY ( <numberEntry> )

Examples:

• = DAY ( @Start date )

• = DAY ( @Date1 - @Date2 )

Possible results:

• DAY ( 13.11.2015 ) = 13 day
• DAY ( 13.11.2015 - 5.11.2015 ) = 8 day
• DAY ( 691200000 ms ) = 8 day

HOUR

Functionality: Extracts the hour from the referenced date or converts a time span into the number of hours. Since a date in Scope is always normalized to midnight, HOUR always results in 0 for a date.
Permitted references:

• Date field
• Number field (with time unit)
• Number with time unit

Return value:

• Number (with unit h)

Syntax:

• = HOUR ( <dateEntry> )

• = HOUR ( <dateEntry> - <dateEntry2> )

• = HOUR ( <numberEntry> )

Possible results:

• HOUR ( 1 day ) = 24 h
• HOUR ( 691200000 ms ) = 192 h

MINUTE

Functionality: Extracts the minutes from the referenced date or converts a time span into the number of minutes. Since a date in Scope is always normalized to midnight, MINUTE always returns 0 for a date.
Permitted references:

• Date field
• Number field (with time unit)
• Number with time unit

Return value:

• Number (with unit min)

Syntax:

• = MINUTE ( <dateEntry> )

• = MINUTE ( <dateEntry> - <dateEntry2> )

• = MINUTE ( <numberEntry> )

Possible results:

• MINUTE ( 1 day ) = 1.440 min
• MINUTE ( 691200000 ms ) = 11.520 min

MONTH

Functionality: Extracts the month from the referenced date, or converts a time span to the number of months.
Permitted references:

• Date field
• Number field (with time unit)
• Number with time unit

Return value:

• Number (with unit month)

Syntax:

• = MONTH ( <dateEntry> )

• = MONTH ( <dateEntry> - <dateEntry2> )

• = MONTH ( <numberEntry> )

Possible results:

• MONTH ( 13.11.2014 ) = 11 month
• MONTH ( 13.11.2014 - 5.09.2014 ) = 2 month
• MONTH ( 5961600000 ms ) = 2 month

NOW

Functionality: Always represents the date and time of the formula evaluation. All formulas containing NOW are re-evaluated as often as the scheduler job UpdateIntradayClockChangeDependencies is started.
Return value:

• Date field

Syntax:

• = NOW

• = NOW + 1 minute

Possible date units for calculation are: second, minute, hour, day, week, month, year (always in the singular!).

PERIODIC

Functionality: Returns the next date of a regularly repeating event. Easily possible herewith: next birthday, next jour fixe.
Permitted references:

• base_date
  • Date field
• period
  • Number field
  • Number with time unit (day, workday, week, month, year)

Return value:

• Date

Syntax:

• = PERIODIC ( <base_date> | <period> )

Examples:

• = PERIODIC ( @Birthday | 1 year )

Possible results Important: the date must be stored as a reference. Direct input of a date in the formula is currently not possible. If today is 01/13/2015, then the following calculations apply:

• PERIODIC ( 07.01.2015 | 5 day ) = 17.01.2015
• PERIODIC ( 08.01.2015 | 5 day ) = 13.01.2015
• PERIODIC ( 13.01.2015 | 5 day ) = 13.01.2015
• PERIODIC ( 05.01.2015 | 1 week ) = 19.01.2015
• PERIODIC ( 08.01.2015 | 1 week ) = 15.01.2015
• PERIODIC ( 02.01.2015 | 2 month ) = 02.03.2015
• PERIODIC ( 31.12.2014 | 2 month ) = 28.02.2015
• PERIODIC ( 31.12.2014 | 1 year ) = 31.12.2015
• PERIODIC ( 29.02.2012 | 1 year ) = 28.02.2015

SECOND

Functionality: Extracts the seconds from the referenced date, or converts a time span to the number of seconds. Since a date in Scope is always normalized to midnight, SECOND always returns 0 for a date.
Permitted references:

• Date field
• Number field (with time unit)
• Number with time unit

Return value:

• Number (with unit s)

Syntax:

• = SECOND ( <dateEntry> )

• = SECOND ( <dateEntry> - <dateEntry2> )

• = SECOND ( <numberEntry> )

Possible results:

• SECOND ( 1 day ) = 86.400 s
• SECOND ( 691200000 ms ) = 691.200 s

TODAY

Functionality: Replaced daily with the current date and the formula re-evaluated.
Return value:

• Date field

Syntax:

• = TODAY

• = TODAY + 1 day

Possible date units for the calculation are: day, week, month, year (always in the singular!).

WEEK

Functionality: Specifies the calendar week in which the referenced date is located.
Permitted references:

• Date field

Return value:

• Number

Syntax:

• = WEEK ( <dateEntry> )

WEEKDAY

Functionality: Returns the day of the week of the referenced date as a number.
Permitted references:

• Date field

Return value:

• Number (1 for Monday ... 7 for Sunday)

Syntax:

• = WEEKDAY ( <dateEntry> )

WORKDAYS

Functionality: Returns the number of working days between two dates. Start and end day are counted. Working days are all days except Saturday and Sunday.
Permitted references:

• Date field

Return value:

• Number

Syntax:

• = WORKDAYS ( <startDate> ; <endDate> )

YEAR

Functionality: Extracts the year from the referenced date or converts a time span to the number of years.
Permitted references:

• Date field
• Number field (with time unit)
• Number with time unit

Return value:

• Number (with unit year)

Syntax:

• = YEAR ( <dateEntry> )

• = YEAR ( <dateEntry> - <dateEntry2> )

• = YEAR ( <numberEntry> )

Possible results:

• YEAR ( 13.11.2014 ) = 2014 year
• YEAR ( 13.11.2014 - 5.09.2011 ) = 3 year
• YEAR ( 100656000000 ms ) = 3 year

Button functions

CALENDAR

Functionality: The CALENDAR formula can be used in a ViewButton to make it easier for the user to call a calendar.
Permitted references:

• calendar (name of a calendar)
• condition
  • Comparisons of the type <reference> <comparison operator> <value>
  • Boolean functions such as AND, OR and NOT with any number of comparisons.
• Referenz
  • Field reference
  • NAME (Element name)
  • STATE (Element state)
  • STAGE (Layout name)
• Value
  • Free text
  • Field reference (field of the element type of the calendar)
  • Field reference with THIS (field on the data sheet of the ViewButtonmay only be used on the right side of the comparison)
  • absolute field reference

Action:

• A (filtered) calendar is opened.

Syntax:

• = CALENDAR ( <calendar> )

• = CALENDAR ( <calendar> | <condition> )

Examples:

• = CALENDAR ( @Tasks )

• = CALENDAR ( @Tasks | @Employee = {Max Mustermann} )

Remarks:

• The data fields referenced in the filter must be on the calendar's datasheet type, though not necessarily visible on a layout or used in the calendar definition.

COPY

Functionality: The COPY formula contains the logic for a CopyButton. A COPY formula consists of references to the data sheet to be copied and the location where it is to be copied (not applicable when copying objects and processes). In addition, when copying an entire structure, the datasheet to be offered for editing when copying can be selected. Finally, the status of the created elements can be defined and the copy scope, which indicates whether a single data sheet or an entire structure is to be copied.
Permitted references:

• sources (element to be copied; multiple entries possible)
  • Object
  • Process
  • Aspect
  • Node
• targets (parent element under which the element is to be copied; multiple entries possible)
  • Object
  • Process
  • Aspect
  • Node
  • When copying an object or process, this reference is left empty.
• input_element (optional; data sheet that is offered for processing; this data sheet must be below source in the structure)
  • Aspect
  • Node
• value_element (optional; data sheet from which the user input is taken; fields with formulas, action buttons and signature fields are ignored)
  • Object
  • Process
  • Node
• connection_targets (if the CopyButton has connection creation enabled and the strategy is set to selected, the user can select a set of targets from this list; for each target a copy is created and connected to this target)
  • Object
  • Process
  • Aspect
  • Node
  • When copying an object or process, this reference is left empty.
• state (status of the copied elements)
  • IMAGINARY
  • PLANNING (default)
  • ACTIVE
• range (scope)
  • SINGLE (only the element is copied)
  • CHILDREN (element with all subelements)
  • CONNECTED (default; element with all subelements and all elements connected by connections)
  • SINGLE_UP (the referenced element and all connections (regardless of their direction) are copied. If these connections exist to imaginary elements, these are also recursively copied, as are their imaginary parent elements and connections).

Action:

• Copy of an element or element structure is created in the REWOO world.

Syntax:

• = COPY ( LINKLIST ( <sources> ) ; LINKLIST ( <targets> ) ; LINKLIST ( <input_element> ) ; LINKLIST ( <value_element> ) ; LINKLIST ( <connection_targets> ) | <state> ; <range> )

Examples:

• =COPY ( LINKLIST ( @orders@template order ) ; LINKLIST ( @orders ) | ACTIVE ; SINGLE )

Remarks:

• For more information on how to use and define a CopyButton, see chapter Possible configurations of a CopyButton.

KANBAN

Functionality: The KANBAN formula can be used in a ViewButton to help the user open a kanban board. It contains the Kanban board to be opened and optionally a filtering by which the Kanban types should be filtered.
Permitted references:

• kanbanBoard (name of a kanban board)
• condition
• comparisons like
• Boolean functions such as AND, OR, and NOT with any number of comparisons
• Reference
  • Field reference
  • NAME (element name)
  • STATE (element state)
• Value
  • Free text
  • Field reference (field of the element type of the kanban board)
  • Field reference with THIS (field on the data sheet of the ViewButton; may only be used on the right side of the comparison)
  • Absolute field reference

Action:

• A (filtered) kanban board is opened.

Syntax:

• = KANBAN ( <kanbanBoard> )

Examples:

• = KANBAN ( @Order overview )

• = KANBAN ( @tasks | @employee = {Max Mustermann} )

PORTFOLIO

See TABLEVIEW
Can still be used instead of TABLEVIEW.

TABLEVIEW

Functionality: The TABLEVIEW formula defines the logic behind a ViewButton. It contains the table view to be opened and optionally a filtering according to which the table rows are to be restricted.
Permitted references:

• tableView (name of a table view created).
• condition
  • comparisons like
  • Boolean functions such as AND, OR, and NOT with any number of comparisons
• Reference
  • Field reference
  • NAME (element name)
  • STATE (element state)
• Value
  • Free text
  • Field reference (field of the element type of the kanban board)
  • Field reference with THIS (field on the data sheet of the ViewButton; may only be used on the right side of the comparison)
  • Absolute field reference

Action:

• A (filtered) table view is opened.

Syntax:

• = TABLEVIEW ( <tableView> )

• = TABLEVIEW ( <tableView> | <condition> )

Examples:

• = TABLEVIEW ( @Order overview )

• = TABLEVIEW ( @Order overview | @Contact = THIS@Contact )

• = TABLEVIEW ( @Order overview | @Order date < TODAY )

• = TABLEVIEW ( @Order overview | AND ( NOT ( STATE = ONHOLD ) ; NAME = {Product 1001} ; OR ( STAGE <> {Final} ; @entry = 4 ) ) )

Remarks:

• The data fields referenced in the filter must be on the data sheet type of the table view, but not necessarily created as columns in the table.

TASKVIEW

Functionality: The TASKVIEW formula defines the logic behind a ViewButton. It contains the task view to be opened and optionally a filtering according to which the table rows are to be restricted.
Permitted references:

• taskView (name of a created table view)
• condition
  • comparisons like
  • Boolean functions such as AND, OR, and NOT with any number of comparisons
• Reference
  • Field reference
  • NAME (element name)
  • STATE (element state)
• Value
  • Free text
  • Field reference (field of the element type of the kanban board)
  • Field reference with THIS (field on the data sheet of the ViewButton; may only be used on the right side of the comparison)
  • Absolute field reference

Action:

• A (filtered) task view opens.

Syntax:

• = TASKVIEW ( <taskView> )

• = TASKVIEW ( <taskView> | <condition> )

Examples:

• = TASKVIEW ( @Tasks )

• = TASKVIEW ( @Tasks | @Contact = THIS@Contact )

• = TASKVIEW ( @Tasks | @Order date < TODAY )

• = TASKVIEW ( @taskView | AND ( NOT ( STATE = ONHOLD ) ; NAME = {Product 1001} ; OR ( STAGE <> {Final} ; @entry = 4 ) ) )

Remarks:

• The data fields referenced in the filer must be on the task view datasheet type, but not necessarily referenced in the task view configuration.

Value functions

ACCOUNTGROUPS

Functionality: Lists all of all memberships in account groupsof the user.
Return value:

• ElementLinks

Syntax:

• = ACCOUNTGROUPS

ATTACHMENTS

Functionality: Lists all email attachments contained in an EmailFiles field.
Permitted references:

• EmailFiles field

Return value:

• FileLinks

Syntax:

• = ATTACHMENTS ( <emails> )

BARCODE

Functionality: Creates an image with a 1D or 2D barcode from a referenced value.
Permitted references:

• barcodeContentEntry
  • String field
• barcodeType
  • AZTEC
  • CODABAR
  • CODE_39
  • CODE_93
  • CODE_128
  • DATA_MATRIX
  • EAN_8
  • EAN_13
  • ITF
  • MAXICODE
  • PDF_417
  • QR_CODE
  • RSS_14
  • RSS_EXPANDED
  • UPC_A
  • UPC_E
  • UPC_EAN_EXTENSION
• width (width of the generated image in pixels)
• height (height of the generated image in pixels)

Return value:

• Image (Barcode as png-Datei)

Syntax:

• = BARCODE ( <barcodeContentEntry> ; <barcodeType> ; <width> ; <height> )

Examples:

• = BARCODE ( @Description ; QR_CODE ; 250 ; 250 )

COUNTER

Functionality: Returns the next number from the named number range.
Permitted references:

• String field
• Text in curly braces

Return value:

• Number

Syntax:

• = COUNTER ( <counterNameEntry> )

• = COUNTER ( <counter_name> )

Examples:

• = COUNTER ( OrderNo )

• = COUNTER ( @created by )

COUNTERIF

Functionality: Returns the next number from the named number range if the condition is true. This can be used to delay setting the ID until all necessary information is available. E.g. the name of the number range can be done by assembling text modules, but they are not complete until a later time. However, text merging also works if not all text parts contain a value. In this case, the number range would be determined prematurely and a new number range would simply be created with the incomplete text.
Permitted references:

• counterName
  • String field
• condition
  • Boolean field
  • Condition field

Return value:

• Number

Syntax:

• = COUNTERIF ( <counterNameEntry> | <condition> )

Example:

• = COUNTERIF ( @created by | @validCounter )

CREATOR

Functionality: Replaced by the user name that created the form in question (e.g. by a CopyButton). Is not evaluated on templates.
Return value:

• String (name of the user)

Syntax:

• = CREATOR

ELEMENTID

Functionality: Returns the id of the current element as a string.
Return value:

• String

Syntax:

• = ELEMENTID

ELEMENTNAME

Functionality: Returns the name/identifier of the current element as a string.
Return value:

• String (datasheet name)

Syntax:

• = ELEMENTNAME

EXCEL

Functionality: Reading a specific cell from an Excel file stored in a SpreadsheetFile field.
Permitted references:

• spreadsheetEntry
  • SpreadsheetFile field
• worksheet (workbook within the Excel file)
• cell (cell within the workbook, example: B12)

Return value (depending on the format of the cell):

• Boolean
• Condition
• Date
• Email
• ExternalLinks
• Number
• String

Syntax:

• = EXCEL ( <spreadsheetEntry> ; <worksheet> ! <cell> )

Examples:

• = EXCEL ( @Project ; Table1!E4 )

GROUPMEMBERS

Functionality: Lists all members of an account group on the corresponding account group datasheet.
Return value:

• ElementLinks

Syntax:

• = GROUPMEMBERS

INIT

Functionality: If an element is copied from a template, the INIT formula is replaced by the referenced value. This allows a value to be set dynamically without ultimately writing a permanent reference. This function is only used on templates.
Permitted references:

• Choice field
• Date field
• ElementLinks field
• MultipleChoice field
• Number field
• String field

Return value (depending on the referenced field):

• Choice
• Date
• ElementLinks
• MultipleChoice
• Number
• String

Syntax:

• = INIT ( <sourceEntry> )

Remarks:

• No formulas are supported within the INIT function. Only individual fields can be referenced.

OTHERASPECTS

Functionality: Can access data fields from other data sheets of a particular aspect type to which some relationship exists. The field containing the formula and the referenced fields must be of the same type.
Permitted references:

• aspect_type (name of the aspect type)
• connection_type (name of the connection type; optional)
• state (state of referenced elements; optional)
  • IMAGINARY
  • PLANNING
  • ACTIVE
  • ONHOLD
  • CLOSED
• entry (name of the referenced field)

Return value (depending on the referenced field):

• Content of the referenced field

Syntax:

• = OTHERASPECTS ( <aspect_type> ) <entry>

• = OTHERASPECTS ( <aspect_type> ; <connection_type> ) <entry>

• = OTHERASPECTS ( <aspect_type> ; <connection_type> | <state1> ; <state2> ; ... ) <entry>

Examples:

• = SUM ( OTHERASPECTS ( @orders ) @Amount )

• = SUM ( OTHERASPECTS ( @orders ; @customer2order ) @Amount )

• = SUMIF (OTHERASPECTS ( @orders | ACTIVE ) @Amount | @Order closed )

Remarks:

• Since this formula returns a quantity, it must be combined with a quantity formula (SUM, SUMIF, MAX, MAXIF, MIN or MINIF).

  = SUM ( OTHERASPECTS ( @aspect_type ) @entry )

• If OTHERASPECTS is used in conjunction with a condition (for example, SUMIF), then the condition refers to the datasheet of the specified type.

PARENTNAME

Functionality: Returns the name of the respective parent element as a string. On connection data sheets, it must also be specified whether the source or the target data sheet is to be returned. Does not work for objects and processes.
Permitted references:

• direction
  • IN - Source datasheet
  • OUT - Target datasheet

Return value:

• String (identifier of the referenced element)

Syntax:

• = PARENTNAME

• = PARENTNAME ( <direction> )

  on connections

PREV/NEXT

Functionality: Access to predecessor or successor elements. For use, a predecessor or successor must be defined on the data sheet, i.e. a PrevLinks or NextLinks field manual (via UserSelectedLinks) or automatically filled via formula language. Since the return value contains a quantity, an aggregation function (SUM, MAX, etc) must be used in combination.
Return value:

• Contents of the referenced field

Syntax:

• = PREV<reference>

• = NEXT<reference>

Examples:

• = SUM (PREV@Holiday)

• = SUM (NEXT@Forecast)

SUBSET

Functionality: Returns a subset of the original list.
Permitted references:

• list
  • LINKLIST
  • LINKLISTIF
  • ROLES
  • ROLESIF
  • ElementLinks field
  • NextLinks field
  • PrevLinks field
  • Signature field

Return value:

• ElementLinks
• NextLinks
• PrevLinks

Syntax:

• = SUBSET ( <list> | <begin_index> ; <end_index> )

Examples:

• = SUBSET ( @Projects | 0 ; 2 )

• = SUBSET ( @Projects | -3 ; -1 )

Remarks:

• Index starts with 0.
• The end index is included in the list
• Positive indices indicate a position relative to the beginning of the list, negative indices indicate a position relative to the end of the list. (0;2) references the first three entries of the list, (-3;-1) the last three.

VERSION

Functionality: Returns the value of the referenced field at a past time. A negative version ID returns a previous version relative to the current version. A positive version id is based on the absolute version number of the field. The point in time can also be specified in the form of a reference to a date field. In this case, the value of the field is read at the corresponding time in the past.
Permitted references:

• Number field
• String field

Return value (depending on the referenced field):

• Number
• String

Syntax:

• = VERSION ( <entry> | <versionId> )

• = VERSION ( <entry> | <date_entry> )

Remarks:

• If the version is determined using a date field, the first value of that date is returned. This is usually the value that the referenced field had at 0:00 (UTC). If you want the last value of a day to be returned, you must select the next day as the date.
• If a date value is specified on which the corresponding element did not yet exist, the formula is marked as "NOT VALUABLE".
• If a future date value is specified, the current value is returned.

System actions

System actions can only be used with the formula type Condition. This is always introduced with a ?.

ALERT

Functionality: Sends a message to all users with at least read permission on the data sheet depending on a condition. This message cannot be marked as read or deleted. However, it disappears automatically when the triggering condition is removed.
Permitted references:

• condition
  • Boolean field
  • Condition field
  • TrafficLight field
  • Condition
  • Boolean expression
• category (category of the message)
  • COMPLIANCE
  • FINANCE
  • SIGNATURE
  • STAFF
  • TIME
  • user-defined category
• message_text (message content; optional)
  • String-Feld
  • RichText-Feld
  • Text in braces
• bool_from (determines which old value leads to the action; optional)
  • f or false
  • t or true
  • c or create
  • schedule (will be evaluated and executed every night if the next state matches)
  • * = true, false, create, schedule
• bool_to (determines which new value leads to the action; optional)
  • f or false
  • t or true
  • * = true, false

Syntax:

• ? <condition> : ALERT

• ? <condition> : ALERT ( <category> )

• ? <condition> : ALERT ( <message_text> )

• ? <condition> : ALERT ( <bool_from> ; <bool_to> )

• ? <condition> : ALERT ( <category> ; <message_text> | <bool_from> ; <bool_to> )

Examples:

• ? @Project costs > 5000 : ALERT ( finance ;  {caution cost exceeds project budget.} | f ; t )

Action:

• All users who see this item will receive an alert message when it is triggered.

CONNECT

Functionality: Creates connections between all start elements and target elements depending on a condition.
Permitted references:

• sources (start elements), targets (target elements) - multiple entries possible
  • LINKLIST
  • LINKLISTIF
  • ROLES
  • ROLESIF
  • ElementLinks field
  • NextLinks field
  • PrevLinks field
  • Signature field
• connection_type (name of the connection type)
• connection_right (permission that is obtained through the connection)
  • NONE = no permission
  • READ = read permission
  • WRITE = write permission
  • ADMIN = administration permission
• strategy (strategy for existing connections)
  • KEEP_EXISTING = keep all existing connections (Default)
  • DELETE_EXISTING_AT_SOURCE = delete all connections of this connection type that originate from the start elements
  • DELETE_EXISTING_AT_TARGET = delete all connections of this connection type that end at the target elements
• bool_from (determines which old value leads to the action; optional)
  • f or false
  • t or true
  • c or create
  • schedule (will be evaluated and executed every night if the next state matches)
  • * = true, false, create, schedule
• bool_to (determines which new value leads to the action; optional)
  • f or false
  • t or true
  • * = true, false

Syntax:

• ? <condition> : CONNECT ( <sources> ; <targets> ; <connection_type> ; <connection_right> )

• ? <condition> : CONNECT ( <sources> ; <targets> ; <connection_type> ; <connection_right> ; <strategy> )

• ? <condition> : CONNECT ( <sources> ; <targets> ; <connection_type> ; <connection_right> | <bool_from> ; <bool_to> )

Examples:

• ? @Project costs > 5000 : CONNECT ( @Teamlead ; LINKLIST(THIS) ; @Project controlling ; WRITE | f ; t )

Action:

• All source elements are connected to all target elements.

DISCONNECT

Functionality: Deletes connections from an element, depending of the direction of the connection.
Permitted references:

• connection_type (name of the connection type)
• connection_direction
  • IN = incomming connection
  • OUT = outgoing connection
• strategy (strategy for existing
• bool_from (determines which old value leads to the action; optional)
  • f or false
  • t or true
  • c or create
  • schedule (will be evaluated and executed every night if the next state matches)
  • * = true, false, create, schedule
• bool_to (determines which new value leads to the action; optional)
  • f or false
  • t or true
  • * = true, false

Syntax:

• ? <condition> : DISCONNECT ( <connection_type> ; <connection_direction> | <bool_from> ; <bool_to> )

Examples:

• ? @Project costs > 5000 : DISCONNECT ( @Project controlling ; IN | f ; t )

Action:

• Removes all incoming connections of type "Project controlling".

EMAIL

Functionality: Sends an e-mail with a specified text to the specified recipients depending on a condition. The e-mail can also have attachments, which can be specified in the form of a reference to a FileLinks field
Permitted references:

• condition
  • Boolean field
  • Condition field
  • Condition
  • Boolean expression
• send_to (email addresses of the recipients)
  • Email field
  • String field
  • Text in braces (comma-separated list of email addresses)
• reply_to (e-mail address of the sender)
  • Email field
  • String field
  • Text in braces
• email_title, email_text (subject and content of the email)
  • String field
  • RichText field
  • Text in braces
• attachments (attachments; optional)
  • FileLinks field
• bool_from (determines which old value leads to the action; optional)
  • f or false
  • t or true
  • c or create
  • schedule (will be evaluated and executed every night, if the next state matches)
  • * = true, false, create, schedule
• bool_to (determines which new value leads to the action; optional)
  • f or false
  • t or true
  • * = true, false

Syntax:

• ? <condition> : EMAIL( <send_to> ; <reply_to> ; <email_title> ; <email_text> )

• ? <condition> : EMAIL( <send_to> ; <reply_to> ; <email_title> ; <email_text> ; <attachments> | <bool_from> ; <bool_to> )

Examples:

• ? @cost ACTUAL > @cost TARGET : EMAIL ( @mail recipient ; @reply ; {caution cost too high!} ; @mailtext | f ; t )

• ? @COST ACTUAL > @COST TARGET : EMAIL ( @mailerecipient ; @reply ; {caution cost too high!} ; {cost exceeds budget. Please check ##DEEPLINK##} | f ; t )

Action:

• An email will be sent to all email addresses provided.

Other:

• Link to data sheet: In email_text the placeholder ##DEEPLINK## can be used to automatically insert a link to the data sheet in the email text.

FILESYNC_MS

Functionality: Synchronizes the files of a FileLinks fields with a folder in Microsoft 365 SharePoint.
Permitted references:

• condition
  • Boolean field
  • Condition field
  • TrafficLight field
  • Condition
  • Boolean expression
• file_links
  • FileLinks field
• coupling
  • SYNC - new files are transferred to SharePoint, remote files are deleted
  • PUSH - new files are transferred to SharePoint
• tenant - GUID of the Microsoft customer
  • Free text
  • String field
• client_id - GUID of Microsoft Azure for the scope installation
  • Free text
  • String field
• client_secret - authentication with Microsoft is done using the client ID and a client secret without the user's account
  • Free text
  • String field
• site - Id of the site at SharePoint; this is composed of a domain and two GUIDs and can be determined via Microsoft's Graph Explorer.
  • Free text
  • String field
• drive - Id of the document library at SharePoint, optional, if no document library is specified, the files are written to the standard library.
  • Free text
  • String field
• parent - GUID or full path of the directory in the site at SharePoint, optional, if no parent is specified, the files will be written to the root directory
  • Free text
  • String field
• bool_from (determines which old value leads to the action; optional)
  • f or false
  • t or true
  • c or create
  • schedule (will be evaluated and executed every night if the next state matches)
  • * = true, false, create, schedule
• bool_to (determines which new value leads to the action; optional)
  • f or false
  • t or true
  • * = true, false

Syntax:

• ? <condition> : FILESYNC_MS ( <file_links> ; <coupling> ; <tenant> ; <client_id> ; <client_secret> ; <site> ; <drive> )

• ? <condition> : FILESYNC_MS ( <file_links> ; <coupling> ; <tenant> ; <client_id> ; <client_secret> ; <site> ; <drive> ; <parent> )

• ? <condition> : FILESYNC_MS ( <file_links> ; <coupling> ; <tenant> ; <client_id> ; <client_secret> ; <site> ; <drive> ; <parent> | <bool_from> ; <bool_to> )

Examples:

• ?( DAY ( @inspection date - TODAY ) < 11 ) : FILESYNC_MS ( @Documents ; SYNC ; {e238cc56-7fb1-923e-2a93-e289f2340df6} ; {d0a923fb-d6af-3496-a63c-0bc23488293d} ; {DCH3S~tHERFcbCV93N8wERyCVghpYEfc4fbgd} ; {rewoogmbh.sharepoint.com,e943d234-43f2-93a2-1f76-1d832cb23f4e,2d023be2-2ccf-92f1-8da4-83ce29ece4f2} ; {b!VtWQ2_loqUiPeR8yWER_XuSbYI1WEVJUjaQkzjns1fIVWXQ83SDcRLnCQI4wLc2o} ; {/Sonstige Unterlagen} | f ; t )

• ?( DAY ( @inspection date - TODAY ) < 11 ) : FILESYNC_MS ( @Documents ; SYNC ; @TenantId ; @ClientId ; @ClientSecret ; @SiteId ; | f ; t )

• ?( DAY ( @inspection date - TODAY ) < 11 ) : FILESYNC_MS ( @Documents ; SYNC ; @TenantId ; @ClientId ; @ClientSecret ; @SiteId ; @DriveId | f ; t )

• ?( DAY ( @inspection date - TODAY ) < 11 ) : FILESYNC_MS ( @Documents ; SYNC ; @TenantId ; @ClientId ; @ClientSecret ; @SiteId ; ; {01QM4V6L5AN5BT73EUGRA35WOITFD5UXXF} | f ; t )

• ?( DAY ( @inspection date - TODAY ) < 11 ) : FILESYNC_MS ( @Documents ; SYNC ; @TenantId ; @ClientId ; @ClientSecret ; @SiteId ; @DriveId ; @Parent | f ; t )

Action:

• The files of the FileLinks field are synchronized with a folder at SharePoint.

FREEZE

Functionality: Removes the formulas from the referenced fields, preventing further changes to the values.
Permitted references:

• condition
  • Boolean field
  • Condition field
  • TrafficLight field
  • Condition
  • Boolean expression
• entry - any form field
• bool_from (determines which old value leads to the action; optional)
  • f or false
  • t or true
  • c or create
  • schedule (will be evaluated and executed every night if the next state matches)
  • * = true, false, create, schedule
• bool_to (determines which new value leads to the action; optional)
  • f or false
  • t or true
  • * = true, false

Syntax:

• ? <condition> : FREEZE ( <entry1> ; <entry2> ; ... )

• ? <condition> : FREEZE ( <entry1> ; <entry2> ; ... | <bool_from> ; <bool_to> )

Examples:

• ?( DAY ( @inspection date - TODAY ) < 11 ) : FREEZE ( @inspecion date | f ; t )

Action:

• The formulas are removed from all referenced fields.

MESSAGE

Functionality: Sends a message depending on a condition to all users who have at least read permission on the data sheet.
Permitted references:

• condition
  • Boolean field
  • Condition field
  • TrafficLight field
  • Condition
  • Boolean expression
• category (category of the message)
  • COMPLIANCE
  • FINANCE
  • SIGNATURE
  • STAFF
  • TIME
  • custom category
• message_text (message content; optional)
  • String-Feld
  • RichText-Feld
  • Text in braces
• bool_from (determines which old value leads to the action; optional)
  • f or false
  • t or true
  • c or create
  • schedule (will be evaluated and executed every night if the next state matches)
  • * = true, false, create, schedule
• bool_to (determines which new value leads to the action; optional)
  • f or false
  • t or true
  • * = true, false

Syntax:

• ? <condition> : MESSAGE

• ? <condition> : MESSAGE ( <category> )

• ? <condition> : MESSAGE ( <message_text> )

• ? <condition> : MESSAGE ( <bool_from> ; <bool_to> )

• ? <condition> : MESSAGE ( <category> ; <message_text> | <bool_from> ; <bool_to> )

Examples:

• ?( DAY ( @inspection date - TODAY ) < 11 ) : MESSAGE ( time ; {Inspection date in 10 days} | f ; t )

If a message is to be triggered at a traffic light, no transition must be specified. The message is triggered when either the yellow or red condition is true. Action:

• A message is sent to all users who see the element.

NOTIFICATION

Functionality: Sends a message depending on a condition to the specified receivers.
Permitted references:

• condition
  • Boolean field
  • Condition field
  • TrafficLight field
  • Condition
  • Boolean expression
• category (category of the message)
  • COMPLIANCE
  • FINANCE
  • SIGNATURE
  • STAFF
  • TIME
  • custom category
• send_to (login nodes of the recipients)
  • ElementLinks field
• message_subject
  • String-Feld
  • RichText-Feld
  • Text in braces
• message_text
  • String-Feld
  • RichText-Feld
  • Text in braces
• bool_from (determines which old value leads to the action; optional)
  • f or false
  • t or true
  • c or create
  • schedule (will be evaluated and executed every night if the next state matches)
  • * = true, false, create, schedule
• bool_to (determines which new value leads to the action; optional)
  • f or false
  • t or true
  • * = true, false

If you want to include a link within the notification text you can use the placeholder ##NOTIFICATIONLINK##. If the notification is forwarded as email (depending on the user settings), a click on the link will automatically mark the notification as read. Syntax:

• ? <condition> : NOTIFICATION ( <category> ; <send_to> ; <message_subject> ; <message_text> | <bool_from> ; <bool_to> )

Examples:

• ?( DAY ( @inspection date - TODAY ) < 11 ) : NOTIFICATION ( TIME ; @receivers ; {Inspection}; {Inspection date in 10 days} | f ; t )

If a message is to be triggered at a traffic light, no transition must be specified. The message is triggered when either the yellow or red condition is true. Action:

• A message is sent to the specified receivers.

SETSTATE

Functionality: Changes the status of the current element depending on a condition. If elements are to be archived or discarded in this context, the notes stored under Problem Cases should be observed.
Permitted references:

• condition
  • Boolean field
  • Condition field
  • TrafficLight field
  • Condition
  • Boolean expression
• state (target state)
  • ACTIVE
  • ONHOLD
  • CLOSED
  • ARCHIVED
  • REJECTED
• bool_from (determines which old value leads to the action; optional)
  • f or false
  • t or true
  • c or create
  • schedule (will be evaluated and executed every night, if the next state matches)
  • * = true, false, create, schedule
• bool_to (determines which new value leads to the action; optional)
  • f or false
  • t or true
  • * = true, false

Syntax:

• ? <condition> : SETSTATE ( <state> | <bool_from> ; <bool_to> )

• ? <condition> : SETSTATE ( <state> )

Examples:

• ? @Order closed : SETSTATE ( CLOSED | f ; t )

Action:

• The current element is set to the specified status.

Comparative Operators

	Number, Date	ElementLinks
>	greater than	contains more than the elements of
>=	greater than or equal to	contains at least the elements of
=	equal to	contains the same elements
<=	less than or equal to	contains at least all elements of
<	less than	contains more than all elements are contained in
<>	not equal	both lists contain more than their intersection

	Text
=	exactly matches the string
?	contains string
!	does not contain string

References

Of datasheets

The following graphic shows all possible relative referencing of other data sheets in a world. An absolute (direct) reference is generally possible to all elements of a world. With the exception of PARENTNAME, all functions specified here return an element list.

Functions:

• LINKLIST(THIS) - Self-reference to data sheet with formula
• LINKLIST(PARENT) - Parent data sheet (not objects or processes!)
• LINKLIST(CHILDREN) - All subordinate data sheets (not on objects or processes!)
• LINKLIST(PREV) - All datasheets that are in PrevLinks fields of this form
• LINKLIST(NEXT) - All datasheets that are in NextLinks fields of this form
• ROLES - All data sheets with which a relationship exists
• ROLESIF - All data sheets to which a relationship of a certain type exists
• PARENTLINK - parent data sheet; for relationships, source or target data sheet respectively
• PARENTNAME - Data sheet name of the parent data sheet as string; for relationships in each case source or target data sheet

Of entries

Beyond data sheets, special data fields can also be referenced on other data sheets. The following graphic shows all possible relative referencing of data fields on other forms. An absolute reference is generally possible to all data fields of a world.

References that refer to more than a single field require a preceding quantity formula, such as SUM, SUMIF, MAX, MAXIF, MIN or MINIF. With regard to objects and processes, the same restrictions apply as for referencing data sheets.

Functionen:

• PARENT@feld - field on parent data sheet
• CHILDREN@feld - field on all subordinate data sheets (requires quantity formula)
• PREV@feld - field on all data sheets stored in PrevLinks field (requires quantity formula)
• NEXT@feld - field on all data sheets stored in the NextLinks field (requires quantity formula)
• OTHERASPECTS(@aspect_type)@feld - field on all datasheets of a certain type to which a relationship exists (requires quantity formula)
• CONNECTIONS(@connection_type)@feld - field on all adjacent relationship data sheets of a specific relationship type (requires quantity formula)

Units

REWOO Scope supports a wide range of units. These are mainly used in the Number data field type.

Supported units

Numerous units of the international system of units (SI units) and alternative units are supported:

• Amount of data: bit, byte, kbit or kilobit, Kibit or kibibit, kB or kilobyte, KiB or kibibyte, Mbit or megabit, Mibit or mebibit, MB or megabyte, MiB or mebibyte, Gbit or gigabit, Gibit or gibibit, GB or gigabyte, GiB or gibibyte, Tbit or terabit, Tibit or tebibit, TB or terabyte, TiB or tebibyte, Pbit or petabit, Pibit or pebibit, PB or petabyte, PiB or pebibyte
• Length: mm (millimeter), cm (centimeter), in (inch), dm (decimeter), ft (foot), yd (yard), m (meter), km (kilometer), mi (mile), nmi (nautic mile), ly (light year)
• Area: mm², cm², dm², m², a (Aar), ha (hectare), km²
• Volume: mm³, cm³, oz (ounce), dm³, mL (milliliter), L (liter), gal (galon), m³, km³
• Duration: ms, s, min, h, day, workday, week, month, year
• Speed: m/s, km/h, mph (miles/hour), kn (nodes), Mach
• Acceleration: m/s², grav (gravity)
• force: N (Newton)
• Power: W (Watt), hp (horse power)
• Mass: g, lb (Pound), kg
• Energy: J (Joule)
• Temperature: K (Kelvin), °F (Fahrenheit), °R (Rankine)
• Pressure: bar, atm, Pa (Pascal)
• Electricity: A (Ampere), V (Volt), F (Farad), Ω (Ohm), S (Siemens), C (Coulomb)
• Amount of substance: mol
• luminous intensity: cd

Currencies

Currencies can also be attached to numbers. For this purpose, the internationally standardized currency abbreviations (for Euro, US Dollar, Yen and British Pound also the respective currency sign) must be used. Attention: Different currencies cannot be automatically offset against each other.

Examples of supported currencies::

• EUR or €
• USD or $
• GBP or £
• CHF
• JPY or ¥

Using units

There are different ways in which units can be added to numbers:

1.Direct input by the user:

The user simply adds the unit when making an entry in a Number field. Attention: Units are always specified in the singular.

Example:

5 day

2. appending by formula:

A unitless number can also be assigned a unit by formula. This is done, for example, by adding an empty quantity with a unit or multiplying the value by 1 and a unit.

Examples:

12 + 0 day = 12 day

3 * 1 month = 3 month

3. Append as text after field value:

In the type designer, there is an option to specify a custom "text after the value" for Number fields, which can accommodate units, among other things. This is only a cosmetic solution, because here only a text after the value is shown, which however is not part of the value itself. A calculation of different units is not possible here, because the attached text is not considered as a unit internally. In addition, unit duplications can occur in the display if a user manually appends a unit to a number.

Offsetting units with each other

Units can be offset against each other as long as they are compatible.

Examples:

1 year + 12 month = 2 year

2 km + 500 m = 2,5 km

3 m * 5 m = 15 m²

15 m² * 2 m = 30 m³

Forcing a specific unit

If you want to display a value with a unit in a different unit (for example, in cm instead of m), you can force this in the formula language in the following ways:

= @Field name <einheit>

When several fields are multiplied, the result is a new unit (the example here assumes that @length and @width have values in centimeters, which is automatically converted to cm² by the multiplication, but should be output in m² by formula language):

= ( @length * @width ) m²

For results of a formula, the formula belongs in parentheses, the unit after the parentheses:

= ( FILESIZE(@FileLinks) ) megabyte.

Note that this only works for chargeable units.

Date functions

Date values are stored in Unix time (milliseconds since 01.01.1970). From this follows that the difference between two dates is of the unit ms. To convert this into another date unit, the result can be interpreted accordingly. The functions DAY, MONTH, YEAR are possible here. Example:

= YEAR ( @date2 - @date1 )

It is also possible to subtract or add a number to a date in a data field of type Date. For this you can specify units like day, month, year. Example:

= @date1 + 14 day

Copy logic

At this point, the logic behind a copy action is explained in detail.
It is not distinguished whether the action is executed via button (without formula addition) or via the interface.

Terms and preparation

The respective picture shows the situation before the copy action (left side) and after the action (right side).
Here

• filled circles are real elements, whereas empty circles are imaginary elements.
• vertical lines represent the implicit dependencies between elements, as they exist between objects, aspects and their nodes.
• curved lines represent relationships.
• each element and each relationship has an identifier.
• those elements have an apostrophe in their name which are a copy, and otherwise have the same identifier as their origin (A' is a copy of A).
• the target of the copy action is always T (Target)
• the source of the copy is always the element N (Node).
• the element S is a connected element and Q is its parent element.
• the source element also has a child element C and a parent element P.

Copy situations

Assume an existing relationship from N to S that has at least outgoing read permissions.

• P,N,C real; Q,S real; R real
• P,N,C real; Q,S real; R imaginary
• P,N,C real; Q real; R imaginary; S imaginary
• P,N real; Q,S real; R real; C imaginary
• P real; Q,S real; R imaginary;N,C imaginary
• P real; Q real; N,C imaginary; R imaginary; S imaginary
• P,N,C imaginary; Q,S imaginary; R imaginary
• P,N,C real; Q,S real; R real
• P,N,C real; Q,S real; R imaginary
• P,N,C real; Q real; R imaginary; S imaginary
• P,N real; Q,S real; R real; C imaginary
• P real; Q,S real; R imaginary; N,C imaginary
• P real; Q real; R imaginary; S,N,C imaginary
• P real; R imaginary; Q,S,N,C imaginary

Possible configurations of a CopyButton

A CopyButton can represent various scenarios and must be configured differently depending on the scenario. The configuration takes place mainly via the COPY formula itself, through which a wide range of possibilities can already be set. In addition, settings can also be stored for the button in the Designer, which affect the behavior and the function.

Configurations using the COPY formula::

1. Copying a TLE with initial editing of the TLE form; all sub-elements and elements connected via relationships are also copied.

   = COPY ( SOURCE )

2. Copying a TLE with initial processing of the TLE form; the copied element is set to the specified status; the scope of the copy is specified by RANGE.

   = COPY ( SOURCE | STATE ; RANGE )

3. Copy a TLE with initial editing of the specified form; all subelements and elements connected via relationships are also copied.

   = COPY ( SOURCE ; ; DATASHEET )

4. Copy a TLE with initial editing of the TLE form and transfer of the values entered by the user from another TLE; all sub-elements and elements connected via relationships are also copied.

   = COPY ( SOURCE ; ; ; DATASHEET )

5. Copying a TLE with initial editing of form A and transfer of the values entered by the user for form B; all sub-elements and elements connected via relationships are also copied.

   = COPY ( SOURCE ; ; DATASHEET A ; DATASHEET B )

6. Copies a TLE with initial processing of the specified form; the copied element is set to the specified status; the copy range is specified by RANGE, but must not be SINGLE, since the form to be processed must be part of the copy set.

   = COPY ( SOURCE ; ; DATASHEET | STATE ; RANGE )

7. Copying an aspect form

   = COPY ( SOURCE ; TARGET )

8. Copy an aspect form; the copied element is set to the specified status; the scope of the copy is specified by RANGE.

   = COPY ( SOURCE ; TARGET | STATE ; RANGE )

9. Copying an aspect form with initial editing of a subordinate form

   = COPY ( SOURCE ; TARGET ; DATASHEET )

10. Copying an aspect form with transfer of the values entered by the user from another form

    = COPY ( SOURCE ; TARGET ; ; DATASHEET )

11. Copying an aspect form with initial processing of a subordinate form A and transfer of the values of form B entered by the user.

    = COPY ( SOURCE ; TARGET ; DATASHEET A ; DATASHEET B )

12. Copying an aspect form with initial processing of the subordinate form A; the copied element is set to the specified status; the copy scope is specified by RANGE, but must not be SINGLE, since the form A to be processed must be part of the copy set.

    = COPY ( SOURCE ; TARGET ; DATASHEET A | STATE ; RANGE )

Configurations in the Designer::

1. Relationship to Copy - Automatically creates a relationship between the form with the CopyButton and the created copy when copying. Direction, right and type of the relationship can be defined. If a data sheet is stored in the COPY formula for editing, the relationship to this element is created.
2. Name of targets - Defines in which format possible copy targets should be displayed in the Copy dialog. The dialog is only displayed if several copy targets are stored in the COPY formula.
3. With form - Determines whether a data sheet is offered to the user for editing when copying. If this function is deactivated, the data sheet is simply copied as it is. Data sheets specified for editing in the COPY formula are ignored in this case.

Formula dependencies

By creating formulas, a dependency network is built internally in REWOO Scope. This describes what effects certain actions have on other parts of the modelled world. For example, the content of a "VAT" field with the formula =(@Invoice Amount / 100) * 19 is not only calculated once, but all future changes to the "Invoice Amount" field also lead to an automatic re-evaluation of the "VAT" field. This is expressed by a dependency from the field "Invoice amount" to the field "VAT".

Dependency types

The dependency graph consists of change nodes of the following types, which result directly from the possibilities of the formula language:

• Value change - recalculation of a field value. This is caused by manually entering a value or by "simple" field references.
• State change - Change of an element state. This can flow into the formula language via STATE and SETSTATE expressions.
• Name change - The element name changes. This event can flow into the model via PARENTNAME, for example.
• Time change - The current time or date has changed. This can be queried in the formula language, e.g. via TODAY.

Note

Usually, it does not matter to the modeler which concrete change nodes make up the dependency graph. However, there are some special cases where some basic understanding of dependency types helps to explain the behavior of the model. These are discussed below.

Rules when processing the dependency network

The dependency graph is processed topologically sorted, i.e. the change node that is no longer dependent on any other change is always evaluated next. If the situation arises that two or more change nodes are not dependent on any other change, the evaluation sequence is in principle random, but status changes are always given priority.

Dies zeigt exemplarisch die folgende Abbildung, welche ein Datenblatt zusammen mit seinem Abhängigkeitsgraph bei Änderungen des Feldes "Schließen" zeigt. Beim Setzen des Boolean-Felds "Schließen" wird sowohl eine Statusänderung (durch die SETSTATE-Formel im Feld "Abschluss") als auch eine Wertänderung (durch die Datums-Formel im Feld "Abschlussdatum") verursacht. Durch die bereits erwähnte Präzedenz von Statusänderungen wird aber die SETSTATE-Formel zuerst ausgeführt. Eine Auswirkung auf die Berechnungsergebnisse hat die Regel in diesem Fall jedoch nicht. Dies ist erst im nächsten Beispiel der Fall.

Problem case: SETSTATE(ARCHIVED) / SETSTATE(REJECTED)

As soon as an element (or one of its fathers) is archived or discarded during an evaluation, no further formulas on the data sheet are evaluated. This is necessary because the archiving of an element should preserve the "current state" of the data sheet as far as possible. Without a precedence rule, however, this would cause a problem in the example explained earlier if the SETSTATE formula archived the element instead of closing it. It would then be random whether or not the Close Date field would be evaluated and written, depending on whether the value change was performed first or the state change was performed first. In this case, the precedence rule results in clearly predictable behavior: The status change is preferred to the value change, so the completion date is never set.

However, the precedence rule does not solve all problem cases. It is still possible for the modeller to move the status change backwards in the evaluation sequence by means of further intermediate calculation steps. However, we strongly advise against this, since the result of the evaluation run can then no longer be predicted. For this reason, you should heed the following rule in connection with SETSTATE(ARCHIVED) and SETSTATE(REJECTED):

If an element is set to the states ARCHIVED or REJECTED by a formula, no other formulas on the same data sheet should refer directly or indirectly to the field that initially causes the status change. Otherwise, unpredictable results may occur when evaluating the formulas.

Best Practice

Buttons

§ Filtering with the ViewButton for a boolean

Problem: In a table view, only jobs that are not yet completed should be displayed. 
Prerequisite: Table view with corresponding Boolean field
Solution:

1. Define table view: Open orders (Boolean field created as a column).
2. TableViewButton with filtering (type: ViewButton)

   = TABLEVIEW ( @Orders | @done? = {false} )

Explanations:

• Displays the table view filtered by the specified condition.
• The field in the condition does not necessarily have to be created as a column in the table.

§ ConnectionButton, which only offers destinations that meet a condition

Problem: The stored list of a ConnectionButtons should only display such elements according to a criterion on the side of the target elements.
Prerequisite: Sibling elements, which have a Boolean field on the form. On the parent element a list of child elements.
Solution:

1. On the parent data sheet:
   List of children (Type: ElementLinks)

   = LINKLISTIF ( CHILDREN | @Boolean )

2. On the Sibling Data Sheet:
   Connect to Sibling (Type: ConnectionButton)

   = PARENT@List of children

Explanations: The filtering of whether a child element is included in the list occurs on the parent element. So the ConnectionButton shows only a selected list.

Nachrichten / Alerts

§ One-time notification when a form is created

Problem: As soon as a new form is created, a message should be sent once.
Solution:

1. Hidden field to send the message (Type: Condition)

   ? 1=1 : MESSAGE ( staff ; {A new form was created.} | * ; t )

§ Send message when a value has exceeded several thresholds

Problem: A message is to be sent when the thresholds 15, 30 or 45 are exceeded. However, no message is to be sent if the value changes within two thresholds.
Prerequisite: A field with a numerical value
Solution:

1. Field with a numerical value (Type: Number)
2. Control field (Type: Number), that returns the interval in which the current value is located.
   

   = COUNTTRUE ( @Value >= 15 ; @Value >= 30 ; @Value >= 45 )

3. The field which checks the interval (Type: Boolean)

   ? AND ( (@Value <= 15 * @alpha) ; (VERSION (@Value | -1) > 15*@alpha) ) : MESSAGE(staff)

§ Watch the FileLinks field and send a message when a change (upload, remove) has been made.

Problem: The project team should receive a message as soon as the central business case file has been changed or a new one has been uploaded.
Requirement: A FileLinks field, into which the file is uploaded.
Solution:

1. Business Case (Type: FileLinks)
2. hidden field for monitoring and triggering the action (Type: Condition)

   ? AND (COUNT (VERSION(@Business Case | -1) - (@Business Case)) = 0; COUNT ( (@Business Case) - VERSION (@Business Case | -1)) = 0 : MESSAGE( compliance; {Change at Business Case!} | *;f)

Explanations: In each of the two conditions, set operations are performed to check whether the previous version is different from the current version.

Date calculations

§ Does a specified date interval fall within the current week?

Problem: Start and end of an operation is indicated. I am interested if the current week is affected and with how many days.
Prerequisite: The key date must be present as a field (Type: Date)
Solution:

1. Field for the start or end of the current week (Type: Date):
   Start current week or End current week
2. Field for the start or end of the operation (Type: Date):
   Start or End
3. Days of the action in this week (Type: Number)
   

   = WORKDAYS( MIN( MAX( @Start ; @Start current week) ; @End current week ) ; MAX( MIN( @End;@End current week ); @Start current week) ) - SUMIF( 1| OR( @End<@Start current week ; AND( @Start=@End ; @End<@Start current week) ) )

Explanations: To exclude Saturdays and Sundays, one uses the functionality of WORKDAYS. The correction term takes care of an exception case.

§ What is the date of the current start of the week?

Problem: For further calculations, the date from Monday of the current week is required.
Solution:

1. Date for this week Monday (Type: Date)

   = TODAY - WEEKDAY(TODAY) + 1

§ When is an employee's next birthday?

Problem: When is an employee's next birthday?
Prerequisite: The employee's date of birth must exist as a field (Type: Date).
Solution:

1. Birthday (Type: Date)
2. Next birthday (Type: Date)

   = PERIODIC (@Birthday | 1 year)

§ Which employee's birthday is next and when?

Problem: The parent form should show which employee's birthday is next and on what date.
Prerequisite: Parent form with subordinate employee data sheets.
Solution:

1. Fields on employee data sheets:
   Birthday (Type: Date)
   Next birthday (Type: Date)

   = PERIODIC (@Birthday | 1 year)

2. Fields on parent form:
   Next birthday (Type: Date)

   = MIN (CHILDREN)

   Birthday child (Type: ElementLinks)

   = LINKLISTIF(CHILDREN | @NextBirthday = THIS@NextBirthday)

Explanations: Also suitable for anniversaries.

§ In which quarter does a key date fall?

Problem: A key date is to be valuated according to its quarter.
Prerequisite: the key date must exist as a field (type date).
Solution:

1. Field for the key date (Type: Date)
2. Field for the quarter (Type: Number)

   = ( MONTH (@key date/1 month) +2 ) MOD 3

Explanations: The offset of 2 is needed so that the division does not result in zero. If you want to transform the problem (in which half-year does the key date fall), you must replace the offset by 5 and the division by 6.

§ Notification when a new value has been entered for a date field

Problem: The end of a project is recorded in a Date field. As soon as a change is made in the field, a message is to be sent.
Prerequisite: A date field that is to be monitored.
Solution:

1. Project end (Type: Date)
2. Monitoring field (Type: Condition)

   ? AND( DAY (@project end) > 0; (VERSION (@project end | 0) - VERSION (@project end | -1)) = 0 ): MESSAGE (time; {project end changed}| *;f)

Explanations:

• The first part of the AND condition ensures that no message is sent when a project end is first set.
• Monitoring is also possible for Number fielder. However, the first part of the AND condition must be "OR (@number / @number = 1;@number=0)".

§ How many working days are between project start and project end?

Problem: Working days between start and end date count, start/end should be integrated and weekend days should be excluded. Thus, one assumes a 5-day week.
Prerequisite: 2 date fields (start and end date), number field for number of working days
Solution (easy):

• Number of working days (Type: Number)

  = WORKDAYS (@Start date; @End date)

Solution (complex):: To match user-defined workdays (if not Monday-Friday).

• Number of working days (Type: Number)

  = ( DAY ( @End date - @Start date ) + 1 )-( ( ( WEEKDAY ( @Start date ) + ( DAY ( @End date - @Start date ) + 1 ) ) DIV 7 + ( ( WEEKDAY ( @Start date ) + ( DAY ( @End date-@Start date ) + 1 ) ) DIV 7 ) ) - COUNTTRUE ( OR ( WEEKDAY ( @Start date ) = 7 ; WEEKDAY ( @End date ) = 6 ) ) ) + COUNTTRUE ( AND ( WEEKDAY ( @Start date ) = 7 ; WEEKDAY ( @End date ) = 6 ) )

Explanations: WORKDAYS counts Monday through Friday as working days by default. Saturday and Sunday are not counted.

History data fields

§ Mean value consideration for trend

Problem: A numerical value changes constantly. Since a threshold value observation is not sufficient to get a feeling for the quality of the change, an average value is to be formed over a selected period and compared with the current value.
Prerequisite: A number field must exist over a defined period of time (e.g. 30 days) and must have been filled with values.
Solution:

1. 1. the number field to observe (type: Number) and its average value over the last 30 days: account balance.

   = SMA (@account balance | 30)

2. the trend over the last 30 days is to be interpreted by an arrow (Type: TrafficLight)

   ? [@mean - @account balance = 0; @mean - @account balance > 0]

Explanations: It is important to note that outliers can strongly influence the mean. To suppress such outliers, one would need sophisticated filter functions.

§ Ramp-Up of a monthly payout depending on today's month and the payout start date

Problem: A monthly amount is to be paid out. In the first month 30%, in the second month 60% and from the third month the full 100% should be paid out. The start date is to be set by the user. The amount to be paid out in the next three months is to be displayed.
Prerequisite: Field with start date and field with the monthly total amount.
Solution:

1. Start Payout (Type: Date)
2. Monthly Amount (Type: Number)
3. Payout this month (Type: Number)

   = (1 - COUNTTRUE (MONTH (@Start Payout) = MONTH (TODAY)) * 0.7 - COUNTTRUE (MONTH(@Start Payout) = MONTH (TODAY) -1 month) * 0.4 - COUNTTRUE (MONTH (@Start Payout) > MONTH (TODAY)) ) * @monthly amount

4. Payout coming month (Type: Number)

   = (1 - COUNTTRUE (MONTH (@Start Payout) = MONTH (TODAY) +1 month) * 0.7 - COUNTTRUE (MONTH (@Start Payout) = MONTH (TODAY)) * 0.4) * @monthly amount

5. Payout in two months (Type: Number)

   = (1 - COUNTTRUE (MONTH (@Start Payout) = MONTH (TODAY) + 2 month) * 0.7 - COUNTTRUE (MONTH (@Start Payout) = MONTH (TODAY) + 1 month) * 0.4) * @monthly amount

§ Generate last uploaded document in the overview

Problem: On several child forms there are FileLinks fields where users can upload files. In the parent overview, one now wants to keep the overview and display the last or the last uploaded files.
Prerequisite: The versioning of the type must be switched on, otherwise this solution does not work.
Solution:

1. The following fields are accessible to the user on the child forms: photos (type: FileLinks) as well as documents (type: FileLinks).
2. On the parent form, these fields should be summed on the child forms.

   = SUM (CHILDREN)

3. On the parent form there needs to be an additional field to display the version difference: Last uploaded files (type: FileLinks)

   = (@Photos - VERSION ( @Photos | -1 )) + (@Documents - VERSION ( @Documents | -1 ))

Explanations: In this example, the two input fields are considered separately, meaning each field represents the most recently uploaded document. If you want to get the globally last document, you have to sum up both fields on the child element and then merge them in the parent element. This is how you disguise the difference.

Traffic lights

§ Traffic light which switches off under a condition

Problem: A TrafficLight field can display green, yellow or red. However, a traffic light can also be switched off and display nothing if the value to be interpreted is "not evaluable".
Prerequisite: A traffic light field with a condition that allows the traffic light to reach the 4th state.
Solution:

1. Field for the traffic light (Type: TrafficLight)

   ? [ Condition1 ; Condition2 ]

2. with the following boolean statement. If the condition is not fulfilled, the traffic light turns off

   ?[AND (Condition1 ; ( 1 / SUMIF (1 | Condition3)) = 1) ; Condition2]

Explanations: The traffic light turns off as soon as condition3 is not fulfilled, as this invalidates the complete yellow condition (division by 0).

§ Check if a traffic light has the 4th state (=switched off)

Problem: If a traffic light is switched off, a corresponding Boolean field should indicate this and trigger further actions if necessary Solution:

1. Traffic light (Type: TrafficLight)
2. Field for the check (Type: Boolean)

   ? NOT (AND ( @trafficlight[0] ; @trafficlight[1] ; @trafficlight[2] ))

Explanations: The result is true if the state of the traffic light is not in the defined set - in this case, off.

§ Referencing of TrafficLight or traffic light control depending on other traffic lights

Problem: A traffic light should switch depending on the states of the child traffic lights. The traffic light should turn yellow if more than four (child) traffic lights are red. More than six red traffic lights result in a red traffic light on the parent element.
Prerequisite: min. one traffic light field on the child elements
Solution:

1. higher-level traffic light (Type: TrafficLight)

   ? [COUNTTRUE (CHILDREN[1]) > 4 ; COUNTTRUE (CHILDREN[1] > 6]

Explanations:

• CHILDREN[0] references yellow traffic lights, CHILDREN[1] references red traffic lights, and CHILDREN[2] references green traffic lights.
• In this example, the traffic lights on the child elements must also be called "status" (i.e. the same traffic light field).
• If different fields are to be used, they must be listed individually in the COUNTTRUE formula.
  Example:

  COUNTTRUE ( CHILDREN@trafficlight1[1] ; CHILDREN@trafficlight2[1])

• If the traffic light fields do not occur on all child elements, the direct reference must be used:
  

  COUNTTRUE (@childname@ampel1[1] ; @childname@ampel1[1] ; etc.).

• If you want to query the state of a traffic light field on the same form, replace CHILDREN with the reference name (e.g. @ampel[1]).

Lists

§ Check whether a single element occurs in an (ElementLinks) list

Problem: In a list (type: ElementLinks) project involved persons are listed. Now it should be checked on the form of a person whether this person is also in the list.
Prerequisite: Existing ElementLinks field with the project participants; a form of a person on which the check is to be performed.
Solution:

1. Field for project stakeholders (Type: ElementLinks)
2. Field for the check (Type: Boolean)

   ? @Participants  >= LINKLIST (THIS)

Explanations: different quantity comparisons are possible:

• >= This or more elements occur in the list
• > This and other elements must occur in the list
• = Only this element may occur in the list

§ Checking whether an element appears in a list (indirect variant)

Problem: Various elements are enumerated in a list. It should now be checked whether a certain element is present in this list.
Prerequisite: A list (Type: ElementLinks)
Solution:

• List (Type: ElementLinks)
• Field for the element to be searched for (Type: ElementLinks)

  = LINKLIST (THIS)

• Check in separate field (Type: Boolean)

  ? COUNT(@List) - 1 = COUNT((@List - @Element))

• Alternative solution:

  ? AND (COUNT (@Element) > 0 ; COUNT ((@List - @Element)) + 1 = COUNT (@List))

Explanations: (@List - @Element) returns a list without the searched element (if it exists)

§ Count elements in a list

Problem: In a list, elements are referenced under fulfillment of various conditions (LINKLISTIF). The number of elements referenced there is now to be determined.
Prerequisite: A list of different items
Solution:

1. List of elements (Type: ElementLinks)
2. Number of elements (Type: Number)

   = COUNT (@List with elements)

§ Sorting into a list according to a selection made in a choice field

Problem: A list should only display entries for which a certain selection has been made via a Choice field.
Prerequisite: An existing Choice field with at least two choices.
Solution:

1. State (Type: Choice)
2. List field with elements (Type: ElementLinks)

   = LINKLISTIF (CHILDREN | @State = {fluid}

§ Check whether a specific entry is set in a choice field.

Problem: Verify that a specific Choice selection has been selected.
Prerequisite: existing Choice field with X choices.
Solution:

1. Choice (Type: Choice)
2. Check field (Type: Boolean)

   ? @Choice[X]

   Alternative solution:

   ? @selection={liquid}

Explanations:

• X must be replaced here by a number of the Choice selection. The index starts with 0. Example: Choice field with 3 answers corresponds to Choice[0], Choice[1] and Choice[2].
• Alternatively, the set value can be referenced directly. Here, however, it is important that the exact notation is observed.

Further examples

§ The output of a total must be output with a unit or currency.

Problem: Numerical values are to be entered for the child elements. Since I can't force the user to report correctly in Euro, at least the sum should appear in Euro.
Prerequisite: Number field
Solution:

1. Amount in Euro (Type: Number)

   = SUM ( CHILDREN@Number field ) + 0 €

Explanations:

• Due to the +0 € the sum is always represented with Euro.
• Instead of the Euro sign, many other SI units can be used.

§ Summation only of values for which a condition is true

Problem: A total is to be formed only from the elements on whose form a Boolean field is true.
Prerequisite: Boolean field
Solution:

1. Checkbox (Type: Boolean)
2. Value (Type: Number)

   = SUMIF (CHILDREN | @Checkbox)

§ Automatic status change based on the process structure

Problem: In a process chain, the successor process should become active as soon as its predecessors have completed their phase.
Prerequisite: A process chain must be defined by using Prev/NextLinks.
The user must set each of its phases from PLANNING to ACTIVE when the phase begins or from ACTIVE to CLOSED when the phase is completed.
Solution:

1. Hidden field on each form for checking (Type: Boolean)

   ? AND (COUNT (PREV) > 0; COUNTIF (PREV | STATE < CLOSED) = 0) : SETSTATE (ACTIVE |f;t)

Explanations: The formula is fault-tolerant against the case where no predecessors are defined. In addition, the change between ONHOLD and ACTIVE is ignored.

§ Navigable route at Google-Maps with start-destination address

Problem: A direct link to Google Maps is to be generated from two addresses, which displays a route suggestion.
Prerequisite: Two filled String fields with the respective start and destination address.
Solution:

1. Start address (Type: String)
2. Destination address (Type: String)
3. Route suggestion (Type: ExternalLinks)

   = {http://maps.google.de/maps?saddr=} + @Start-Adress + {&daddr=} + @Target-Adress

§ Link to XING search

Problem: A link is to be provided on a form that automatically searches the XING network for a given name.
Prerequisite: a filled string field with the name and possibly another string field with the location.
Solution:

1. Name (Type: String)
2. Location (Type: String)
3. XING search (Type: ExternalLinks)

   = {https://www.xing.com/publicsearch/query?utf8=%E2%9C%93&search[q]=} + @Name + {+} + @Location + {&send=1}

Explanations: As a result, the XING search opens with the corresponding data.