Formula Node

      Formula Node


        Article summary

        Formula Node

        The Formula node can be used to perform complicated calculations or to modify values. It can access values from previous JSONs and use them in calculations with the current JSON.

        Adding a Formula node to your workflow

        1. First, make sure you’ve selected your desired solution and are now in Rayven Workflow.
        2. Select ‘Functions’ from the left-hand panel.
        3. Find the Formula node and drag it onto the canvas.
        4. Connect the Formula node to your desired input node.
        5. Double click on the Formula node to open its configuration window.


         

        Configuring your Formula node

        1. Give your node a Name. It’s best to choose something simple that clearly explains the node’s purpose.
        2. Enter the 'Output Field Name' where you want the output data to be stored. If you use a field name that already exists in the incoming JSON the original value will be overwritten.
        3. Choose whether to select a formula template from the dropdown list. The selected formula will be added to the formula text field and can be modified.
        4. Enter the formula for your required calculations in the free text field. A list of the possible formula node functions can be found below. They are listed in NOUN.VERB format (for example, TIME.CALCTIMESPAN).
        5. Set the desired decimal precision for your output value.
        6. Select how the node should behave if there is an error in the code:
          • Stop and log the error,
          • Continue and ignore the error,
          • Continue and add the error to the JSON.
        7. Choose how the node should respond to a coding error. The default is to add the error text to the JSON. If the 'Output Zero if Value Not Found' checkbox is selected a 0 will be added as the value in the output field name if an error occurs.

        Formula node functions

        This table provides a comprehensive overview of all the functions possible with the Formula node. Customize the formula templates for your own needs using the formula syntax rules explained below. 

        Formula Syntax

        • Use [[ ]] to take value from the flow field. For example if incoming JSON is {“my_value”:”50”} you would write [[my_value]] in formula. Use [[! !]]to return an empty string if value is not found instead of throwing an error.
        • Use [[my_value(-1)]] to get value from LAST incoming JSON. (-2) for from 2 before and so on. Put inside single quotes ('[[my_value(-1)]]') if the value will be a string
        • Use [[my_value(-1dL)]] to get the last value from the previous day. use "h" for hours. "m" for minutes."month" for months. Put inside single quotes if the value will be a string
        • Example: "-2dL" now it is 7:36AM Jan 10th. Code will find the last value before 0:00AM on Jan 9th.
        • Example: "-1dL" now it is 7:36AM Jan 10th. Code will find the last value before 0:00AM on Jan 10th (previous day).
        • Example: "-5hL '' now it is 7:36AM. Code will find last value before 3:00 AM
        • Use [[my_value(-1dF)]] to get the first value from the previous day. same as above but will go forward from the time chosen. Put inside single quotes if the value will be a string
        • Use [[my_value(same_date)]] to get value from Cassandra using the date-time from the current node (as sent via timeStamp variable). If current JSON has that variable it will use what is in current JSON (since it obviously is the correct date-time)


        Example

        Use << >> to take value from a custom table as follows:  

        <<custom_table_ID.reference_column_name.target_column_name.field_name>> 

        1. Takes the value field_name 
        2. Goes to Custom Table custom_table_ID 
        3. Looks for a value of reference_column_name that matches field_name
        4. Returns the value of the corresponding target_column_name 

        Formula Name (noun.verb.modifier)

        Formula Template
        (formula with standardised arguments)

        Help Text (explanation of function and arguments)

        [[ ]]

        Takes value from flow field
         
         [[field_name]]: Get value from field_name key in JSON object
         [[! !]]: Return empty string if value not found
         [[field_name(-1)]]: Get value from LAST incoming JSON
         [[field_name(-1dL)]]: Get last value from previous day
         *Change bold letter to:
         - h: hours
         - m: minutes
         - month: months

         [[field_name(-1dF)]]: Get first value from previous day
         *Change bold letter to:
         - h: hours
         - m: minutes
         - month: months
         
         [[field_name(same_date)]]: Get value using date-time from current node. Returns value if variable exist in current JSON
         ‘[[field_name]]’: Return value from field_name key in String format
         
         AGGREGATE Functions of Last Value
         [[field_name<device_group.avg>]]
         [[field_name<device_group.cnt>]]
         [[field_name<device_group.min>]]
         [[field_name<device_group.max>]]
         [[field_name<device_group.sum>]]


        CUSTOMTABLE.AGGREGATE

        <<custom_table_ID.column_name.aggr>>

        Performs a calculation using each value from the specific column name. The available functions are average, sum and count and they are configured using the aggregate field when the custom table column is created. Use aggr as the argument and the formula will automatically use the function that has been configured.

        CUSTOMTABLE.LOOKUP

        <<custom_table_ID.reference_column_name.target_column_name.field_name>>

        Return the value in target_column_name from a custom table, by doing a lookup from the field_name against reference_column_name

        DATA.QUERYCASSANDRAHISTORY

        Querycassandrahistory('field_name',integer)

        Checks a certain number of historical payloads, as configured in the integer argument. Returns True if the value of field_name exists in previous payloads, otherwise returns False.

        DEVICE.COUNT

        <[CountDevices]>

        Returns a count of all devices.

        DEVICE.LATITUDE

        <[Latitude]>

        Returns Latitude from the device configuration.

        DEVICE.LONGITUDE

        <[Longitude]>

        Returns Longitude from the device configuration.

        DEVICE.NOW.TZ

        <[System.DeviceTZNow]>

        Returns current date/time in device timezone.

        DEVICE.PAYLOAD.TZ

        <[System.DeviceTZTimeStamp]>

        Returns payload date/time in device timezone.

        LOGIC.IF

        If(Expression,Value_Is_True,Value_Is_False)

        Returns a value based on a condition.

        LOGIC.IN

        In(comma_separated_list)

        Returns whether an element is in a set of values.

        MATH.ABS

        Abs(Variable)

        Returns the absolute value of a specified number.

        MATH.ACOS

        Acos(Variable)

        Returns the angle whose cosine is the specified number.

        MATH.ASIN

        Asin(Variable)

        Returns the angle whose sine is the specified number.

        MATH.ATAN

        Atan(Variable)

        Returns the angle whose tangent is the specified number.

        MATH.CEILING

        Ceiling(Variable)

        Returns the smallest integer greater than or equal to the specified number.

        MATH.COS

        Cos(Variable)

        Returns the cosine of the specified angle.

        MATH.EXP

        Exp(Variable)

        Returns e raised to the specified power.

        MATH.FLOOR

        Floor(Variable)

        Returns the largest integer less than or equal to the specified number.

        MATH.IEEE.REMAINDER

        IEEERemainder(Input,Divisor)

        Returns the remainder resulting from the division of a specified number by another specified number.

        MATH.LOG

        Log(Variable, Base)

        Returns the logarithm of a specified number.

        MATH.LOG10

        Log10(Variable)

        Returns the base 10 logarithm of a specified number.

        MATH.MAX

        Max(Variable1, Varable2)

        Returns the larger of two specified numbers.

        MATH.MIN

        Min(Variable1, Variable2)

        Returns the smaller of two numbers.

        MATH.POW

        Pow(Variable,Exponent)

        Returns a specified number raised to the specified power.

        MATH.RANDOM

        Random(D)

        Returns random decimal number between 0 and 1.

        MATH.RANDOMBETWEEN

        Random(From_Integer,To_Integer)

        Returns random integer from first variable up to but not including second variable.

        MATH.ROUND

        Round(Variable,Decimal_Places)

        Rounds a value to the nearest integer or specified number of decimal places. The rounding behaviour for a middle number can be changed by using EvaluateOption.RoundAwayFromZero during construction of the Expression object.

        MATH.SIGN

        Sign(Variable)

        Returns a value indicating the sign of a number.

        MATH.SIN

        Sin(Variable)

        Returns the sine of the specified angle.

        MATH.SQRT

        Sqrt(Variable)

        Returns the square root of a specified number.

        MATH.TAN

        Tan(Variable)

        Returns the tangent of the specified angle.

        MATH.TRUNCATE

        Truncate(Variable)

        Calculates the integral part of a number.

        REPO.GETDATA

        Repo(repo_node_id, 'from_date', 'to_date', 'field_name_to_filter', filter_condition,'field_name_to_do_calculation_on' , AVG)

        Function to retrieve data from a repository node. Date formats are 'YYYY-MM-DD HH-MM-SS' Example: REPO(9452,'2019-01-01 00:00:00','2019-05-01 00:00:00','status','on','temp','AVG'). Aggregate functions: MIN,MAX,CNT,SUM,AVG

        SYSTEM.DEVICE.TZ.Name

        <[System.DeviceTZName]>

        Returns Time Zone Name.

        SYSTEM.DEVICE.TZ.TIMESTAMP

        <[System.DeviceTZTimeStamp]>

        Converts into a string (including single quotes) with the current DateTime in the devices time zone (as defined in the device group) in format YYYY-MM-DD HH:MM:SS (can be used with DATEPART function).

        SYSTEM.NOW.UTC

        <[System.UTCNow]>

        Converts into a string (including single quotes) with the current Date-Time (UTC) in format YYYY-MM-DD HH:MM:SS (can be used with DATEPART function).

        TAG.CALC.NAME

        [[my_value{your_tag.tag_name.avg}]]

        Averages the value in 'my_value' field for all devices with a value for the selected tag (sum/cnt/min/max can also be used).

        TAG.CALC.VALUE

        [[my_value{your_tag.tag_value.avg}]]

        Averages the value in 'my_value' field for all devices with the same tag value (sum/cnt/min/max can also be used).

        TAG.COUNT.NAME

        <[count_devices.your_tag.tag_name]>

        Returns a count for all devices with a value for the selected tag.

        TAG.COUNT.UNIQUE.VALUE

        <[count_unique_tag_values.your_tag]>

        Returns an output for each unique tag value accross all devices in the selected tag.

        TAG.COUNT.VALUE

        <[count_devices.your_tag.tag_value]>

        Returns a count for all devices with the same value in the selected tag.

        TAG.COUNTVALUES.NAME

        Countvalues('field_name To Count',my_tag_name.tag_name')

        Returns a count for each number of times each value appeared based on last value received from all devices with the same tag name.

        TAG.COUNTVALUES.VALUE

        Countvalues('field_name To Count','my_tag_name.tag_value')

        Returns a count for each number of times each value appeared based on last value received from all devices with the same tag value.

        TAG.VALUE

        <[device_tag_value.your_tag]>

        Returns the tag value set in the device.

        TIME.CALCTIMESPAN

        Calctimespan('field_name', 'start value', 'end value', bool)

        Returns a count in seconds using payload timestamp from the first instance of 'start' value being received, until the first 'end value' is received. If the boolean is true, the output is the time from the last broadcast to now. If false, the output is the time from start to now.

        TIME.DATEADD

        Dateadd(<[System.UTCNow]>,'DAY',integer)

        Function will add the number of time with the second arguement as the unit,from the current time. (can also use minus to remove time). Second argument can receive: SECOND,MINUTE,HOUR,DAY,MONTH,YEAR. Third argument: integer to determine how many seconds, minutes, hours etc. to be added.

        TIME.DATEPART

        Datepart(datePart,[[Variable]])

        Extracts an element from a date string. datePart is the element to extract and accepts YEAR, MONTH, DAY, HOUR, MINUTE,HMS (returns HHmmss), or YMDHMS (returns yyyyMMddHHmmss).
         Variable is a string argument format must be 'yyyy-MM-dd HH:mm:ss'.

        TIME.TIMEPART

        Timepart(timePart,[[Variable]])

        "Extracts an element from a time string. timePart is the element to extract and accepts cepts HOUR, MINUTE, SECOND, HMS (returns HHmmss).
         Variable is a string argument format must be : 'HH:MM:SS'

        TIME.TOUNIXTIME

        Tounixtime([[Variable]],format)

        Converts a date and time to unix time, which is the number of seconds since epoch (1/1/1970).
         Variable is the date and time to be converted. format is a string of tokens describing the first argument. Note that this is case sensitive (MM - Month, dd - Day, yyyy - Year, hh - Hour, mm - Minute, ss - Second).

        TIME.COUNTDAYSSINCE

        Countdayssince(TimeStamp,format)

        Count the number of days that have passed since a specific timestamp. Timestamp parameter can be yyyy-MM-dd, yyyy-MM-dd HH:mm:ss or Epoch. The format parameter is optional, however if an Epoch timestamp is supplied then the format parameter must be EPOCH.


        See Also: Creating Device Labels
        As of Release: 14.10.21