Table row number

Ignition
1

Is there a way to show the row number in a table? So if I bound the data in a table to a SQL query, is there a way to show row number 1 to x as another column?

2

Easiest is probably:

SELECT 0 AS 'MyRowNumber', ......

In the configureCell extension function of the table:

if colName == "MyRowNumber":
	return {"text":rowIndex+1}
1 Like
3

Awesome, thank you!!

4

Alternatively most databases have some way to do it, SQL Server has the ROW_NUMBER() functionality for instance, and MySQL there are work arounds to get it. Depending on the size of your dataset you may want to put it in your SQL query for speed, but you also probably wouldn’t notice any difference till it’s quite large.

5

I’m using MySQL and have a query/stored procedure that works, but my issue was finding a way to call it. I wasn’t able to use a trigger…

“MySql Error: Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger”

Any idea on how best to call query or procedure?

6

Sure, you want to use this

https://docs.inductiveautomation.com/display/DOC79/system.db.execSProcCall

normally the set up is like so

call = system.db.createSProcCall( "my stored procedure")
system.db.execSProcCall(call)

7

Can use the following for MySQL

SELECT (@row_number := @row_number + 1) AS 'RowNumber',m.ID, m.Description
FROM mytable m
CROSS JOIN
(SELECT @row_number := 0) AS r
8

What I meant, was how/when to call this? I don’t want to call it all of time. Really only after update/insert/delete. Problem is it might not be updated/inserted/deleted from Ignition, which is why I was hoping to use the trigger in SQL.

I’m probably over-thinking this…

9

Is there any way to call this after sorting occurs? This method works fine, unless you start sorting, then it’s not useful at all. It’s usually not a huge deal, but in this case each column is editable and I need a way to select multiple rows, so I need a column used for grabbing.

Any options?

10

Any takers on this one? My goal is to simply show a line number from top to bottom, starting at 1. But, the order needs to remain intact regardless if other columns are sorted.

11

Run your dataset through my view() expression function with PseudoSQL as follows:

view("SELECT *, _r+1 AS row", {Root Container.path.to.property})

{ Bind the original source to a custom property, then bind table.data as above. }