Faster field name edits in Alteryx

From a distance, editing field names might not seem such a big issue, worth dedicating a blog post. Yet, I realize how much experience I gained over time, dealing with such minor issues, which end up affecting my productivity significantly over time. In other words, consider each time you draft a new workflow in Alteryx, and you will realize that, whether you deal with long SFDC tables with __c names or other naming conventions that leave to be desired,  you get to rename fields a lot. And you most likely have not had the best experience along the way. Read on as I will share some productivity tips and warn you about potential issues, especially in INDB.

To illustrate those hacks, I will take the scenario of extracting the SFDC ACCOUNT table using the API standard connector. Note that the longer the table, the more relevant those tips get. Here, this table is short only for illustration purpose. You will most likely have some custom fields in there, with suffix __c in their API name. Here is the Account table definition in the Select tool:

It is not really related, but as a first step, I would advocate selecting all the SFDC ID fields, which are easy to spot, as their size is always 18. Press the CTRL key while you select them if they are not grouped, or sort fields by size and select all the 18 long. Then change their type to String, for performance, since we know every entry in that column will be exactly a 18 characters String:

Next, we need to enhance those field names by renaming them to match their description, because ID alone or even Type or Name does not say much, especially when you work with several SFDC tables such as ACCOUNT, CONTACT, or LEAD which all have a ID, Name, Type. Alteryx offers a great feature for that purpose: Add Prefix, which works for one to many fields, at once:
Note that this feature is available in the INDB version of the Select tool as well.

Now, how would I remove the unsightly __c from the custom fields? I don’t care knowing which field is custom in my reporting table… One would assume I could use the Remove Suffix feature, but alas, it is grayed out… Well, fear not, we can overcome that bug/limitation by inserting first a Prefix easy to remove such as ZZZ(Space), and then Remove Suffix will become enabled:
All I have to do is select the __c option, followed by a remove Prefix ZZZ(Space) and I am done with many fields at once, without any risk of typo!

Now, let’s get into a more complex scenario, where you inherit a really long table, where all the fields are in Upper Case, and you need to change all of them to Lower Case or Proper Case to match the rest of the tables you are working with. That scenario happened to me when I used the excellent Alooma web service to extract SFDC tables, which sadly, converts all the fields names to Upper Case… Well, Alteryx does not offer a feature to change the case of field names automatically. Yes I know, there are some functions available in the Formula tool, but using them to change the field names of a data source is a much larger effort than I care for, it should be plainly in the Options menu of the Select tool, and it should include Proper Case:

Anyways, while Alteryx engineers work hard to include that feature, there is a hack that will ease the pain. It will require the use of the excellent utility Notepad++ which you should have already, if not, it is free, as in “free beer”.

Step 1

Select all relevant fields, and add Prefix ZZZ_ or ZZZ with a space, and save workflow.

Step 2

Open the .YXMD workflow (which is really a XML file) into Notepad++ and press CTRL + H to open the Search + Replace window:
After Searching for ZZZ and replace with nothing, you will get to your field names. Select them, and press ALT + U to convert them automatically to Proper Case, or CTRL + Shift + U for Uppercase, CTRL + U for Lowercase. You can even automate the process by recording  a Macro, or by using some Regex in the Find What window followed by Replace All… Notepad++ offers a wealth of text manipulating functions documented here.
In our example, with a ZZZ(SPACE) inserted as Prefix, I would use this Regex to automatically replace all Field names by the Proper Case version:
Note the Search Mode is set to RegEx, and here is the code ready to paste:
Find What: (rename=”)ZZZ (\w)(\w*)
Replace With: \1\u\2\L\3
If you want to get rid of the __c in the same swipe, use:
Replace With: (rename=”)ZZZ (\w)(\w*)__C or depending (rename=”)ZZZ (\w)(\w*)__c 
More Regex reference here and this convenient website to test your Regex with explanations: Regex101.
Save the workflow and reopen it in Alteryx to see all the changes completed…

Note that if you edit field names in a INDB workflow, and that the Select INDB tool is followed by any other Select INDB tools, they will display the changes,  but will NOT implement the changes to the underlying  generated SQL code, even after pressing F5 to refresh… The workflow will fail to run, indicating missing fields and I believe it is a BUG in the Alteryx INDB functions. The remedy is quite simple: Insert a new Select INDB tool before the existing subsequent Select INDB, then remove the subsequent Select INDB, this will force the refresh of the underlying SQL code.