Formula Node
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
- First, make sure you’ve selected your desired solution and are now in Rayven Workflow.
- Select ‘Functions’ from the left-hand panel.
- Find the Formula node and drag it onto the canvas.
- Connect the Formula node to your desired input node.
- Double click on the Formula node to open its configuration window.
Configuring your Formula node
- Give your node a Name. It’s best to choose something simple that clearly explains the node’s purpose.
- 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.
- 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.
- 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).
- Set the desired decimal precision for your output value.
- 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.
- 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>>
- Takes the value field_name
- Goes to Custom Table custom_table_ID
- Looks for a value of reference_column_name that matches field_name
- Returns the value of the corresponding target_column_name
Formula Name (noun.verb.modifier) | Formula Template | Help Text (explanation of function and arguments) |
[[ ]] | Takes value from flow field | |
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). |
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). |
TIME.TOUNIXTIME | Tounixtime([[Variable]],format) | Converts a date and time to unix time, which is the number of seconds since epoch (1/1/1970). |
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