Control Node-RED Flows with groov View

One way to have Node-RED react to some groov View action like a button press is to read the data from a groov data store tag, and then make a decision based on the value that is returned, for example fetching some information or activating some function.
Here is a simple example that generates a random number when you press an action button in groov View:


image

These are the steps the flow goes through to process and react to the button press:

  1. Node-RED reads the “action” button value once every second.
  2. The value is ignored unless it has changed compared to the last value.
  3. The value is accepted if it is true, so only when the button is turned on.
  4. The button is reset to false AND the random number is generated.

A button gadget is used instead of a momentary gadget so that you just press the button once and it latches on until Node-RED starts the action and toggles it back off. If a momentary gadget were used and pressed for less than one second, it’s possible that Node-RED would never read it!

To set this up in groov View, create or select a groov data store to hold the button variable, in this example I have a “NodeRED” data store with an “action” boolean for the button and a “debug” string tag to hold the result.

image

Outside of the randomizer this is how you would do some generic action, just replace DO ACTION with what ever task you want it to activate:

Here is the flow you can import into Node-RED:

[{"id":"73401010.ffcd1","type":"switch","z":"65e0d8c3.e0ef98","name":"is true","property":"payload","propertyType":"msg","rules":[{"t":"true"}],"checkall":"true","repair":false,"outputs":1,"x":470,"y":960,"wires":[["1d2c9b69.c355c5","8abf8602.1fb2b8"]]},{"id":"8db3554b.5ce7c8","type":"rbe","z":"65e0d8c3.e0ef98","name":"","func":"rbei","gap":"","start":"","inout":"out","property":"payload","x":350,"y":960,"wires":[["73401010.ffcd1"]]},{"id":"1d2c9b69.c355c5","type":"groov-write-ds","z":"65e0d8c3.e0ef98","dataStore":"894197e9.8e9408","tagName":"action","tableStartIndex":"","value":"false","valueType":"value","name":"","x":610,"y":940,"wires":[[]]},{"id":"9746615d.73346","type":"groov-read-ds","z":"65e0d8c3.e0ef98","dataStore":"894197e9.8e9408","tagName":"action","tableStartIndex":"","tableLength":"","value":"","valueType":"msg.payload","topic":"","topicType":"none","name":"","x":230,"y":960,"wires":[["8db3554b.5ce7c8"]]},{"id":"c207bdc0.3aa0b","type":"inject","z":"65e0d8c3.e0ef98","name":"1s","topic":"","payload":"","payloadType":"date","repeat":"1","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":960,"wires":[["9746615d.73346"]]},{"id":"add4b1cb.2b878","type":"groov-write-ds","z":"65e0d8c3.e0ef98","dataStore":"894197e9.8e9408","tagName":"debug","tableStartIndex":"","value":"","valueType":"msg.payload","name":"output","x":790,"y":1000,"wires":[[]]},{"id":"43bb7a59.8cc854","type":"debug","z":"65e0d8c3.e0ef98","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":790,"y":960,"wires":[]},{"id":"8abf8602.1fb2b8","type":"function","z":"65e0d8c3.e0ef98","name":"randomizer","func":"min = 0;\nmax = 100;\nresult = min + (Math.random() * (max-min));\nreturn { payload : result };","outputs":1,"noerr":0,"x":630,"y":980,"wires":[["add4b1cb.2b878","43bb7a59.8cc854"]]},{"id":"894197e9.8e9408","type":"groov-data-store","z":0,"project":"360f71a2.fb70ee","dsName":"NodeRED"},{"id":"360f71a2.fb70ee","type":"groov-project","z":"","address":"localhost"}]

Happy coding!

1 Like

Hello! I was able to successfully reproduce the random number generation using a button in Groov View. My next task is to have a button in Groov View that executes a MSSQL query. A few weeks ago, in this post, I got everything going in terms of having Node-RED connect to our SQL database and populating values into my PAC Control strategy variables.

In the last part of the above post (control flows with groov View), the function node labeled as DO ACTION should obviously be replaced with the SQL query nodes from my other post, but that is where I am stuck. My flow was starting with an Inject node then followed by an MSSQL node then the PAC write node. I believe I can put some JavaScript into the function node that will name the location, password, etc. of the database and also put the SQL query in there, but that’s a bit over my head (I have no JavaScript experience whatsoever). If possible, I would prefer to skip the function node and use the Inject node like I had been doing originally, but Inject nodes do not have input connections and therefore I do not believe I can use it.

If I’m understanding your setup correctly you should be able to skip the function block entirely without issue, as long as your MSSQL node settings are static.
Just delete the function node and replace that with your fully-configured MSSQL node directly wired to the “is true” switch node. That way your query will be triggered as if it was wired straight to an inject node like your current setup. It might look something like this:

Let me know if that doesn’t work for you or if you have any other questions.

That works, but for some reason I am only able to retrieve 1 variable (and remember I have about 10 that I am trying to pull from SQL and insert into my PAC Control strategy).

My MSSQL node looks like this. Note the query that I have written has the WorkOrderNumber hard coded into this for now (eventually I will use a PAC read node to populate this value).

For just 2 variables (AbbrCustName and PartNumber) and assuming WorkOrderNumber 71393, I can only retrieve the PartNumber. The AbbrCustName cannot be read for some reason (“Cannot read property ‘AbbrCustName’ of undefined”). I double checked everything and I cannot find any mistakes in my syntax.

In case it helps, here is what the PAC Write node for AbbrCustName looks like:
image

Thanks in advance.

Could you please put a debug node on the output of the MSSQL node and show exactly what’s coming from there? That will help figure out why payload[1] is undefined.

See below. FWIW, I can swap around AbbrCustName and PartNumber and can get AbbrCustName to be retrieved successfully but NOT PartNumber, so the problem seems to be only when I try to retrieve the 2nd, 3rd, 4th… field values from SQL.

The array only has one result (array[1]) and the Node-RED setup looks good, so I am weary of the SQL select statement. Can you do a select on the database from SQL management or some other client software to confirm that there are multiple entries matching that exact work number?
Additional rows should just come in as extra objects in the array.

You could also test this by getting everything with SELECT * FROM ... and that should for sure give you multiple rows, just to confirm it’s working on the Node-RED side.

If I’m understanding this correctly, you are trying to retrieve 1 row with multiple columns, not 10 rows of data, right?

Your screenshot of the AbbrCustName PAC write node has payload[1].AbbrCustName, which doesn’t exist. Should this be payload[0].AbbrCustName?

1 Like

Here is the same query (SELECT * FROM PermaTemp.tblFeederDataFromWORP1 WHERE WorkOrderNumber = 71393) executed in SSMS:

But Philip has won the prize…I am indeed trying to retrieve 1 row with multiple columns, and not 10 rows of data. And changing payload[1].AbbrCustName to payload[0].AbbrCustName fixed it.

Thank you both for all your help.

My only question now is what is the general approach to passing the SQL parameter for my query (in this case, the 5-digit work order number)? I have that value as an Int32 variable in my PAC Control strategy, so I presume a PAC Read node needs to be placed just before the MSSQL node. How does one write a query referencing the value from a PAC Read node?

2 Likes

You can put the value returned by the PAC Read node in the SQL statement by using mustache templates. If your SQL node doesn’t support mustache, you can build the SQL statement in a template node prior to the SQL call.

select * from tbl where id={{payload}}

Edit: Beware of SQL injection issues if you are doing things like this with strings.

1 Like

Awesome! That did the trick. I can now type in a 5-digit work order number in Groov View and all of the associated fields (customer name, part number, feedrate, temperature, etc.) come into PAC Control as variables.

This is really great stuff!

2 Likes

Hi Terry,
I’ve been using this flow a lot. But I add 500ms delay before reseting the action flag.
As I am writing now, it happened that, for some reason the reseting did not take place.
Therefore Action Flag is always 1… If this is the case, the flow will stop working, since the source logic will set the Flag to 1, and rbe will not let it pass.

Do you have idea of a fail safe, when resetting of flag did not occur?

I would drop the rbe - it isn’t gaining you anything in this case.

Edit- you would need to get rid of the delay though so the write action happens immediately

1 Like

I’m inclined to agree with Philip on this one, but I’m not sure why the reset didn’t take place even with the RBE there…

My first thought / gut reaction is to keep the delay, but increase it to 1s or even 1.1s so that it matches (or exceeds) the scan rate of groov View, in case the write events are overlapping somehow. The other thing that I have done when using this flow is to reset the flag after “DO ACTION” so that the flag is only reset when it is actually done processing, rather than after the request is made.

I would suggest trying to drop the RBE and see if that works, if so, great, if not, perhaps try the above.

It’s possible that node-red was updated and restarted during the delay causing the write to never go out. You could use this pattern which would make sure it gets cleared:

1 Like

Hi Philip,

Once again, thank you for sharing your bright mind.
Your flow is fullproof.

1 Like