Filter to cycle through list

Hello,

I have a workflow that creates a PDF report driven by a single filter. In the example below, I could choose to filter "1234" run the workflow, then manually choose "5678" and rerun the workflow. Is there a way to have the workflow loop through the filtered list choosing one cell at a time and stopping when it reaches the end? I have attaches a sample workflow. In my sample I don't have an output, but that's not necessary here...just looking to cycle through the Filter List column. Thanks

Annotation 2019-10-03 091253.png

Sample Workflow.yxmd

Macros

Apps

Dynamic Processing

Accepted answers

djodts

I worked with a coworker and have come up with the solution to my batch macro. In simple terms, the solution was the inverse of how I expected it to work. Rather than a macro driving my filter, my entire workflow became a macro.

I am replying to my own post in an effort to help those of you who are looking to create an individual report by having a macro loop through a list and generate a single report per cycle. I have also attached the files so anyone can walk through this. In my example, I have 10 stores with revenue and expenses. I want my workflow to calculate the net and then print one PDF for each store. I could manually enter the Location ID into the filter, run the workflow, enter the next store Location ID and run the workflow again. However, it is much more efficient to have a macro run through a list and do this tedious process for you. I have seen some videos but some were overly complex so hopefully this helps you.

The initial prerequisite for this is for you to create the following folder: C:\Reports as this is where the workflow will save the reports. Of course, you can create whatever folder path you want but will need to edit the workflow once we get to that point. I have also attached four files. The Initial Workflow, the macro created from that workflow, the Data Workflow and the end result.

One more thing to mention before we get into this which I’ll explain in more detail below but in hopes to alleviate some frustration, you cannot just download my final workflow and go with it. You do have to walk through this process so your workflow knows where the macro is saved. I’ll get to that in more detail at the end.

Here goes:

After creating the C:\Reports folder open the attached Initial Workflow. You’ll notice some basic data. The Location ID, Store Name, Revenue, and Expenses. Let me briefly explain each tool in case you’re new and not sure why it’s set up in this way.

The first filter is currently set to filter for Store1, so that when I run the workflow, only one PDF is created for Store1 and all other stores’ data is excluded.

Select Tool: I only inserted this to change the Revenue and Expenses data type. I believe it had initially pulled it in a “string” data which wouldn’t let me do simple subtraction.

Formula tool: is simply calculating the Net difference between the Revenue and Expenses.

Table: Here I chose which columns I want to appear on my report and excluded those I did not. I did not add any formatting other than centering and right aligning some columns but I’m not going to discuss formatting as it’s for another post. Within the table, I did not select the Location ID since I didn’t want it on the report, but I do prefer that the filenames contain the location ID.

Multi Join tool: Since the Location ID is not chosen in the Table tool, that field will not flow through, so I needed to insert a Join Multiple tool and I fed the filter into this in order to get the Location ID back into the workflow.

Formula Tool: this second formula tool is being used to dynamically create the full filepath/filename. This step is crucial because if you successfully create a macro to loop through your locations and generate a report for each one, your work will be for nothing if you do not create a separate file for each. Without a dynamic filename, each generated report will just overwrite the previous file created just seconds before. You can also get creative here and insert a date timestamp to the file or any other preference you have.

Render tool: Within the render tool, I only made a few changes. Be sure that you use the same. Note that though the Output filename is not going to be used, the tool will error if you leave it blank. The field “Filename” will drive where and how each PDF will be named.

Now, onto actually building the macro.

Once you’ve opened the workflow, right click on the Text Input tool and select “Convert To Macro Input”. Don’t worry, you’re not rendering this input frozen in time with the current data. We’ll revisit this further down.

Once converted, the icon changes as follows.

Next, go to the Interface tab and insert a Control Parameter and an Action tool and place as shown. I have connected these to the Filter Tool since I want the macro to interact with the filter as explained above.

Before moving on, select the Action tool and drill down to Operands and select Operand-value=”Store1”. This is telling the action tool that within the filter, this is what we specifically want to update. The filter’s value (which I had initially set to “Store1”).

Next, go to File, select SaveAs, then choose a location and enter the new filename. Be careful in this step to not accidentally click Save and overwrite your Initial Workflow. If you are working with your own workflow, when you get to this step you may want to copy it into a new workflow before converting it to a macro to reduce the risk of saving it over your original workflow.

The next step is to insert the macro into your original workflow. To keep it simple, I have attached a “Data Workflow” that can be used. To create this, I took the Initial Workflow and deleted all tools except the first Text Input tool which had my data. I made a copy of the text input tool and removed all data except for the Location IDs and labeled it “Store List”. This step isn’t necessary since the Location IDs are in the Text Input tool that has all of the other data, but I feel that it helps visualize how to think about connecting the macro next.

In order to insert the macro, simply right click on the canvas, select Insert, and go down to Macro at the bottom of the list. If your Macro is still open, you’ll see it. If not, you’ll have to browse for it. If it is open, in my experience, you have to move your cursor over the actual little circle icon to insert it. If I click the name of my macro it usually doesn’t insert.

Once the macro is inserted, connect the Store List (the list of Location IDs) to the upside down question mark input of the macro. The data is to be fed into the lower input. As I mentioned above, this data (Text Input tool in our case) was converted to a Macro Input. The data that was in this tool which is now part of the macro is no longer used…what is used is this Text Input, so if your input is dynamic you should be able to feed that into the macro and not worry that you have static data (the Text Input that’s baked into the macro) that is feeding the workflow.

Before moving on, click the macro and set both dropdowns in the “Group By” tab to Location ID.

Next, select the Questions tab and also choose Location ID. At this point, you can save the workflow. If you hit run, the ten Store files should be generated in your Reports folder.

Contrary to Excel, macros are not embedded into the final Workflow file so you will need both files…your workflow and the macro that we will create to be saved to your computer. In my example, I saved the macro to the same Reports folder. If you ever move the macro, you will need to reinsert it into your workflow and follow the same instructions above. I wish Alteryx would allow the full workflow of the Macro to be embedded in the Workflow that it was inserted to. This would allow users to be able to send one file. If you wanted to edit the macro you could expand it similarly to a minimized container. If you agree, see my post here and give it a thumbs up.

DataWorkflow.yxmd

LoopMacro.yxmc

Initial Workflow.yxmd

FinalWorkflow.yxmd

All comments

wdavis

Hi @djodts

If you have a look through this article - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Iterate-through-data-to-send-multiple-PDFs/td-p/334477 a solution has been posted on here which I think will cover what you are after!

Let me know if this doesn't work for you though.

Kind Regards

Will

Vicks

You can create an iterative macro using the sample tool. Two sample tool should be connected to input tool. first sample tool should only take first record and connect to the macro output. The second tool should skip the first record and to reverted back to input tool.

benakesh

Hi @djodts ,

You can loop through batch macro . Simple batch macro using 'group by' configuration is attached .

Hope this helps .

Sample Workflow_bm_10.3.yxmd

bm_10.3.yxmc

DiganP

@djodts The macro would be very simple.You would want to use a batch macro instead of a iterative macro.

You can use the filter tool before to filter the list and go through the macro.

Attached is the workflow and the macro.

It just requires an input/output.

Sample Workflow.yxmd

New Workflow3.yxmc

djodts

Thanks @wdavis . I did see that, but not sure I follow why it was marked solved. I don't quite see how it addresses cycling through a list. It seems to address how to dynamically update PDF names which I have been able to do. I just need the filter to cycle through a list that will change monthly. The filter is at the very beginning and drives about 150+ tools within the workflow so I need the action to take place in the filter tool itself.

djodts

Hi @DiganP @benakesh

Thanks, but it doesn't look like the macro drives the filter. I am reattaching an updated workflow because I don't think I was clear. My workflow starts with a data input. In the workflow example, that is the store data/sales. If I didn't have a macro, this is my process (or how I would like the macro to behave). I would look at my list of stores (A, C, D, and F in the new attached example). I would enter "A" into the filter and then run the workflow to get my report. I would then enter "C" into the filter and run the workflow, and so on until I ran it for all four stores in my list. I don't want it to run for all of the stores in the list at the same time. I want it to look at one, run the workflow, then the second, run the workflow, and so on.

I watched the video "Build Your first Batch Macro" by JoeM and though it seemed to be promising, I think it runs for all items all at once.... at least that's how his output appeared.

Below is my attempt to insert a batch macro after watching the video. This is my actual workflow but too much to attach. I don't want to package my workflow into the macro, but rather I want it to just cycle thought the list of stores one by one and then after entering the first ID into the filter to run through the massive workflow, then the next as I described above. Hope this helps clarify.

Annotation 2019-10-03 175854.png

Sample Workflow.yxmd

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335