Compact Table Scripting and Add IF statement to columnData.append

Ignition
1

I have a script running the following code. As is, columns “5x5” and “RB” are initialized to 1 (all added columns are set to Boolean in the Table). I want to change it to initialize these the columns based on what is contained in a column named ‘Description’ that comes from the query data. If the description column contains ‘5X5’ I want the 5x5 column to be set to true. See the screen shot for how the table should look upon initialization. Thanks

table_shot.JPG822×401 61.2 KB 

po = event.source.value
data = system.db.runNamedQuery("BOM/PoDetails",{"po":po})
############################################################################################
ds1 = data
colCount = ds1.getColumnCount()
columnName = "5x5"
columnData = []
for i in range(ds1.getRowCount()):
 columnData.append(1)
   
ds2 = system.dataset.addColumn(ds1, colCount, columnData, columnName, int)
event.source.parent.getComponent('Table3').data = ds2
############################################################################################
ds3 = ds2
colCount = ds3.getColumnCount()
columnName = "4x8"
columnData = []
for i in range(ds3.getRowCount()):
 columnData.append(0)
 
ds4 = system.dataset.addColumn(ds3, colCount, columnData, columnName, int)
event.source.parent.getComponent('Table3').data = ds4
############################################################################################ 
ds5 = ds4
colCount = ds5.getColumnCount()
columnName = "RB"
columnData = []
for i in range(ds5.getRowCount()):
 columnData.append(1)
  
ds6 = system.dataset.addColumn(ds5, colCount, columnData, columnName, int)
event.source.parent.getComponent('Table3').data = ds6
###########################################################################################
ds7 = ds6
colCount = ds7.getColumnCount()
columnName = "HW"
columnData = []
for i in range(ds7.getRowCount()):
 columnData.append(0)
  
ds8 = system.dataset.addColumn(ds7, colCount, columnData, columnName, int)
event.source.parent.getComponent('Table3').data = ds8
############################################################################################
2

What I’ve tried so far

for i in range(ds1.getRowCount()):
 if columnName == "Description" and "5x5" in value:  
   columnData.append(1)
else: columnData.append(0)

I tried a FOR statement instead of the IF as well. I’m probably going about it wrong, but I don’t claim to be a programmer!

3

Just so we’re clear, the first three columns are from the query?

4

Yes the first 3 columns are from the query

5

Okay, I can write up some code that’s close, but it’ll take a few minutes. :slight_smile:

1 Like
6

I don’t know if this actually meets your requirements, but I couldn’t help not repeating myself :slight_smile:

po = event.source.value
data = system.db.runNamedQuery("BOM/PoDetails",{"po":po})

fields = ['5x5', '4x8', 'RB', 'HW']

for field in fields:
	columnData = []
	for row in range(data.getRowCount()):
		description = data.getValueAt(row, "DESCRIPTION")
		columnData.append(field in description)
	data = system.dataset.addColumn(data, data.getColumnCount(), columnData, field, int)

event.source.parent.getComponent('Table3').data = data
7

Looks much nicer than my rep rep repetitiveness ha ha. I got this code to work and initialize all Booleans to 0.
Your code threw an error…TypeError: can’t put element of type class java.lang.Booleanin column of type class java.lang.Integer

po = event.source.value
data = system.db.runNamedQuery("BOM/PoDetails",{"po":po})

fields = ['5x5', '4x8', 'RB', 'HW']

for field in fields:
	columnData = []
	for row in range(data.getRowCount()):
		##description = data.getValueAt(row, "Description")## This line Commented out
		columnData.append(0)
	data = system.dataset.addColumn(data, data.getColumnCount(), columnData, field, int)

event.source.parent.getComponent('Table4').data = data
8

To me, working directly with datasets is kind of clunky. I prefer using PyDataSets.


dataIn = system.dataset.toPyDataSet(system.db.runNamedQuery("BOM/PoDetails",{"po":po}))

headers = ['FRAME', 'QUANTITY', 'DESCRIPTION', '5x5', '4x8', 'RB', 'HW']
dataOut = []

for row in dataIn:
  newRow = []

# Add old data into new row  
  for col in row:
    newRow.append(col)

# Check for 5x5	
  if '5x5' in row['Description']:
    newRow.append(1)	
  else:
    newRow.append(0)

# Check for 4x8
  if '4x8' in row['Description']:
    newRow.append(1)
  else:
    newRow.append(0)

# Add RB Value
  newRow.append(1)

# Add HW Value
  newRow.append(0)
  
#Add new row to the data going out.  
  dataOut.append(newRow)
  
event.source.parent.getComponent('Table3').data = system.dataset.toDataSet(headers, dataOut)
1 Like
9

HA! Nice work JordanCClark! I did have to make 2 very small adjustments… Change the Ifs to DESCRIPTION not Description, and change ‘5x5’ to ‘5 X 5’ to match the query formatting. Much appreciated.

po = event.source.value
dataIn = system.dataset.toPyDataSet(system.db.runNamedQuery("BOM/PoDetails",{"po":po}))

headers = ['FRAME', 'QUANTITY', 'DESCRIPTION', '5x5', '4x8', 'RB', 'HW']
dataOut = []

for row in dataIn:
  newRow = []

# Add old data into new row  
  for col in row:
    newRow.append(col)

# Check for 5x5	
  if '5 X 5' in row['DESCRIPTION']:
    newRow.append(1)	
  else:
    newRow.append(0)

# Check for 4x8
  if '4 X 8' in row['DESCRIPTION']:
    newRow.append(1)
  else:
    newRow.append(0)

# Add RB Value
  newRow.append(1)

# Add HW Value
  newRow.append(0)
  
#Add new row to the data going out.  
  dataOut.append(newRow)
  
event.source.parent.getComponent('Table4').data = system.dataset.toDataSet(headers, dataOut)
1 Like
10

Consider the view() expression function from Simulation Aids, like so:

view("Select *, "+
  "'5 X 5' in DESCRIPTION as _5x5, "+
  "'4 X 8' in DESCRIPTION as _4x8, "+
  "True as RB, "+
  "False as HW", {Root Container.path.to.query.property})

The only thing it can’t do is create a column name that starts with a digit.

11

Fixed that. This beta of Simulation aids works with output column names that aren't valid python identifiers. You can use a quoted string as an output column name too, if you want spaces or other special characters.

12

I might have to give this a try

13

(-:
Your named query should be able to be bound to a custom property, with a reference to the value from your PO entry field. With that and view(), you should not need any other python.

14

On another note…… Once the table is generated, how can I make the Boolean columns dependent upon each other? In other words, if ‘5x5’ Boolean is checked, then the adjacent ‘4x8’ Boolean can’t be checked. It’s either one or the other, not both. Is that possible?

15

That’ll have to be enforced in your cell edit event function.

2 Likes