Contents x
    Formula Node
        Contents

        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

            What's Next