Grouped Record ID Tool – Macro-Based with JavaScript UI

In the last post in this series, we created a standard Alteryx Macro which creates a grouped record id field. The goal of this post is to replace the interface with a JavaScript SDK based UI. We will cover creating the tool structure, creating the UI and packaging it all up as a YXI.

Preparing the Macro

Before we start building the JS UI, let’s take a look at the XML configuration of the macro as it stands:

<Configuration>
  <Value name="Text Box (14)">RowID</Value>
  <Value name="Check Box (12)">True</Value>
  <Value name="Drop Down (17)">Int16</Value>
  <Value name="Numeric Up Down (20)">5</Value>
  <Value name="Numeric Up Down (26)">1</Value>
  <Value name="List Box (27)">""</Value>
  <Value name="List Box (33)">""</Value>
  <Value name="List Box (34)">""</Value>
</Configuration>

While this was fine when I was building the macro (as I just drag lines), my programmer hat hates these names, so we need to do some tweaks to the Macro. If you go to the annotation tab within the configuration panel for the interface tools you will find a name entry:

Annotation Tab

The easiest way to fix the issue is to edit them in here. However, I’m lazy … so here’s how I did it.

  • Open the folder containing the macro in Visual Studio Code
  • Open the macro in the text editor
  • Find the questions section (The VS Code XML TreeView is excellent):
          <Questions>
            <Question>
              <Type>TextBox</Type>
              <Description>Field Name</Description>
              <Name>Text Box (14)</Name>
              <ToolId value="14" />
              <Default>RecordID</Default>
              <Password value="False" />
              <Multiline value="False" />
              <Hidden value="False" />
            </Question>
  • Next build up a table of what we want to change:
Old Name New Name
Text Box (14) FieldName
Check Box (12) LastColumn
Drop Down (17) FieldType
Numeric Up Down (20) StringSize
Numeric Up Down (26) StartingValue
List Box (27) GroupingFields
List Box (33) SortingFields
List Box (34) DescendingFields
  • Now we can use the global search and replace and let VS Code do all the work:

Search and Replace

  • Finally, repackage and retest!

Tool Annotation

The only disadvantage to this approach is that while the interface tool is correctly configured and will work fine, the annotation tab in the configuration panel will show the old Name (e.g. Text Box (14)) rather than the new one (e.g. FieldName):

Old Tool Name

To fix this, you need to correct the Name element in the Annotation element for each tool. For example change:

    <Node ToolID="14">
      <GuiSettings Plugin="AlteryxGuiToolkit.Questions.TextBox.QuestionTextBox">
        <Position x="750" y="42" width="59" height="59" />
      </GuiSettings>
      <Properties>
        <Configuration />
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
    </Node>

to

    <Node ToolID="14">
      <GuiSettings Plugin="AlteryxGuiToolkit.Questions.TextBox.QuestionTextBox">
        <Position x="750" y="42" width="59" height="59" />
      </GuiSettings>
      <Properties>
        <Configuration />
        <Annotation DisplayMode="0">
          <Name>FieldName</Name>
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
    </Node>

The easiest way to correct this is to just edit with the designer.

Creating the JS Tool

Before we can go much further, we need to create a JS tool structure. This consists of:

  • Config file: Metadata for the tool (describes input, output, category)
  • Tool image file: Image for ribbon
  • Macro files
  • GUI HTML file
  • GUI JavaScript file
  • Installer Script

Fortunately for the first three items, this is pretty similar to the same problem as packaging a macro in a yxi file. A few small adjustments to the createMacroYXI.ps1 to get started:

  • Don’t compress the output
  • Create a tool folder called JS (chosen so not to clash with macro)
  • Place the macro in a subfolder called Supporting_Macros within the tool folder
  • Write the base64 image to logo.png within the tool folder
  • Create an XML configuration file called JS within the tool folder

Some changes to the structure of the configuration are needed from the YXI script.

  • Need to add an EngineSettings entry. Note the entry point is from one directory up from the configuration file. Something like:
    <EngineSettings EngineDLL="Macro" EngineDLLEntryPoint="GroupedRecordIDJS/Supporting_Macros/GroupedRecordID.yxmc" SDKVersion="10.1"/>
  • Need to add a GuiSettings entry. This tells Alteryx where the logo and the entry point for the tool is. The output looks like:
    <GuiSettings Html="GroupedRecordIDGUI.html" Icon="logo.png" SDKVersion="10.1">
    </GuiSettings>
  • The properties section doesn’t need changing

The final alteration is to add the input and outputs to the GuiSettings entry. There are three types to deal with: Macro Input, Macro Output and Control Parameter Inputs (for Batch Macros). For Macro Inputs, the details we need can be found in the Macro Input tools configuration which looks like:

    <Node ToolID="3">
      <GuiSettings Plugin="AlteryxBasePluginsGui.MacroInput.MacroInput">
        <Position x="78" y="282" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <UseFileInput value="False" />
          <Name>Input</Name>
          <Abbrev>W</Abbrev>
          <ShowFieldMap value="False" />
          <Optional value="False" />

This needs to be translated into:

    <InputConnections>
      <Connection Name="Input" AllowMultiple="False" Optional="False" Type="Connection" Label="W"/>
    </InputConnections>

A small block of PowerShell takes care of this in the create script:

    $macroInputs =$xmlDoc.AlteryxDocument.Nodes.SelectNodes("Node[GuiSettings/@Plugin='AlteryxBasePluginsGui.MacroInput.MacroInput']")
    if ($macroInputs.Count -ne 0) {
        Add-Content $config -Value '        <InputConnections>'
        foreach ($macroInput in $macroInputs) {
            $iname = $macroInput.Properties.Configuration.Name
            $optional = $macroInput.Properties.Configuration.Optional.value
            $abbrev = $macroInput.Properties.Configuration.Abbrev
            if ($abbrev -ne "") {""
                $abbrev = " Label=""$abbrev"""
            }
            Add-Content $config -Value "            <Connections Name=""$iname"" AllowMultiple=""False"" Optional=""$optional"" Type=""Connection""$abbrev/>"
        }
        Add-Content $config -Value '        </InputConnections>'
    }

A similar block converts the outputs as well. There is a little extra handling needed for a batch macro inside the script as well.

The new config file looks like:

<?xml version="1.0"?>
<AlteryxJavaScriptPlugin>
    <EngineSettings EngineDLL="Macro" EngineDLLEntryPoint="GroupedRecordIDJS/Supporting_Macros/GroupedRecordID.yxmc" SDKVersion="10.1"/>
    <GuiSettings Html="GroupedRecordIDGUI.html" Icon="logo.png" SDKVersion="10.1">
        <InputConnections>
            <Connection Name="Input" AllowMultiple="False" Optional="False" Type="Connection"/>
        </InputConnections>
        <OutputConnections>
            <Connection Name="Output7" AllowMultiple="False" Optional="" Type="Connection"/>
        </OutputConnections>
    </GuiSettings>
    <Properties>
        <MetaInfo>
            <Name>GroupedRecordIDJS</Name>
            <Author>James Dunkerley</Author>
            <Description>Macro creating a RecordID with Grouping and Sorting.</Description>
            <CategoryName>Preparation</CategoryName>
            <ToolVersion>1.0</ToolVersion>
           <Icon>logo.png</Icon>
        </MetaInfo>
    </Properties>
</AlteryxJavaScriptPlugin>

Placeholder HTML and JS Files

To create an HTML UI for the tool, first we need an entry HTML file called GUI.html. The following content will set up an empty page linked to a JavaScript file called GUI.js:

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>[ToolName]</title>
    <script type="text/javascript">
        document.write(`<link rel="import" href="${window.Alteryx.LibDir}2/lib/includes.html">`)
    </script>
</head>
<body>
	<form>
	</form>
    <script type="text/javascript" src="[ToolName]GUI.js"></script>
</body>
</html>

For the JavaScript file, the following is a good starting point (it does nothing but puts placeholders in ready to be filled in and used):

/**
 * Specify actions that will take place before the tool's configuration is loaded into the manager.
 * @param manager The data manager.
 * @param AlteryxDataItems The data items in use on this page.
 * @param json Configuration
 */
Alteryx.Gui.BeforeLoad = (manager, AlteryxDataItems, json) => {
}

/**
 * Specify actions that will take place before the tool's configuration is loaded into the manager.
 * @param manager The data manager.
 * @param AlteryxDataItems The data items in use on this page.
 */
Alteryx.Gui.AfterLoad = (manager, AlteryxDataItems) => {
}

/**
 * Reformat the JSON to the style we need
 * @param json Configuration
 */
Alteryx.Gui.BeforeGetConfiguration: (json) =>  {
  return json
}

/**
 * Set the tool's default annotation on the canvas.
 * @param manager The data manager.
 * @returns {string}
 */
Alteryx.Gui.Annotation = (manager) => ''

Installer (and Uninstaller)

The final piece of the setup puzzle is to set up an install script (to make it easy to develop). The idea of this installer is to create a Junction point within the Alteryx HTML Plugins folder to the JS file. The create JS tool script will grab the newest version of the Powershell scripts within the Alteryx Omnibus. There is both and Installer and Uninstaller. Also, it will create a couple of batch files which call this with the correct argument for the tool.

Final Folder Structure

At this point, we now have a complete (albeit with a blank UI) HTML/JS Custom tool. The folder structure for the Grouped Record ID tool looks like:

├── GroupedRecordIDJS
│ ├── Supporting_Macros
│ │ └── GroupedRecordID.yxmc
│ ├── GroupedRecordIDJSGUI.html
│ ├── GroupedRecordIDJSGUI.js
│ ├── GroupedRecordIDJSConfig.js
| └── logo.png
├── Install.bat
├── Installer.ps1
├── Uninstall.bat
└── Uninstaller.psi

You can download the script to create this structure from GitHub. Right-click on the Raw link and select Save link as … to download the script. It can then be run with a single argument of the path of the macro to convert.

Creating The HTML UI

To add the UI controls we need to go and edit the HTML file GroupedRecordIDJSGUI.html. For this pass, the goal is just to recreate the Macro interface within HTML. The current UI looks like:

Macro UI

This has a reasonable number of the interface tools in here to reproduce. Alteryx, provide a pretty straightforward SDK to build your UI from.

Field Name: Text Box and Simple String

Starting from the top, there is a TextBox control with a default value of RecordID. This is stored in a Field Name value. The original Text Box configuration looks like:

Text Box Configuration

First add the plugin widget to the HTML:

<label for="dataFieldName">Field Name</label>
<ayx data-ui-props='{type:"TextBox", widgetId:"dataFieldName"}'></ayx>

The ayx tag defines the PlugIn Widget. It must have a data-ui-props which gives the constructor arguments for building the widget. The type argument tells it what to build and the other arguments are given after this. All the Plugin widgets are based on React components.

To set up the default value and bind it to the text box, add the following to the BeforeLoad section of the JavaScript file.

  const dataFieldNameItem = new AlteryxDataItems.SimpleString('FieldName', {})
  dataFieldNameItem.setValue('RecordID')
  manager.addDataItem(dataFieldNameItem)
  manager.bindDataItemToWidget(dataFieldNameItem, 'dataFieldName')

HTML Text Box

For the sake of being a bit of a reference guide here are some of the other options for the TextBox control.

Mulit-Line Text Box

For a multi-line text box you use:

<ayx data-ui-props='{type:"TextArea", widgetId:"TextArea1", rows:5}'></ayx>
<ayx data-ui-props='{type:"TextArea", widgetId:"TextArea2", rows:2, resizable:false, minRows:1, maxRows:10}'></ayx>

You have a little more control over a JavaScript Text Area than on an Interface Text Box. You can add the following options:

  • rows: integer, number of rows to display
  • resizable: boolean, allow the text area to be resized
  • minRows: integer, minimum number of rows for text area (undocumented)
  • maxRows: integer, maximum number of rows for text area (undocumented)

Passwords

A password text box is a little different. It needs to be set up on the DataItem rather than the plugin widget. The HTML is the same as the TextBox. The JavaScript looks like:

  const dataPasswordItem = new AlteryxDataItems.SimpleString('Password', {password: true})
  manager.addDataItem(dataPasswordItem)
  manager.bindDataItemToWidget(dataPasswordItem, 'dataPassword')

If you are building a UI without the plugin widgets, then a little special handling on the binding to account for the asynchronous nature of decoding the value is needed. You can subscribe to changes of value via the registerPropertyListener method on the DataItem:

  userPass.registerPropertyListener('value', (propertyChangeEvent)  => {
    document.getElementById('userPass').value = propertyChangeEvent.value
  })

Check Box and Simple Bool

The next control is a CheckBox. Version 1 of the JavaScript API had toggle switches, but at present, I don’t believe these are available in Version 2. The HTML component looks like:

<ayx data-ui-props='{type:"CheckBox", widgetId:"dataLastColumn", label:"Add Field As Last Column"}'></ayx>

Again, the binding to the field needs to be done within JavaScript.

  const dataLastColumnItem = new AlteryxDataItems.SimpleBool('LastColumn', {})
  manager.addDataItem(dataLastColumnItem)
  manager.bindDataItemToWidget(dataLastColumnItem, 'dataLastColumn')

HTML Check Box

The default value is unchecked. If you want to have a default of checked then add:

  dataLastColumnItem.setValue(true)

Field Type Drop Down

The Drop Down box needs a different backing data item, a StringSelector but otherwise, this is similar to the other tools.

<label for="dataFieldType">Field Type</label>
<ayx data-ui-props='{type:"DropDown", widgetId:"dataFieldType"}'></ayx>

The DataItem has the options that can be displayed as well as the default value as in the previous cases.

  const dataFieldTypeItem = new AlteryxDataItems.StringSelector('FieldType', {
    optionList: [
      'Byte',
      'Int16',
      'Int32',
      'Int64',
      'String',
      'WString',
      'V_String',
      'V_WString'
    ].map(a => { return {label: a, value: a} })
  })
  dataFieldTypeItem.setValue('Int64')
  manager.addDataItem(dataFieldTypeItem)
  manager.bindDataItemToWidget(dataFieldTypeItem, 'dataFieldType')

HTML Drop Down Box

The options list is an array of objects with properties value and label. In the case above, it maps from an array of string to an array of objects. The default value needs to be equal to the value, not the label if they are not the same.

Other modes for the Drop Down widgets

In macro UI, the Drop Down tool can be used in a variety of ways:

  • Field types
  • Fixed list (Manually set values)
  • External source
  • Fields from a connected tool
  • Datasets (Allocate, Geocoder, etc.)
  • File browse

The easiest way to do Field types is to expand the list above to the complete set:

  const dataFieldTypeItem = new AlteryxDataItems.StringSelector('FieldType', {
    optionList: ['Blob', 'Bool', 'Byte', 'Int16', 'Int32', 'Int64', 'FixedDecimal', 'Float', 'Double', 'String', 'WString', 'V_String', 'V_WString', 'Date', 'Time', 'DateTime', 'SpatialObj']
      .map(a => { return {label: a, value: a} })
  })

The fixed list can be done either as with the field list (if you want the label and value the same) or something like:

  const dataFieldTypeItem = new AlteryxDataItems.StringSelector('FieldType', {
    optionList: [
      {label: 'alpha', value: 1},
      {label: 'beta', value: 2},
      {label: 'gamma', value: 3}
    ]
  })

Importing from an external source will be a little more fiddly. The JavaScript SDK is built on top of CEF but you do not have Node. Something like the following should work:

  const dataFieldTypeItem = new AlteryxDataItems.StringSelector('FieldType', {disabled: true})
  const xhr = new XMLHttpRequest()
  xhr.addEventListener('load', e => {
    if (xhr.readyState === 4) {
      dataFieldTypeItem.setOptionListFromStrings(xhr.responseText.replace('\r', '').split('\n'))
      dataFieldTypeItem.setDisabled(false)
    }
  })
  xhr.open('GET', 'items.txt')
  xhr.send()
  manager.addDataItem(dataFieldTypeItem)
  manager.bindDataItemToWidget(dataFieldTypeItem, 'dataFieldType')

Fields from a connected tool are done using a different Data Item – a FielsSelector. The code below takes the fields from the first connection and is filtered to Numeric fields.

  const dataInputFieldItem = new AlteryxDataItems.FieldSelector('InputField', {manager: manager, connectionIndex: 0, anchorIndex: 0, fieldType: 'Numeric'})
  manager.addDataItem(dataInputFieldItem)
  manager.bindDataItemToWidget(dataInputFieldItem, 'dataInputField')

Note you must include the manager in the constructor parameter or the data item will fail to be created:

Field Selector Parameters

The options for the FieldType are:

  • All (default)
  • NoBinary
  • NoBlob
  • NoSpatial
  • String
  • Date
  • DateOrTime
  • StringOrDate
  • NumericOrString
  • Numeric
  • SpatialObj
  • Bool
  • Time
  • Blob

As for the DataSets and File Browse, I am not sure the best way to reproduce this functionality.

Searchable Drop Drown

New in version 11.7, the Drop Down can be a searchable drop down box (or a combo box if you prefer the WinForms name). Add a property to the data-ui-props of searchable: true and you get:

Combo Box

A few additional options become available in searchable mode:

  • caseSensitiveSearch: boolean
  • searchByLabelOrValue: string enumeration of label, value or both
  • placeholder: string text to be displayed before value chosen
  • clearable: boolean adds a button to clean the text

Numeric Spinners

The next two controls in the original macro’s UI are both Numeric Up-Down controls:

Numeric Up Down controla

The String Size is an integer value with a minimum value of 1 and maximum of 1073741823 (the default length in a formula tool). In the HTML SDK this looks like:

<label for="dataStringSize">String Size</label>
<ayx data-ui-props='{type:"NumericSpinner", widgetId:"dataStringSize"}'></ayx>

with the data item specifying the minimum and maximum value:

  const dataStringSize = new AlteryxDataItems.ConstrainedInt('StringSize', {min: 1, max: 1073741823})
  dataStringSize.setValue(5)
  manager.addDataItem(dataStringSize)
  manager.bindDataItemToWidget(dataStringSize, 'dataStringSize')

The results in:

HTML Numeric Spinner

The ConstrainedInt also allows for setting the step size. There is also an allowedPrecision setting which specified how many digits after the decimal point. As far as I can tell there is no difference between the ConstrainedInt and ConstrainedFloat classes in the DataItems.

List Boxes

The last section of the original interface was built using List Box tools set to display a list of fields:

Macro List Boxes

In all cases, these are configured to create ‘Custom Lists’. In 11.7 Alteryx added List Boxes to the HTML SDK. The code for these look like:

<label for="dataGroupingFields">Grouping Fields</label>
<ayx data-ui-props='{type:"ListBox", widgetId:"dataGroupingFields", searchable: false}'></ayx>

with the data item defined as:

  const dataGroupingFields = new AlteryxDataItems.FieldSelectorMulti('GroupingFields', {manager: manager, connectionIndex: 0, anchorIndex: 0, delimiter: '","'})
  manager.addDataItem(dataGroupingFields)
  manager.bindDataItemToWidget(dataGroupingFields, 'dataGroupingFields')

In this mode, the list box will create a custom list with the default separator of a comma. The macro uses a "," separator with a leading and trailing ". While the delimiter is a constructor argument, there is no built-in way add text at the start or the end directly. One To handle this is to adjust the JSON serialization. The function below adjusts the data item so that it adds and removes " when converting to and from JSON:

function setJsonSerialiser (item) {
  const innerFn = item.fromJson
  item.fromJson = (e, t, n) => (typeof n === 'string' && innerFn(e, t, n.replace(/(^")|("$)/g, '')))
  item.toJson = (e, t) => e({ DataItem: `"${item.getValue().join(item.getDelimiter())}"`, DataName: item.getDataName() })
}

The resulting UI looks like:

Macro List Boxes

The list box has a few modes it can be used in. In terms of the item list, it is very similar to the drop down box for setting up except you need to use a FieldSelectorMulti or a StringSelectorMulti data item to populate the list. The ListBox itself has a small amount of customisation available to hide the search bar or info row.

Final HTML and Javascript

With a little more work the final UI looks like:

Macro List Boxes

Final scripts look like:

Testing and Packaging as a YXI File

The last steps are to create the test macros and make sure all is working. A simple adjustment to the previous test workflows from the simple macro.

To package it up as YXI file, I used the CreateYXI script which is part of Omnibus tools. The script below packages the tool as a YXI:

pushd %~dp0
PowerShell -C "Remove-Item ./GroupedRecordIDJS.yxi -Force"
PowerShell -C "../Scripts/CreateYXI.ps1 -folder ./GroupedRecordIDJS -version 1.0.0 -imagePath ./GroupedRecordIDJS/logo.png"

Installer Screen

Summary

While still not a perfect UI, the macro now has an HTML UI which can build upon. It still does do everything I want. It works on the large scale and as with the macro is simple to distribute as a YXI file, but now looks a little bit prettier.

Resources

To download files from GitHub right click on the Raw link and choose Save Link As ...

What’s Next

So now we have a simple macro with a basic HTML US, in the next post, I will add more custom JavaScript and look further into DataItems and PlugIn Widgets.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s