Monday, 10 February 2020

Building a Better Record ID tool ...

Whilst visiting the Shambles in York, I was treated to a set of ACE inspired ghosts from my friend and fellow ACE, Rafal Olbert.  We searched the community of ghosts at the Ghost Merchant for the perfect trio of goblins to complete the set.  If you notice the coloring, they closely resemble the ACE logo on Raf's polo.  Who except us would spend the time rummaging through hundreds of ghosts to complete this task?
Now comes the question that you might be asking, "What does this have to do with CReW macros?"  It has everything to do with them.  Often there will be a simple task that has to be configured by many in the community.  The time spent in configuring a task could be better spent elsewhere (e.g. visiting York with friends).

There are multiple posts in the Alteryx Community asking how to get unique record IDs on groups of records.  There are also multiple ideas in the community to modify the functionality of the RecordID tool.  James Dunkerley presented at Inspire 2020 on the magic of modifying XML.  One example that he showed was the adding of the grouping functionality to the Record ID.

I grabbed a copy of his macro and have had it waiting for an opportunity to release it to the CReW user-base.  If you think about the construction of a macro that allows for GROUPING, you'll realize that the dynamic nature of grouping requires you to build a solution that allows for multiple (any) selections of fields from the incoming data stream.  James solved the grouping by modifying the XML in a formula:

IIF(IsEmpty([#1]) OR [#1]='""', "<GroupByFields />", 
'<GroupByFields><Field field=' 
+ Replace([#1], '","', '"/><Field field="')
+ '/></GroupByFields>')+ "/*" + [#1] + "*/"

He's modifying the XML of a standard Multi-Row Formula to update the GROUP BY parameter.  When no fields are selected, the resulting xml (grouping) looks like this:

<GroupByFields />

If the incoming data has three (3) fields selected for grouping, the results need to look like this:

   <Field field="Field1"  />
   <Field field="Field2"  />
   <Field field="Field3"  />

The macro is configured to read the incoming metadata and will populate a LIST BOX interface tool with all of the incoming fields.  When the user selects the fields (e.g. Field1, Field2, Field3), the question results are passed to an ACTION tool as a comma delimited series of values.  James then creates a formula to modify the output to produce the XML component displayed above.

If James had attended the BUILD 2 event in Nashville, this was one of the challenges posed.  He would have won that event.

If you'd like to download a copy of the macro, it is in the Alteryx Gallery and can be found here . Any feedback is welcomed.




  1. Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking. Gebäudereinigung Hardegsen

  2. I wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post. Asheville Property Management

  3. It is dependent upon you to ensure that in case you are utilizing a developer to take care of your job that they comprehend from the start whether they are assuming liability for guaranteeing that building work follows the Building Regulations. Tree Trimming Pearland

  4. Thanks for your post. I’ve been thinking about writing a very comparable post over the last couple of weeks, I’ll probably keep it short and sweet and link to this instead if thats cool. Thanks. Charles

  5. I cannot thank you enough for the blog. Really thank you! Great.
    Top rated tree care Lexington

  6. Thank you so much for the post you do. I like your post and all you share with us is up to date and quite informative, i would like to bookmark the page so i can come here again to read you, as you have done a wonderful job. Charles

  7. You can also post some posters which contains specific information that clients would want to know. Emergency Tree Service Nashville, TN

  8. It is included in my habit that I often visit blogs in my free time, so after landing on your blog. I have thoroughly impressed with it and decided to take out some precious time to visit it again and again. Thanks.

  9. This comment has been removed by the author.

  10. It is the craft of designing a home to speak to all preferences and in this way will interest a more extensive scope of possible purchasers. houses for sale hidden hills