Automation Professionals' Integration Toolkit Module

Phil,
Apologies for my ignorance--
I'm not seeing how I implement this module, as in it's a .modl file: where does it go and how does Ignition know it's there for me to use it?

You install it like any other module:
https://docs.inductiveautomation.com/display/DOC81/Installing+or+Upgrading+a+Module

2 Likes

tried the sample view provided, but table.props.data uses table.custom.filteredDS, and table.custom.filteredDS uses table.props.data. I get the error in the screenshot below.

I tried to replace table.props.data for table.custom.rawData in filteredDS, but no luck

Do you have an up to date sample?

Use this. I left a now-broken binding on filteredDS.

Summary
{
  "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",
                "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"
  }
}

There are many other examples in this topic. Some have been broken by module updates, as I fine-tuned how things should work. (unionAll(), selectStar(), and forEach() have had breaking changes along the way.)

This work for me

Maybe I am pushing my luck, but to try this module, I wanted to have fun, and replicate PLC behavior with an expression in our dev environment

I have raw material length, an order defined as a UDT, and I want to calculate the expected scrap length (done in the PLC in production).
image

I usually can read the UDT, and get a JSON document. Loop through properties Part1 to Part60, and get the sum of Quantity x Length.
image

Would you expect this to work?

No, JSON documents aren't supported. But you could use my tags() function to get the values as lists. Something like this:

sum(
	forEach(
		tags(
			forEach(
				60,
				stringFormat("[.]Order/Part%d/Length", idx()+1)
			)
		),
		it()[1]
	)
)

To calculate Length * Quantity, it gets substantially more complicated, needing flatten() to generate the tag path list with a nested loop, and groupBy() to then loop by Part#.

Thanks, ended up with the following

sum(foreach(60, {[.]Order}['Part' + idx()]['Length']))
1 Like

Hmm. I guess JSON documents are supported (by Ignition's square brackets) after all. That's pretty handy.

2 Likes

Automation Professionals is pleased to announce a new release of the Integration Toolkit module. This release adds one new scripting function, system.db.runPrepInsert().

It is very similar to IA's system.db.runPrepUpdate(), intepreting the query, args, database, and tx arguments in the same fashion. The getKey argument is omitted as moot, as this function always retrieves generated keys. The skipAudit optional argument is unimplemented.

For Ignition v8.1+: v2.0.15.240431913

Do note the documenation's notes on the stellar performance of PostgreSQL's JDBC driver, and the equally lame performance of MS SQL Server's JDBC driver. (Yes, latest v12.6.)

{ Ahem. Tips on implementing skipAudit would be welcome... }

4 Likes

In the RPC message, it's just adding a boolean true for the skipAudit arg on the outgoing Message, which is parsed in the RPC handling (com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery). On the gateway, skipAudit is a no-op (the call is simply never audited), as far as I can tell.

Hah! That explains my failure to figure out how it worked. Spelunking with reflection can only go so far.