# Sort Power Table on Multiple Columns

**URL:** <https://forum.inductiveautomation.com/t/sort-power-table-on-multiple-columns/31342>\
**Category:** Ignition\
**Tags:** scripting\
**Created:** [January 16, 2020, 3:38pm UTC](https://forum.inductiveautomation.com/t/sort-power-table-on-multiple-columns/31342 "2020-01-16T15:38:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![c.bertulli](https://avatars.discourse-cdn.com/v4/letter/c/f19dbf/32.png) [@c.bertulli](https://forum.inductiveautomation.com/u/c.bertulli)\
**Post date:** [January 16, 2020, 3:38pm UTC](https://forum.inductiveautomation.com/t/sort-power-table-on-multiple-columns/31342/1 "2020-01-16T15:38:06Z")

</div>

Hi All,

Is it possible to sort a Power Table on multiple columns through scripting?  
if yes, how?

Thanks in advance,  
Cheers,

---

<div class="post-metadata">

**Author:** ![martinel3746](https://avatars.discourse-cdn.com/v4/letter/m/e0b2c6/32.png) [@martinel3746](https://forum.inductiveautomation.com/u/martinel3746)\
**Post date:** [January 16, 2020, 11:02pm UTC](https://forum.inductiveautomation.com/t/sort-power-table-on-multiple-columns/31342/2 "2020-01-16T23:02:41Z")

</div>

No, the dataset.sort only sorts on a single column.  
I had this question last week and exchanged email with support.  
Clearly the best answer would be to use SQL sort the data before it gets the client but, when that’s not an option…  
Here’s an example of what works for me:

```auto
	# get a copy of the data in the Power Table
	editsToBeSaved = root.getComponent( 'Power Table' ).data
	# sort the data to be sure records are processed in the correct order - only sorts on one column but preserves prior order
	editsToBeSaved = system.dataset.sort( editsToBeSaved, 'task' ) # last
	editsToBeSaved = system.dataset.sort( editsToBeSaved, 'operation' )	# 2nd
	editsToBeSaved = system.dataset.sort( editsToBeSaved, 'process' ) # 1st
	# for row in range( editsToBeSaved.rowCount ):
		# do whatever needs to be done here

```

I am expecting (and so far, getting) the sort order of the prior sorts to be preserved.  
The alternative would be to loop through the records and sort them yourself.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [January 17, 2020, 5:43pm UTC](https://forum.inductiveautomation.com/t/sort-power-table-on-multiple-columns/31342/3 "2020-01-17T17:43:26Z")

</div>

No way natively but you can use itemgetter and sort on multiple columns and then assign the dataset back to the table.

```auto
from operator import itemgetter
tbl = event.source.parent.getComponent('Power Table') #Get the table control
oDS = system.dataset.toPyDataSet(tbl.data) #Convert to a pyDataSet
hdrs=['Col1','Col2','Col3'] #The column names of the table
oDS = sorted(oDS, itemgetter(0,1,2)) #Sort the DS on the column numbers you want
tbl.data = system.dataset.toDataSet(hdrs,oDS) #Assign the new DS to the table

```

[https://docs.python.org/3/library/operator.html#operator.itemgetter](https://docs.python.org/3/library/operator.html#operator.itemgetter)

---

<div class="post-metadata">

**Author:** ![c.bertulli](https://avatars.discourse-cdn.com/v4/letter/c/f19dbf/32.png) [@c.bertulli](https://forum.inductiveautomation.com/u/c.bertulli)\
**Post date:** [January 20, 2020, 1:21pm UTC](https://forum.inductiveautomation.com/t/sort-power-table-on-multiple-columns/31342/4 "2020-01-20T13:21:15Z")

</div>

Thanks for the answer. It’s really an interesting method.  
I tried your code but it gave me the error:" **TypeError: an integer is required** at line `oDS = sorted(oDS, itemgetter(0,1,2))` so after a little Google search it became `oDS = sorted(oDS, key=itemgetter(1,4,8))`.  
Now it throws an error on the last line `tbl.data = system.dataset.toDataSet(hdrs,oDS)` which is `java.lang.ClassCastException: class org.python.core.PyObjectDerived cannot be cast to class org.python.core.PySequence (org.python.core.PyObjectDerived and org.python.core.PySequence are in unnamed module of loader java.net.URLClassLoader @a3549b1)`  
I think it’s because oDS is a list of pyRow and the function `system.dataset.toDataSet` is expecting a list of lists.

---

<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:** [January 20, 2020, 2:32pm UTC](https://forum.inductiveautomation.com/t/sort-power-table-on-multiple-columns/31342/5 "2020-01-20T14:32:34Z")

</div>

Sounds like a job for ORDER BY in my [view() expression function](https://www.automation-pros.com/simaids/doc/expression.view.html) in [Simulation Aids](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids). You’d put your original binding on a custom property, then the view() expression on the data property. You can use a string property to hold the Pseudo-SQL and change it to change the ordering.