How to access data in a data table and use it to write a cumulative function

#1

Hi,
I am creating a system to estimate remaining shelf-life of perishable products. I have a device that measures temperature and humidity. With the temperature I can calculate the degradation rate (k) for each measurement using the MATH function in workflows and store the value in a data table.

Now, I would like to calculate an average k_ by adding all the previous measurement’s k value to the next measurement’s k value, and store the cumulative value in the table row with the latest measurements. How can I do this calculation with Losant workflows.

0 Likes

#2

For this use case, I am assuming you are only storing one row per device in your data table, and you are updating that row each time the device reports state.

First, I would create a column in your data table for ‘deviceId’, which is the ID of the device reporting temperature and humidity. (I’m assuming you’ll have multiple devices reporting.) Give it a type of “string” and a constraint of “unique”.

Your table should also include a column for “remainingShelfLife”. I don’t know specifically how you’re recording this data but I assume it’s a number, and that there should always be a value in the column, so set the type to “number” and the constraint to “required”. Or, if all devices have the same initial shelf life, you can set a default value for the column.

In your workflow, start with a Device: State Trigger and configure it to fire when any of your temp / humidity devices reports state. (Easiest way to do that is to put a common device tag on all of those devices and set the config on the trigger to match that tag.)

Connect that trigger to a Table: Get Rows Node and query by deviceId (column) === {{triggerId}} (device that reported state, from the payload).

If the query fails to return a row (check with a Conditional Node), then this device has never reported before, in which case you’ll want to use a Table: Insert Row Node to create a row for this device and set an initial value for shelfLife (if necessary).

After the row is created – or, if the row already exists – you can do your calculation to determine the new value for shelfLife based on the temperature and humidity in your state report and the value of shelfLife from your row. You said you were already doing this with a Math Node. Then, use a Table: Update Row Node to set the new shelfLife value in your row.

I should note that if you want to use our dashboards to visualize the shelf life degradation over time, you cannot currently do so directly from the values in your data table. Instead, you would have to store the remaining shelf life as an attribute on the device. This would be pretty easy to do by simply chaining a Device State Node on to the end of the workflow and setting the value of the attribute to the new value you’re setting in the table row. But if you go that route, you will need to guard against infinite loops in your workflow, as using this node will trigger another run per your Device: State Trigger at the top of the workflow. You can do this by adding another Conditional Node immediately after the trigger checking for the existence of the “shelfLife” attribute in the state report. If it does not exist, you may proceed with the workflow as described above. If it does, you should stop the workflow there.

0 Likes