Select array-element from a database with the "form" node

#1

Hi together,
I built the folowing flow:

I use it to get data from a database. I have to choose the element of the array manually.
In my example it is element 1.

This is the corresponding code:

Node "Datenabfrage_FROM_orders:
msg.topic = "SELECT * FROM orders";
return msg;

Node "Abfrage Auftragsinformationen":
//choose an element from the array of the database
var o_id = 1;

var number_items = msg.payload[o_id].number_items;
var number_finished = msg.payload[o_id].number_finished;

var itemstatus = {};
itemstatus.labels = ["Herzustellen", "Fertiggestellt"];
itemstatus.data = [[number_items, number_finished]];
itemstatus.series = ["Sales"];

var prozent_finished = parseInt([(number_finished / number_items)*100])

var test = itemstatus.data[0][1];

return [{payload:[itemstatus],topic:msg.topic},
{payload: prozent_finished},
{payload: test},
{payload: o_id}
];

This works and the data are shown on the dashboard.

But now I want to choose an element from the array by tiping in a number on the dashboard and not changing the function node "Abfrage Auftragsinformationen" all the time manually. My plan is to use the "form" node.

I have tried a lot of ways to realise this plan, but I have only little experience.
There are always new errors (e.g. syntax errors) and I have absolutly no idea how to realise my idea.

Can somebody help me, please.

If it is not understandable what I wanted to explain or if you need more information, please ask.

Tank you in advance

(Sorry, as a new member I'm just allowed to post 1 image)

0 Likes

#2

Supplement:

This is the array from the database

0 Likes

#3

Supplement 2:

And this is what I want to do

0 Likes

#4

It sounds like you want some user input from the dashboard to filter the records returned by your Sql database query. So for instance, if there was a dropdown of the o_id values, selecting one of them could send that value in the msg.payload to a node that includes that value in your query statement -- so the msg.topic sent to the Sql node would be in this format:

SELECT * FROM orders WHERE o_id = 1;

There are several ways to take the UI form output and build the query string (e.g. using a function, template, or change node). This is more efficient than returning all the database rows and selecting the desired row by index number.

0 Likes

#5

First thanks a lot for your reply. It helped me solving my problem.

When everything works as designated I will try out some other (more efficient) ways to take the UI form as you suggestet.

BR Tim

0 Likes