# Compact Table Scripting and Add IF statement to columnData.append

**URL:** <https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358>\
**Category:** Ignition\
**Created:** [June 8, 2018, 2:32pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358 "2018-06-08T14:32:00Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 8, 2018, 2:32pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/1 "2018-06-08T14:32:00Z")

</div>

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

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/fa6d7a15bafdfd5f3e84fd0cb1316e7844f055ff.JPG)

```auto
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
############################################################################################
```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 8, 2018, 2:42pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/2 "2018-06-08T14:42:07Z")

</div>

What I’ve tried so far

```auto
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!

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 8, 2018, 2:43pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/3 "2018-06-08T14:43:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 8, 2018, 2:44pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/4 "2018-06-08T14:44:24Z")

</div>

Yes the first 3 columns are from the query

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 8, 2018, 2:47pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/5 "2018-06-08T14:47:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 8, 2018, 2:49pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/6 "2018-06-08T14:49:16Z")

</div>

I don’t know if this actually meets your requirements, but I couldn’t help [not repeating myself](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself) 🙂

```auto
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

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 8, 2018, 3:12pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/7 "2018-06-08T15:12:18Z")

</div>

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

```auto
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
```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 8, 2018, 3:16pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/8 "2018-06-08T15:16:14Z")

</div>

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

```auto

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)

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 8, 2018, 3:28pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/9 "2018-06-08T15:28:16Z")

</div>

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.

```auto
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)
```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 8, 2018, 4:33pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/10 "2018-06-08T16:33:32Z")

</div>

Consider the [view() expression function](https://www.automation-pros.com/simaids/doc/expression.view.html) from [Simulation Aids](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids), like so:

```auto
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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 8, 2018, 5:04pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/11 "2018-06-08T17:04:09Z")

</div>

> [@pturmel](#):
>
> The only thing it can't do is create a column name that starts with a digit.

Fixed that. [This beta](https://www.automation-pros.com/ignition/Simulation%20Aids-1.7.7.1806081654.modl) 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.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 8, 2018, 6:56pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/12 "2018-06-08T18:56:45Z")

</div>

I might have to give this a try

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 8, 2018, 7:02pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/13 "2018-06-08T19:02:32Z")

</div>

(-:  
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.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 8, 2018, 7:07pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/14 "2018-06-08T19:07:06Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 8, 2018, 7:44pm UTC](https://forum.inductiveautomation.com/t/compact-table-scripting-and-add-if-statement-to-columndata-append/18358/15 "2018-06-08T19:44:16Z")

</div>

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