How do I sum the values of a column of a table component?

Ignition
1

Good night, my name is Felipe and I’m new to Ignition. I received a mission to add the hfiNumber values ​​of each process machine (eg cupper, bodymaker, decorator) to the ignition screen next to each machine. I intend to place a numeric label next to each machine on the client’s screen informing hfiNumber. I made this little application that writes the values ​​in a table named hfi from a MySQL database. The script in the Submit button is:

timeStamp = event.source.parent.getComponent('Label').text
line= event.source.parent.getComponent('Dropdown 1').selectedStringValue
machine = event.source.parent.getComponent('Dropdown').selectedStringValue
hfis=event.source.parent.getComponent('Spinner').intValue
description = event.source.parent.getComponent('Text Area').text

query = "INSERT INTO `ign_historical`.`hfi` (timeStamp, Line, Machine, hfiNumber, Description) VALUES (?,?,?,?,?)"

args = [timeStamp, line, machine, hfis, description]


system.db.runPrepUpdate(query,args)

image
image1363×725 74.4 KB

os valores do componente tabela vem de uma consulta SQL

SELECT * FROM hfi

However, as the values ​​filled in by the factory’s quality department staff (Line, Machine, hfiNumber and description) will be fed throughout the day, I don’t know how I add the hfi number values ​​of a machine during an interval shift, for example: the Cupper machine from line 22, from 7:00 am to 7:00 pm, on April 4, hfiNumber is equal to 4, the Cupper machine from line 23 hfiNumber is equal to 2 (as shown in the example in the image). I do not know how I add these values ​​for a given machine during a certain time interval and the values ​​will be filled in randomly by the quality department

I tried to do this using the lookup script function, but it only returns the value of the row above the specified column, and I need the sum within a time interval, since the process is divided by shift.

the other question is also that I added edit and delete buttons, in case I want to edit a line or delete a line directly from the database. but I have no idea how to edit or delete a selected line.

In this small project I tried to use inserting data directly into the database. but if anyone has a new and more practical idea of ​​how the people in the quality department would insert these values ​​into the ignition and how I can get the data from hfiNumber will be very welcome. As I’m new to Ignition, I still have a lot to learn

thank you very much!!!

2

Hello Felipe,

summing up the hfiNumber for a specific machine and a specific date could be done in SQL. As I’m using MSSQL, I’m not 100% sure about the correct syntax for MySQL.

SELECT 
    SUM(hfiNumber) as hfi
    , date(timeStamp) as dt
FROM hfi
  
where
	machine like 'Cupper'
	and HOUR(dt)>=7 and HOUR(dt)<19
group by date(timeStamp)
order by dt

This should give you the sum of hfiNumbers for the Cupper machine for the 7am to 7pm shift for each day.

In order to edit or delete a line you just need to read the selectedRow property of your table, get the values (see http://forum.inductiveautomation.com/t/how-to-extract-the-value-of-the-selected-row/8817) and then run the wanted SQL update or delete command.

3

Hola !!!
Estoy intentando realizar la suma de una columna de mi base de datos, pero al hacerlo en un script de Ignition me marca el siguiente error:

Caused by Exception: Error executing system.db.runPrepUpdate(SELECT SUM(Flujo) AS Flujo FROM [dbo].[Consumo_L] WHERE [Id_consumo] = ?, [8], SUMIN_AGUA, , false, false)
caused by SQLServerException: Se ha generado un conjunto de resultados para actualización.

Y esta la consulta que estoy utilizando:
query = “SELECT SUM(Flujo) AS Flujo FROM [dbo].[Consumo_L] WHERE [Id_consumo] = ?”
args = [Id_consumo]
system.db.runPrepUpdate(query,args)

Espero me puedan ayudar a encontrar mi error.