Need help with table setup

Ignition
1

I have to setup a table that lists 4 columns: displayname, field, cost center, and poll. the data needs to be grouped by a different column named ftg ftg is a bit datatype in my table. I basically need it setup just like this attached image.

The ones on the top are ftg = 0 and the ones on the bottom are ftg = 1. I guess my problem comes in that I need to list the total count of the ftg =0 below the ftg = 0 wells and then also list total count of ftg = 1 below the ftg = 1 wells.

I need this in a table so that it can be exported to csv.

table.jpg469×680 154 KB

2

Well, you have two choices: try to write a single query that returns the expected results or create the dataset in scripting. It is possible to bring back those results in a single SQL query, but it will be a nasty looking query using UNIONs. It is best to generate the results in scripting. Here is an example:[code]header = ["", “Field”, “Cost Center”, “May”]
data = []
res = system.db.runQuery(“SELECT * FROM table WHERE ftg = 0”)
data.append([“FOC Wells”, “”, “”, “”])
for row in res:
data.append([row[0], row[1], str(row[2]), str(row[3])])
data.append(["", “”, “Total”, str(len(res))])

data.append(["", “”, “”, “”])

res = system.db.runQuery(“SELECT * FROM table WHERE ftg = 1”)
data.append([“FTG Wells”, “”, “”, “”])
for row in res:
data.append([row[0], row[1], str(row[2]), str(row[3])])
data.append(["", “”, “Total”, str(len(res))])

table.data = system.dataset.toDataSet(header, data)[/code]You can create a global function in the Script Modules that can return this dataset. Add a new function like so:def returnDS(): import system .... return system.dataset.toDataSet(header, data)and bind the table’s data to the following expression:runScript("app.table.returnDS()", 10000)The second argument can be how fast you want to run the script, in my case 10 seconds. To learn more about script modules look here:

inductiveautomation.com/supp … odules.htm

Let me know if you have any questions or if you want to try the single query idea.

3

thanks travis.

4

Ok, got it working… but is it possible to add bolding to only certain items? I would like to Bold Ignition billing, and the location/field/cost center, and the count and actual count number

I kind of modified it a little.

header = ["", “”, “”]
data = []
res = system.db.runQuery(“SELECT d.displayname, f.name, d.cost_center FROM devices d right outer join fields f on d.fieldid = f.id where d.ftg = 0 order by f.name asc”)
data.append([“IGNITION BILLING”, “”, str(system.tag.getTagValue("[System]Client/System/CurrentDateTime"))])
data.append(["", “”, “”])
data.append([“Location”, “Field”, “Cost Center”])
data.append([“FOC Wells”, “”, “”])
for row in res:
data.append([str(row[0]), str(row[1]), str(row[2])])
data.append([“COUNT”, “”, str(len(res))])

data.append(["", “”, “”])

res = system.db.runQuery(“SELECT d.displayname, f.name, d.cost_center FROM devices d right outer join fields f on d.fieldid = f.id where d.ftg = 1 order by f.name asc”)
data.append([“FTG Wells”, “”, “”])
for row in res:
data.append([str(row[0]), str(row[1]), str(row[2])])
data.append([“COUNT”, “”, str(len(res))])

event.source.getComponent(‘Table’).data = system.dataset.toDataSet(header, data)

5

You can make it bold by either using HTML or the Font Mapping for individual cells. It is better to use the Font Mapping since the HTML will get exported in the CSV. In this case you need to add an additional column that is an integer column mapping a number to a font for the Ignition billing column. You need to give the column a name and the value will be 0 for a regular font and 1 for a bold font. Once you have the extra column you can go into the table customizer and specify the font mapping for the Ignition billing column. You will also need to give the Ignition billing column a name other than empty string. There you will see at the bottom a way to map the font based on the additional column you added and you can just create the mapping. Hope that helps.