# Automation Professionals' Integration Toolkit Module

**URL:** <https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934>\
**Category:** 3rd Party Modules\
**Tags:** ignition81, vision, ignition83, scripting, perspective\
**Created:** [June 13, 2023, 12:42am UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934 "2023-06-13T00:42:35Z")\
**Posts on this page:** 20\
**Page:** 1

<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 13, 2023, 12:42am UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/1 "2023-06-13T00:42:35Z")

</div>

Automation Professionals is pleased to announce the immediately availability of a major upgrade to its **free** ~~Simulation Aids~~ **Integration Toolkit** module. The new feature justifying the "major upgrade" language is the implementation of ~~**eleven**~~ **_thirty-one_** new expression functions which use **iteration** to accomplish complex transforms of datasets and lists, to or from other datasets and lists, applying filtering, ordering, and computed new columns along the way. While there are certain operations that are still only available in the view() expression function, most of its functionality is now available **without** calling jython code.

Available for Ignition v8.1 **only** : ~~v2.0.0.231640004~~

_Visit my [Module Sales](https://www.automation-pros.com/modulesales.html) page for the latest._

The list handling and generation code, including the handling of nested mappings, has no equivalent among the scripted functions, and is intended to accommodate the more complex data structures of Perspective. As with many of the technologies I implement, performance is the primary motivation for this work.

The online documentation has been updated ~~in place~~ , so both the module showcase and my Module Sales pages link to the [latest content](https://www.automation-pros.com/toolkit/doc/).

This functionality is also a stretch for the expression language foundation, as noted over here:

> [@Dynamic Dataset creation for template repeater Vision](https://forum.inductiveautomation.com/t/dynamic-dataset-creation-for-template-repeater-vision/74800/5):
>
> Funny you should say that. I had to stay home today, so my thumb twiddling has been off in that sort of left field....

Paul's statement, only four days old, is no longer true. 😁

There are debug loggers for the key iteration functions that report performance. Put "simaids" into the logger search box to get to them quickly.

If you would like a quick start to see these functions in action, I've been playing with this view:

> **View JSON**
>
> ```python
> {
> "custom": {},
> "params": {},
> "props": {},
> "root": {
> "children": [
> {
> "children": [
> {
> "custom": {
> "filtered": [
> "Folsom",
> "Helsinki",
> "San Francisco",
> "Washington, DC",
> "Wellington",
> "Sydney",
> "Wellington"
> ],
> "filteredDS": {
> "$": [
> "ds",
> 192,
> 1686616771960
> ],
> "$columns": [
> {
> "data": [
> "Folsom",
> "Helsinki",
> "Jakarta",
> "Madrid",
> "Prague",
> "San Diego",
> "San Francisco",
> "Shanghai",
> "Tokyo",
> "Washington, DC",
> "Wellington",
> "Delhi",
> "Dhaka",
> "Lagos",
> "Karachi",
> "Istanbul",
> "Cairo",
> "Mexico City",
> "London",
> "New York City",
> "Tehran",
> "Bogota",
> "Rio de Janeiro",
> "Riyadh",
> "Singapore",
> "Saint Petersburg",
> "Sydney",
> "Abidjan",
> "Dar es Salaam",
> "Wellington",
> "Los Angeles",
> "Berlin",
> "Jeddah",
> "Kabul",
> "Mashhad",
> "Milan",
> "Kiev",
> "Rome",
> "Chicago",
> "Osaka",
> "Bandung",
> "Managua",
> "Paris",
> "Shiraz",
> "Manila",
> "Montreal",
> "Guadalajara",
> "Dallas",
> "Yerevan",
> "Tunis"
> ],
> "name": "city",
> "type": "String"
> },
> {
> "data": [
> "United States",
> "Finland",
> "Indonesia",
> "Spain",
> "Czech Republic",
> "United States",
> "United States",
> "China",
> "Japan",
> "United States",
> "New Zealand",
> "India",
> "Bangladesh",
> "Nigeria",
> "Pakistan",
> "Turkey",
> "Egypt",
> "Mexico",
> "United Kingdom",
> "United States",
> "Iran",
> "Colombia",
> "Brazil",
> "Saudi Arabia",
> "Singapore",
> "Russia",
> "Australia",
> "Ivory Coast",
> "Tanzania",
> "New Zealand",
> "United States",
> "Germany",
> "Saudi Arabia",
> "Afghanistan",
> "Iran",
> "Italy",
> "Ukraine",
> "Italy",
> "United States",
> "Japan",
> "Indonesia",
> "Nicaragua",
> "France",
> "Iran",
> "Philippines",
> "Canada",
> "Mexico",
> "United States",
> "Armenia",
> "Tunisia"
> ],
> "name": "country",
> "type": "String"
> },
> {
> "data": [
> 77271,
> 635591,
> 10187595,
> 3233527,
> 1241664,
> 1406630,
> 884363,
> 24153000,
> 13617000,
> 658893,
> 405000,
> 11034555,
> 14399000,
> 16060303,
> 14910352,
> 14025000,
> 10230350,
> 8974724,
> 8825001,
> 8622698,
> 8154051,
> 7878783,
> 6429923,
> 5676621,
> 5535000,
> 5191690,
> 208374,
> 4765000,
> 4364541,
> 405000,
> 3884307,
> 3517424,
> 3456259,
> 3414100,
> 3001184,
> 1359905,
> 2908703,
> 2877215,
> 2695598,
> 2691742,
> 2575478,
> 2560789,
> 2229621,
> 1869001,
> 1780148,
> 1649519,
> 1495189,
> 1317929,
> 1060138,
> 1056247
> ],
> "name": "population",
> "type": "int"
> }
> ]
> },
> "rawData": [
> {
> "city": {
> "align": "center",
> "editable": true,
> "justify": "left",
> "style": {
> "backgroundColor": "#F7901D",
> "classes": "some-class"
> },
> "value": "Folsom"
> },
> "country": "United States",
> "population": 77271
> },
> {
> "city": "Helsinki",
> "country": "Finland",
> "population": 635591
> },
> {
> "city": "Jakarta",
> "country": "Indonesia",
> "population": 10187595
> },
> {
> "city": "Madrid",
> "country": "Spain",
> "population": 3233527
> },
> {
> "city": "Prague",
> "country": "Czech Republic",
> "population": 1241664
> },
> {
> "city": "San Diego",
> "country": "United States",
> "population": 1406630
> },
> {
> "city": "San Francisco",
> "country": "United States",
> "population": 884363
> },
> {
> "city": "Shanghai",
> "country": "China",
> "population": 24153000
> },
> {
> "city": "Tokyo",
> "country": "Japan",
> "population": 13617000
> },
> {
> "city": "Washington, DC",
> "country": "United States",
> "population": 658893
> },
> {
> "city": "Wellington",
> "country": "New Zealand",
> "population": 405000
> },
> {
> "city": "Delhi",
> "country": "India",
> "population": 11034555
> },
> {
> "city": "Dhaka",
> "country": "Bangladesh",
> "population": 14399000
> },
> {
> "city": "Lagos",
> "country": "Nigeria",
> "population": 16060303
> },
> {
> "city": "Karachi",
> "country": "Pakistan",
> "population": 14910352
> },
> {
> "city": "Istanbul",
> "country": "Turkey",
> "population": 14025000
> },
> {
> "city": "Cairo",
> "country": "Egypt",
> "population": 10230350
> },
> {
> "city": "Mexico City",
> "country": "Mexico",
> "population": 8974724
> },
> {
> "city": "London",
> "country": "United Kingdom",
> "population": 8825001
> },
> {
> "city": "New York City",
> "country": "United States",
> "population": 8622698
> },
> {
> "city": "Tehran",
> "country": "Iran",
> "population": 8154051
> },
> {
> "city": "Bogota",
> "country": "Colombia",
> "population": 7878783
> },
> {
> "city": "Rio de Janeiro",
> "country": "Brazil",
> "population": 6429923
> },
> {
> "city": "Riyadh",
> "country": "Saudi Arabia",
> "population": 5676621
> },
> {
> "city": "Singapore",
> "country": "Singapore",
> "population": 5535000
> },
> {
> "city": "Saint Petersburg",
> "country": "Russia",
> "population": 5191690
> },
> {
> "city": "Sydney",
> "country": "Australia",
> "population": 208374
> },
> {
> "city": "Abidjan",
> "country": "Ivory Coast",
> "population": 4765000
> },
> {
> "city": "Dar es Salaam",
> "country": "Tanzania",
> "population": 4364541
> },
> {
> "city": "Wellington",
> "country": "New Zealand",
> "population": 405000
> },
> {
> "city": "Los Angeles",
> "country": "United States",
> "population": 3884307
> },
> {
> "city": "Berlin",
> "country": "Germany",
> "population": 3517424
> },
> {
> "city": "Jeddah",
> "country": "Saudi Arabia",
> "population": 3456259
> },
> {
> "city": "Kabul",
> "country": "Afghanistan",
> "population": 3414100
> },
> {
> "city": "Mashhad",
> "country": "Iran",
> "population": 3001184
> },
> {
> "city": "Milan",
> "country": "Italy",
> "population": 1359905
> },
> {
> "city": "Kiev",
> "country": "Ukraine",
> "population": 2908703
> },
> {
> "city": "Rome",
> "country": "Italy",
> "population": 2877215
> },
> {
> "city": "Chicago",
> "country": "United States",
> "population": 2695598
> },
> {
> "city": "Osaka",
> "country": "Japan",
> "population": 2691742
> },
> {
> "city": "Bandung",
> "country": "Indonesia",
> "population": 2575478
> },
> {
> "city": "Managua",
> "country": "Nicaragua",
> "population": 2560789
> },
> {
> "city": "Paris",
> "country": "France",
> "population": 2229621
> },
> {
> "city": "Shiraz",
> "country": "Iran",
> "population": 1869001
> },
> {
> "city": "Manila",
> "country": "Philippines",
> "population": 1780148
> },
> {
> "city": "Montreal",
> "country": "Canada",
> "population": 1649519
> },
> {
> "city": "Guadalajara",
> "country": "Mexico",
> "population": 1495189
> },
> {
> "city": "Dallas",
> "country": "United States",
> "population": 1317929
> },
> {
> "city": "Yerevan",
> "country": "Armenia",
> "population": 1060138
> },
> {
> "city": "Tunis",
> "country": "Tunisia",
> "population": 1056247
> }
> ]
> },
> "meta": {
> "name": "Table1"
> },
> "position": {
> "basis": "400px",
> "grow": 1
> },
> "propConfig": {
> "custom.constList": {
> "binding": {
> "config": {
> "expression": "asList(\u0027Hello\u0027, \"Cruel\", \"World\", 56, \"Today\")"
> },
> "type": "expr"
> }
> },
> "custom.constMap": {
> "binding": {
> "config": {
> "expression": "asMap(\u0027key1\u0027, \u0027Value1\u0027, \"key2\", \"Another\", \u0027key3\u0027, \u0027Cruel\u0027, \u0027key4\u0027, \u0027World\u0027)"
> },
> "type": "expr"
> }
> },
> "custom.filtered": {
> "access": "PRIVATE",
> "binding": {
> "config": {
> "expression": "forEach({this.props.data}, if(it()[\u0027population\u0027] \u003c 1000000, unMap(it()[\u0027city\u0027], \u0027value\u0027), null))"
> },
> "previewEnabled": false,
> "type": "expr"
> },
> "persistent": true
> },
> "custom.filteredDS": {
> "access": "PRIVATE",
> "binding": {
> "config": {
> "expression": "unionAll(\n\tasList(\u0027city\u0027, \u0027country\u0027, \u0027population\u0027),\n\tasList(\u0027str\u0027, \u0027str\u0027, \u0027i\u0027),\n\tforEach(\n\t\t{this.props.data},\n\t\tasList(unMap(it()[\u0027city\u0027], \u0027value\u0027), it()[\u0027country\u0027], it()[\u0027population\u0027])\n\t)\n)"
> },
> "type": "expr"
> },
> "persistent": true
> },
> "custom.rawData": {
> "access": "PRIVATE"
> },
> "props.data": {
> "binding": {
> "config": {
> "expression": "forEach(\n\t{this.custom.filteredDS},\n\tasMap(it())\n)"
> },
> "type": "expr"
> }
> }
> },
> "type": "ia.display.table"
> }
> ],
> "meta": {
> "name": "FlexContainer"
> },
> "position": {
> "basis": "34px",
> "grow": 1
> },
> "props": {
> "direction": "column"
> },
> "type": "ia.container.flex"
> }
> ],
> "meta": {
> "name": "root"
> },
> "props": {
> "direction": "column"
> },
> "type": "ia.container.flex"
> }
> }
> 
> ```

Note: the new stuff is only lightly tested. The changes were sufficiently non-invasive that I consider the balance of the module to still be production-grade.

Edit: Name changed with v2.0.14 release.

## New Wiki Topic

As of April 14, 2025, for usage discussions and guidance. Please do not ask usage questions in **this** announcements topic going forward. Use the Wiki:

> [@Integration Toolkit Solutions Wiki](https://forum.inductiveautomation.com/t/integration-toolkit-solutions-wiki/105080):
>
> By popular request, I've created this topic to be used in wiki mode for collaborative generation of solutions to particular problems. To keep this neat, please follow these guidelines: New problems seeking solutions should be posted as replies, and the discussion of that problem kept within that comment, wiki-style. A one-line summary should be added to this topic starter, with an embedded link (not on its own line). After posting, be sure to convert the comment to operate in wiki mode.

---

<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 13, 2023, 12:59am UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/2 "2023-06-13T00:59:25Z")

</div>

I suppose I should note what isn't present, what might be present later, and what is not likely to ever work:

- GROUP BY

~~I have a rough idea how to do this part, but haven't nailed the details down.~~ _Implemented in v2.0.3._

- PIVOT

Depends on GROUP BY, and some dynamic column naming. Details to be worked out.

- HAVING

Not actually necessary as a separate function, as it can be accomplished by stacking the `where()` outside the future grouping function.

- LIMIT

Not necessary, as it can be accomplished by strategic placement of `where()` that checks `idx()`.

- JOIN (in various flavors)

~~There are ideas floating around....~~ _Implemented in v2.0.6._

- Tag reads (inside the iterator)

This probably will never work. The iteration functions rely on thread locals to carry state, and therefore must execute to completion. No callouts to the tag subsystem permitted. Even if that could be surmounted, I suspect the path-relative parsing support cannot tolerate my "pre-execution" methods.

~~If you are converting lists of tagpaths to tag values in transforms, I cannot help you. (And I think that is an anti-pattern--you should be indirect binding within a column view to display live tag values in tables, or indirect binding in templates in a template repeater.)~~ _Implemented_ `tags()` _in v2.0.12. Indirection through tables or repeaters is unbearably slow._

Edit: Marked the items above that are no longer "future" features.

---

<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 14, 2023, 3:52pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/3 "2023-06-14T15:52:56Z")

</div>

So I was poking around and realized I missed a major feature: a substitute for python's `range()` functions.

And while at it, I factored out some common code so that `where()` and `orderBy()` could handle and return sequences instead of just datasets. Of the four iterators, now only the `selectStart()` function requires and returns a dataset. `forEach()` still only **returns** a list, but can accept datasets, lists, arrays, and single integers (for range behavior).

Here ya go:

For Ignition v8.1+ only: ~~v2.0.1.231651531~~

---

<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 16, 2023, 5:06pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/4 "2023-06-16T17:06:48Z")

</div>

This summary of performance advice by @lrose is relevant here:

> [@Building for Performance](https://forum.inductiveautomation.com/t/building-for-performance/71082/3):
>
> In this example, #3 is the most performant, though it could be argued that the performance gain in this instance would be negligible. Anytime you add an additional processing step you add latency. In general for the best performance I follow these guidelines: If you can achieve what you want with a binding, use a binding. Always use the binding type that is the most direct. (e.g. if all you need is a binding to a property use a property binding, not an expression binding.) Given the choice b…

Note bullet point #3. That particular question applies to transforms as well as property change events.

---

<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 19, 2023, 9:17pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/5 "2023-06-19T21:17:29Z")

</div>

I have spent my holiday poking at this. Wanted to provide an efficient replacement for a common use of python transforms: Make a (Perspective) table that dynamically populates both `.data` and `.columns` from source dataset that isn't constrained to any particular structure or data types.

In Vision, one can achieve this (mostly) by pre-populating the column attributes dataset with all of the possible column names your application might deliver. No such thing in Perspective. But a transform can take the column names and types from a dataset, perform lookups on one or the other in a map of presets for `.columns`, and construct the corresponding entry.

I realized pretty quickly that I forgot to provide a way to iterate over the column information of a dataset, so there's now a `columnsOf()` expression to obtain that, and I realized that iterating over maps (as key-value pairs) is pretty handy, too. So that has been added to the iterators, and the `asList()` and `asMap()` functions have been updated to convert back and forth.

And I realized that I had no way to compare the performance of expression functions versus similar scripting solutions. I can't instrument the machinery of script transforms themselves, but I was able to instrument calls to `runScript()` as if calling a script transform. Should have close to the same jython overhead.

Anyways, I created a test case view for a "chameleon" table--takes any dataset tag via a tagpath parameter, and displays it with the simplest possible jsonifcation into `.data` and proof-of-concept for map-lookup generation of `.columns`. This is the library script that supports the python transforms:

```auto
from java.lang import Throwable
from com.inductiveautomation.ignition.common.xmlserialization import ClassNameResolver
from com.inductiveautomation.ignition.common.model.values import QualifiedValue
logger = system.util.getLogger('transforms')

resolver = ClassNameResolver.createBasic()

# Convert dataset to list of dictionaries for Perspective table data property.
def dsToJsonData(ds):
	headings = list(ds.columnNames)
	pyds = system.dataset.toPyDataSet(ds)
	return [dict(zip(headings, row)) for row in pyds]

def columnsOf(ds):
	return zip(ds.columnNames, map(resolver.getName, ds.columnTypes))

def deQualify(value):
	if isinstance(value, QualifiedValue):
		return value.value
	return value

def unMap(value, *args):
	value = deQualify(value)
	if isinstance(value, dict):
		for key in args:
			try:
				return deQualify(value[key])
			except:
				pass
	return value	

# Convert dataset columns to list of dictionaries for Perspective table columns property.
def dsToJsonColumns(ds, handlingMap):
	headings = list(ds.columnNames)
	try:
		return [dict(field=colName, header=dict(title=unMap(deQualify(handlingMap.get(colName, colName)), 'title'))) for colName in headings]
	except Throwable, t:
		logger.info("dsToJsonColumns() unexpected error", t)
	except Exception, e:
		logger.info("dsToJsonColumns() unexpected exception", shared.later.PythonAsJavaException(e))

```

The `resolver` is there for later decision-making based on column types, without having to deal with actual class instances.

Anyways, the bindings that call into that script module look like this:

```auto
timeMe('chameleon-pydata', 
runScript('transforms.dsToJsonData', 0, {view.custom.dataset}))

```

and:

```auto
timeMe('chameleon-pycolumns',
runScript('transforms.dsToJsonColumns', 0, {view.custom.dataset}, {this.custom.columnHandling}))

```

The pure expression bindings that perform the same transformation using my new iterators look like this:

```auto
timeMe('chameleon-data',
	forEach(
		{view.custom.dataset},
		asMap(it())
	)
)

```

and:

```auto
timeMe('chameleon-columns',
	forEach(
		columnsOf({view.custom.dataset}),
		asMap('field', it()[0],
			'header', asMap('title', try(unMap({this.custom.columnHandling}[it()[0]], 'title'), it()[0]))
		)
	)
)

```

The python columns transform has a slight advantage in that it is not converting the column type class instances to their string names (or abbreviations), which the `columnsOf()` expression function does unconditionally. So the speed advantage is a bit understated in these results from a little while ago:

```auto
[20:20:27]: Duration 279070ns timeme=chameleon-data, target=props.data
[20:20:27]: Duration 1439812ns timeme=chameleon-pydata, target=custom.pyData

[20:20:27]: Duration 372097ns timeme=chameleon-columns, target=props.columns
[20:20:27]: Duration 755441ns timeme=chameleon-pycolumns, target=custom.pyColumns

[20:20:27]: Duration 242858ns timeme=chameleon-data, target=props.data
[20:20:27]: Duration 1213344ns timeme=chameleon-pydata, target=custom.pyData

[20:20:27]: Duration 231521ns timeme=chameleon-columns, target=props.columns
[20:20:27]: Duration 773343ns timeme=chameleon-pycolumns, target=custom.pyColumns

[20:20:47]: Duration 298823ns timeme=chameleon-data, target=props.data
[20:20:47]: Duration 1316618ns timeme=chameleon-pydata, target=custom.pyData

[20:20:47]: Duration 295162ns timeme=chameleon-columns, target=props.columns
[20:20:47]: Duration 707748ns timeme=chameleon-pycolumns, target=custom.pyColumns

[20:20:47]: Duration 247223ns timeme=chameleon-data, target=props.data
[20:20:47]: Duration 1050020ns timeme=chameleon-pydata, target=custom.pyData

[20:20:47]: Duration 265910ns timeme=chameleon-columns, target=props.columns
[20:20:47]: Duration 619060ns timeme=chameleon-pycolumns, target=custom.pyColumns

```

The new Simulation Aids expressions are 2.5x to 5x faster than the python transforms, working with a 50Rx3C source dataset.

Latest for Ignition v8.1.x: ~~v2.0.2.231701910~~

I think there are a few more tweaks to come. 😁

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [June 23, 2023, 1:09pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/6 "2023-06-23T13:09:11Z")

</div>

This is awesome. Question - I currently have your old Simulation Aids installed on a production gateway and I mostly use it for the `view()` function - if I upgrade will I also see a 2.5x-5x speed increase for these expressions?

---

<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 23, 2023, 1:55pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/7 "2023-06-23T13:55:18Z")

</div>

No. The `view()` function is unchanged, except that it now honors the `debugMe()` and `traceMe()` tools.

And it can't really ever be changed, because its syntax is built around python expressions. I can't evaluate those except with python, so 🤷‍♂️

You will get the performance gains as you phase out script-based expressions and script transforms in favor of these pure expression techniques.

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [June 28, 2023, 11:09pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/8 "2023-06-28T23:09:47Z")

</div>

Hey Phil,

I'm a big fan of your `view` expression and and am currently trying to wrap my head around these new expressions.

Is it possible to do nested `forEach` statements?

I am using a [Tree Component](https://docs.inductiveautomation.com/display/DOC80/Perspective+-+Tree) and am trying to see if it's possible to build the components required `items` list from a source dataset using your new expressions

> **Source Dataset Example**
>
> | Col A | Col B | Col C |
> | --- | --- | --- |
> | Item 1 | Child1 | Grandchild 1 |
> | Item 1 | Child1 | Grandchild 2 |
> | Item 2 | Child of Item 2 | Different Grandchild |

> **Expected output**
>
> ```auto
> [
> {
> "label": "Item 1",
> "expanded": true,
> "data": [],
> "items": [
> {
> "label": "Child 1",
> "expanded": false,
> "data": [],
> "items": [
> {
> "label": "Grandchild 1",
> "expanded": false,
> "data": [],
> "items": []
> },
> {
> "label": "Grandchild 2",
> "expanded": false,
> "data": [],
> "items": []
> }
> ]
> }
> ]
> },
> {
> "label": "Item 2",
> "expanded": false,
> "data": [],
> "items": [
> {
> "label": "Child of Item 2",
> "expanded": false,
> "items": [
> {
> "label": "Different Grandchild",
> "expanded": false,
> "items": []
> }
> ]
> }
> ]
> }
> ]
> 
> ```

I've been playing around with it for a bit and I can easily get a single `forEach` expression to work, but I'm not sure how I'd pass `Col 1`'s value to the nested `selectStar` / `where` / `forEach`'s `it` expression

Is this currently possible with the built in expressions? I'm assuming the lack of a `groupBy` will throw a wrench into my plans.

Thank you for your time and thanks again for this free module. It honestly has saved me countless hours!

---

<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 29, 2023, 2:16am UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/9 "2023-06-29T02:16:18Z")

</div>

> [@Matthew.gaitan](#):
>
> Is it possible to do nested `forEach` statements?

Yes, but not recursively. If you share a bigger example of your source data, the `view()` expression you are currently using, and the output shape, I can help produce the necessary expression.

Do read the docs for the optional argument to the `it()` and `idx()` functions.

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [June 29, 2023, 4:30pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/10 "2023-06-29T16:30:43Z")

</div>

> [@pturmel](#):
>
> Do read the docs for the optional argument to the `it()` and `idx()` functions.

So I completely glossed over that part of the docs 🙄 Apologies for not doing a btter job to RTFM

> **Input Data**
>
> ```auto
> "#NAMES"
> "RouteName","StopName","WellName"
> "#TYPES"
> "str","str","str"
> "#ROWS","71"
> "Route 1","Stop 1","Well 1"
> "Route 1","Stop 1","Well 2"
> "Route 1","Stop 1","Well 3"
> "Route 1","Stop 1","Well 4"
> "Route 1","Stop 1","Well 5"
> "Route 1","Stop 1","Well 6"
> "Route 1","Stop 1","Well 7"
> "Route 1","Stop 1","Well 8"
> "Route 1","Stop 1","Well 9"
> "Route 1","Stop 2","Well 1"
> "Route 1","Stop 2","Well 2"
> "Route 1","Stop 2","Well 3"
> "Route 1","Stop 2","Well 4"
> "Route 1","Stop 2","Well 5"
> "Route 1","Stop 2","Well 6"
> "Route 1","Stop 2","Well 7"
> "Route 2","Stop 1","Well 1"
> "Route 2","Stop 1","Well 2"
> "Route 2","Stop 1","Well 3"
> "Route 2","Stop 1","Well 4"
> "Route 2","Stop 1","Well 5"
> "Route 2","Stop 1","Well 6"
> "Route 2","Stop 1","Well 7"
> "Route 2","Stop 2","Well 1"
> "Route 2","Stop 2","Well 2"
> "Route 2","Stop 2","Well 3"
> "Route 2","Stop 2","Well 4"
> "Route 2","Stop 2","Well 5"
> "Route 2","Stop 2","Well 6"
> "Route 2","Stop 2","Well 7"
> "Route 2","Stop 2","Well 8"
> "Route 2","Stop 3","Well 1"
> "Route 2","Stop 3","Well 2"
> "Route 2","Stop 3","Well 3"
> "Route 2","Stop 3","Well 4"
> "Route 2","Stop 3","Well 5"
> "Route 2","Stop 3","Well 6"
> "Route 2","Stop 3","Well 7"
> "Route 2","Stop 3","Well 8"
> "Route 2","Stop 3","Well 9"
> "Route 2","Stop 4","Well 1"
> "Route 2","Stop 4","Well 2"
> "Route 2","Stop 4","Well 3"
> "Route 2","Stop 4","Well 4"
> "Route 2","Stop 4","Well 5"
> "Route 3","Stop 1","Well 1"
> "Route 3","Stop 1","Well 2"
> "Route 3","Stop 1","Well 3"
> "Route 3","Stop 1","Well 4"
> "Route 3","Stop 1","Well 5"
> "Route 3","Stop 1","Well 6"
> "Route 3","Stop 1","Well 7"
> "Route 3","Stop 2","Well 1"
> "Route 3","Stop 2","Well 2"
> "Route 3","Stop 2","Well 3"
> "Route 3","Stop 2","Well 4"
> "Route 3","Stop 2","Well 5"
> "Route 3","Stop 2","Well 6"
> "Route 3","Stop 2","Well 7"
> "Route 3","Stop 2","Well 8"
> "Route 3","Stop 2","Well 9"
> "Route 3","Stop 2","Well 10"
> "Route 3","Stop 3","Well 1"
> "Route 3","Stop 3","Well 2"
> "Route 3","Stop 3","Well 3"
> "Route 3","Stop 3","Well 4"
> "Route 3","Stop 3","Well 5"
> "Route 3","Stop 3","Well 6"
> "Route 3","Stop 3","Well 7"
> "Route 3","Stop 3","Well 8"
> "Route 3","Stop 3","Well 9"
> 
> ```

> **Desired output**
>
> ```auto
> [
> {
> "expanded": false,
> "label": "Route 1",
> "items": [
> {
> "expanded": false,
> "label": "Stop 1",
> "items": [
> {
> "expanded": false,
> "label": "Well 1",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 2",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 3",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 4",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 5",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 6",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 7",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 8",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 9",
> "items": []
> }
> ]
> },
> {
> "expanded": false,
> "label": "Stop 2",
> "items": [
> {
> "expanded": false,
> "label": "Well 1",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 2",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 3",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 4",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 5",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 6",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 7",
> "items": []
> }
> ]
> }
> ]
> },
> {
> "expanded": false,
> "label": "Route 2",
> "items": [
> {
> "expanded": false,
> "label": "Stop 1",
> "items": [
> {
> "expanded": false,
> "label": "Well 1",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 2",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 3",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 4",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 5",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 6",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 7",
> "items": []
> }
> ]
> },
> {
> "expanded": false,
> "label": "Stop 2",
> "items": [
> {
> "expanded": false,
> "label": "Well 1",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 2",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 3",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 4",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 5",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 6",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 7",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 8",
> "items": []
> }
> ]
> },
> {
> "expanded": false,
> "label": "Stop 3",
> "items": [
> {
> "expanded": false,
> "label": "Well 1",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 2",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 3",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 4",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 5",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 6",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 7",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 8",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 9",
> "items": []
> }
> ]
> },
> {
> "expanded": false,
> "label": "Stop 4",
> "items": [
> {
> "expanded": false,
> "label": "Well 1",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 2",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 3",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 4",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 5",
> "items": []
> }
> ]
> }
> ]
> },
> {
> "expanded": false,
> "label": "Route 3",
> "items": [
> {
> "expanded": false,
> "label": "Stop 1",
> "items": [
> {
> "expanded": false,
> "label": "Well 1",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 2",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 3",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 4",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 5",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 6",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 7",
> "items": []
> }
> ]
> },
> {
> "expanded": false,
> "label": "Stop 2",
> "items": [
> {
> "expanded": false,
> "label": "Well 1",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 2",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 3",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 4",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 5",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 6",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 7",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 8",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 9",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 10",
> "items": []
> }
> ]
> },
> {
> "expanded": false,
> "label": "Stop 3",
> "items": [
> {
> "expanded": false,
> "label": "Well 1",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 2",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 3",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 4",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 5",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 6",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 7",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 8",
> "items": []
> },
> {
> "expanded": false,
> "label": "Well 9",
> "items": []
> }
> ]
> }
> ]
> }
> ]
> 
> ```
> 
> ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/4/d43239225466407cf9a7f24dd90f7d3fcca07da7.png)

> **Current expression**
>
> ```auto
> forEach(
> {this.custom.routes},
> asMap(
> 'label', it(0)['RouteName'],
> 'items', forEach(
> it(0),
> if(
> it(0)['RouteName'] = it(1)['RouteName'],
> asMap(
> 'label', it(1)['StopName'],
> 'items', forEach(
> it(1),
> if(
> it(1)['RouteName'] = it(2)['RouteName'] && it(1)['StopName'] = it(2)['StopName'],
> asMap(
> 'label', it(2)['WellName']
> ),
> null
> )
> )
> ),
> null
> )
> )
> )
> )
> 
> ```

> **Expression output**
>
> ```auto
> [
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 1"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 2"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 3"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 4"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 5"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 6"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 7"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 8"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 9"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 1"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 2"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 3"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 4"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 5"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 6"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 1",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 7"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 1"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 2"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 3"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 4"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 5"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 6"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 7"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 1"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 2"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 3"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 4"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 5"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 6"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 7"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 8"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 1"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 2"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 3"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 4"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 5"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 6"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 7"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 8"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 9"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 4",
> "items": [
> {
> "label": "Well 1"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 4",
> "items": [
> {
> "label": "Well 2"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 4",
> "items": [
> {
> "label": "Well 3"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 4",
> "items": [
> {
> "label": "Well 4"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 2",
> "items": [
> {
> "label": "Stop 4",
> "items": [
> {
> "label": "Well 5"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 1"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 2"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 3"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 4"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 5"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 6"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 1",
> "items": [
> {
> "label": "Well 7"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 1"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 2"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 3"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 4"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 5"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 6"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 7"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 8"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 9"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 2",
> "items": [
> {
> "label": "Well 10"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 1"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 2"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 3"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 4"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 5"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 6"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 7"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 8"
> }
> ]
> }
> ]
> },
> {
> "label": "Route 3",
> "items": [
> {
> "label": "Stop 3",
> "items": [
> {
> "label": "Well 9"
> }
> ]
> }
> ]
> }
> ]
> 
> ```

I'm trying to pass the results of one iterator into next as using it as the first argument of the `forEach` expression. I'm not sure if that's kosher or not. As you can see from the output it's creating an equal number of items to the source input instead of grouping the objects together by level

---

<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 29, 2023, 5:54pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/11 "2023-06-29T17:54:10Z")

</div>

Hmm. You need `groupBy()`, which I haven't written yet. 🙁

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [June 29, 2023, 9:32pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/12 "2023-06-29T21:32:17Z")

</div>

LOL well i guess I chose the wrong test candidate then 😅

I will try on something that doesn't require grouping.

I will say, so far I like the way everything chains together. If you stare at it long enough it makes sense what it's trying to do

---

<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:** [July 13, 2023, 6:08pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/13 "2023-07-13T18:08:17Z")

</div>

> [@pturmel](#):
>
> which I haven't written yet.

No longer true. 😁

For Ignition v8.1+: ~~v2.0.3.231942018~~

With @Matthew.gaitan 's input dataset in a custom property named `source`, this expression:

```auto
forEach(
	asMap(
		groupBy(
			{this.custom.source},
			it()['RouteName'],
			it()['StopName']
		)
	),
	asMap(
		'label', it()[0],
		'expanded', false,
		'data', asMap('nodeType', 'Route'),
		'items', forEach(
			it()[1],
			asMap(
				'label', it()[0],
				'expanded', false,
				'data', asMap('nodeType', 'Stop'),
				'items', forEach(
					it()[1],
					asMap(
						'label', it()['WellName'],
						'expanded', false,
						'data', asMap('nodeType', 'Well'),
						'items', asList()
					)
				)
			)
		)
	)
)

```

Yields this output:

```auto
[
  {
    "label": "Route 1",
    "expanded": true,
    "data": {
      "nodeType": "Route"
    },
    "items": [
      {
        "label": "Stop 1",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 9",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 2",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      }
    ]
  },
  {
    "label": "Route 2",
    "expanded": true,
    "data": {
      "nodeType": "Route"
    },
    "items": [
      {
        "label": "Stop 1",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 2",
        "expanded": true,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 3",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 9",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 4",
        "expanded": true,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      }
    ]
  },
  {
    "label": "Route 3",
    "expanded": true,
    "data": {
      "nodeType": "Route"
    },
    "items": [
      {
        "label": "Stop 1",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 2",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 9",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 10",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      },
      {
        "label": "Stop 3",
        "expanded": false,
        "data": {
          "nodeType": "Stop"
        },
        "items": [
          {
            "label": "Well 1",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 2",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 3",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 4",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 5",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 6",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 7",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 8",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          },
          {
            "label": "Well 9",
            "expanded": false,
            "data": {
              "nodeType": "Well"
            },
            "items": []
          }
        ]
      }
    ]
  }
]

```

The magic is here:

[https://www.automation-pros.com/simaids/doc/expression.groupby.html](https://www.automation-pros.com/simaids/doc/expression.groupby.html)

The `asMap()` function was altered to handle multi-item keys in a sequence of key-value pairs as requests to make nested mappings. Note that the `asMap` function will work with key values that are not valid names within Perspective objects, so you may need to construct such maps nested in the expression that consumes them (as in this case).

Both `asMap()` and `asList()` were updated to properly return an empty object when given no arguments.

---

<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:** [July 13, 2023, 6:10pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/14 "2023-07-13T18:10:31Z")

</div>

Note that expressions don't have any mechanism to express recursion, so this approach can only produce fixed-depth trees.

---

<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:** [July 13, 2023, 6:15pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/15 "2023-07-13T18:15:42Z")

</div>

I don't know whether to be impressed or terrified 🙂

Very cool stuff.

---

<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:** [July 13, 2023, 6:21pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/16 "2023-07-13T18:21:42Z")

</div>

I'm pretty sure I can use the mappings of groups to implement dataset joins. 😅

---

<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:** [July 13, 2023, 6:26pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/17 "2023-07-13T18:26:49Z")

</div>

I tacked on a `timeMe()`, which yielded 2.45-2.51 milliseconds for Matthew's source data. Jython doesn't even _get started_ that fast.

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [July 13, 2023, 7:49pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/18 "2023-07-13T19:49:04Z")

</div>

Phil, this is freaking awesome! Thank you! Going to have to upgrade asap 😃

---

<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:** [July 13, 2023, 8:32pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/19 "2023-07-13T20:32:23Z")

</div>

Of course, the general purpose of a `GROUP BY` clause is to apply aggregate functions to the groups. A simple example is to make a simple view with two tables side by side. Name the first one `Source` and leave its sample data in place. In the second one, place this expression binding on `props.data`:

```auto
orderBy(
	forEach(
		groupBy({../Source.props.data}, it()['country']),
		asMap(
			'country', it()[0],
			'cities', len(it()[1]),
			'population', sum(
				forEach(
					it()[1],
					it()['population']
				)
			)
		)
	),
	descending(it()['population'])
)

```

You get this output:

```auto
[
  {
    "country": "China",
    "cities": 1,
    "population": 24153000
  },
  {
    "country": "United States",
    "cities": 8,
    "population": 19547689
  },
  {
    "country": "Japan",
    "cities": 2,
    "population": 16308742
  },
  {
    "country": "Nigeria",
    "cities": 1,
    "population": 16060303
  },
  {
    "country": "Pakistan",
    "cities": 1,
    "population": 14910352
  },
  {
    "country": "Bangladesh",
    "cities": 1,
    "population": 14399000
  },
  {
    "country": "Turkey",
    "cities": 1,
    "population": 14025000
  },
  {
    "country": "Iran",
    "cities": 3,
    "population": 13024236
  },
  {
    "country": "Indonesia",
    "cities": 2,
    "population": 12763073
  },
  {
    "country": "India",
    "cities": 1,
    "population": 11034555
  },
  {
    "country": "Mexico",
    "cities": 2,
    "population": 10469913
  },
  {
    "country": "Egypt",
    "cities": 1,
    "population": 10230350
  },
  {
    "country": "Saudi Arabia",
    "cities": 2,
    "population": 9132880
  },
  {
    "country": "United Kingdom",
    "cities": 1,
    "population": 8825001
  },
  {
    "country": "Colombia",
    "cities": 1,
    "population": 7878783
  },
  {
    "country": "Brazil",
    "cities": 1,
    "population": 6429923
  },
  {
    "country": "Singapore",
    "cities": 1,
    "population": 5535000
  },
  {
    "country": "Russia",
    "cities": 1,
    "population": 5191690
  },
  {
    "country": "Ivory Coast",
    "cities": 1,
    "population": 4765000
  },
  {
    "country": "Tanzania",
    "cities": 1,
    "population": 4364541
  },
  {
    "country": "Italy",
    "cities": 2,
    "population": 4237120
  },
  {
    "country": "Germany",
    "cities": 1,
    "population": 3517424
  },
  {
    "country": "Afghanistan",
    "cities": 1,
    "population": 3414100
  },
  {
    "country": "Spain",
    "cities": 1,
    "population": 3233527
  },
  {
    "country": "Ukraine",
    "cities": 1,
    "population": 2908703
  },
  {
    "country": "Nicaragua",
    "cities": 1,
    "population": 2560789
  },
  {
    "country": "France",
    "cities": 1,
    "population": 2229621
  },
  {
    "country": "Philippines",
    "cities": 1,
    "population": 1780148
  },
  {
    "country": "Canada",
    "cities": 1,
    "population": 1649519
  },
  {
    "country": "Czech Republic",
    "cities": 1,
    "population": 1241664
  },
  {
    "country": "Armenia",
    "cities": 1,
    "population": 1060138
  },
  {
    "country": "Tunisia",
    "cities": 1,
    "population": 1056247
  },
  {
    "country": "New Zealand",
    "cities": 2,
    "population": 810000
  },
  {
    "country": "Finland",
    "cities": 1,
    "population": 635591
  },
  {
    "country": "Australia",
    "cities": 1,
    "population": 208374
  }
]

```

Since the data is in JSON format, not dataset format, there must be a `forEach()` inside the aggregate function to pull the desired data out of the map. If the source data to `groupBy()` is a dataset, then the value of `it()[1]` when iterating through the groups will also be a dataset, and you would use `sum(it()[1], 'population')` instead of generating the sequence for `sum()`. Like so:

```auto
orderBy(
	forEach(
		groupBy({../Source.props.data}, it()['country']),
		asMap(
			'country', it()[0],
			'cities', len(it()[1]),
			'population', sum(it()[1], 'population')
		)
	),
	descending(it()['population'])
)

```

Also, found a bug in `orderBy()`. Link fixed above.

I should emphasize that when you look at these iterators, remember that the first argument, the source, is evaluated completely before that function starts its own loop. So if you see `it()` or `idx()` in that position, it is referencing an outer iteration loop.

{ `timeMe()` says that first expression runs in 1.7-2.2 milliseconds. }

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 14, 2023, 6:40pm UTC](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/20 "2023-07-14T18:40:30Z")

</div>

Phil, I'm having difficulty developing a `view()` expression to pivot a dataset. There are also some other things I would like to accomplish at the same time, but currently I'm stuck on getting the correct Pivoted output.

Original dataset is simply a wide result for `system.tag.queryTagHistory()`. Preferably the Column Headings would be the Hours from the t\_stamp column.

I have read the documentation, but I just can't seem to put the correct expression together.

This then leads to other questions:

1. Is it possible to combine two of the columns into one the value would be the sum of the two columns per row.  
2.Is it possible to supply an order by expression to order a column outside of just the normal ascending or descending. For instance our fiscal day runs from 7am - 7am, so ideally the columns after pivoting would be in that order, where the hours that have not yet occurred on the current day are filled by the previous day's numbers?

TYIA for any assistance.

[Next page](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934.md?page=2)