Macro: select columns and use fields downstream

[Peter4]

I'm splitting my workflow into 3 streams to clean it. Those 3 streams are identical apart fom the fields I select in the Select tool. I thought I could optimise this step with a macro and I was able to update the select tool. But how do I get the parse tool to use the field I have previously picked? It seems to be hardcoded.

[Blake]

Hey @Peter4 

Luckily you don't need a macro for this! Since all your fields have the same delimiter (and should have the same number of concatenated chunks of text), we can transform your data in to one field and treat it all the same. 

I think the text input you included in the example was truncated but this should work on your full data set. 

See the attached workflow and let me know if it works. 

[Peter4]

Thanks Blake!

But just because I just started learning about macros, how would I update the field in my text-to-columns tool?

[Blake]

Hey Peter, 

I think you would need to make this a batch macro wrapped inside of a standard macro since you are looking to work with one field (batch) at a time. 

More information on Batch Macros 

Thanks, good luck! 

[DavidP · Accepted solution]

I made a couple of changes to your macro to get it to work.

1. Use drop down instead of list box. The problem with list box is you can select more than one and the text to columns tool can only handle one field, so it has to be exclusive.

2. Remove the Select Tool at the start of your macro and use Dynamic select after the Text to columns.

As Blake said, you don't need a macro for this, but I appreciate that you'd like to practice building macros. Well done for experimenting, you weren't far off.

[Blake]

Hey @Peter4  - I've created a batch macro inside a standard macro that does what you were looking to accomplish. It works but I would strongly recommend using the original logic I posted earlier in the thread rather than this macro because I have not tested it with any other data and it was tossed together at the end of a long day. 

The screenshot below shows the inside of the batch macro. The batch macro takes one field at a time via the control parameter and runs it through the text to columns and select tools. It then appends the field name, gives a record ID, and outputs to the standard macro. 

The standard macro takes the selected fields and feeds them in to the batch macro. After the fields are processed in the batch macro, they are run through a cross tab and a select tool to remove the Record ID tool

Finally, the standard macro lives on the main canvas.

Thanks, let me know if you have any other questions or want further explanation on how it works.