Sign in or start a free trial to avail of this feature.
10. Creating Batch Macros
Learn how to complete and utilise a batch macro to automatically reference files in a local directory and post output to an alternative directory.
Notes on files
- The Timesheet files should be placed in a separate directory
- Users will need to set up a separate directory for Payslips in order for the macro to run correctly
Creating Batch Macros
- Batch macros are bookended by Control Parameter tools
- It’s important to correctly configure the action tools connected to the Control Parameter tools
- If the action tools are not adjusted, the macro may only run on one file
- The directory tool connects to multiple files from a single directory
In the previous lesson, we introduced the concept of batch macros. We created a simple workflow where we took timesheet information for a single employee and then cross-referenced that information with company pay rate data to generate a payslip for a defined period.
We'll continue with the workflow from the previous lesson and use it as a template for each of our employee timesheet files. If you haven't already done so, please put these employee timesheet files in their own folder. Our goal in this lesson is to create a batch macro that generates payslips for all employees. We'll accomplish this in three key steps. First, we'll specify the location for all relevant timesheet inputs for our macro. Next, we'll specify where the macro should deposit the generated payslips. Finally, we'll run the macro. For our first step, we'll specify where the macro can find the relevant input files. Unfortunately, we cannot use the macro input and output tools for a batch macro. Since the batch macro will use information from and generate multiple files, we need to specify the locations for those inputs and outputs. In this case we'll use the control parameter tool to specify the directory for input and output files. Each instance of this tool acts as either the input or output node for a batch macro. We'll now connect the incoming control parameter to the input data tool. We'll then click on the action tool and highlight the file path. This will tell the tool to change the incoming file for each iteration until it has processed all the files in the directory. We'll then go back to the control parameter tool and enter the label incoming. We're now ready to move on to step two and specify where the tool should deposit the payslips. We'll now connect a second control parameter tool to the output data tool at the end of the workflow. Again, we'll click on the action tool and highlight the file path. However, this time we'll select to replace the string. The tool will now save a new payslip for each iteration. Finally, we'll click on the control parameter and label this tool as outgoing.
Now that we've set up our batch macro, we're ready to move on to step three and see the macro in action.
We'll save our workflow and open up a new canvas. On the new canvas we'll navigate to the in/out tab and bring down a directory tool. In the configuration window we'll point this tool to the directory containing our timesheet files. In the file specification box we'll enter *.csv.
The asterisk means that all .csv files in this directory will be brought through the workflow. The directory tool brings in a number of items of information regarding the various files in our chosen directory. We're only interested in the full path name, so we'll bring a select tool onto the canvas and choose the full path field only. We'll run the workflow and see the full path address for each of the timesheet files in the specified directory. We want to apply our batch macro to each of these files and place the resulting payslip files in a separate directory. To do this, we need to give the workflow the address of the outgoing information. We'll bring a formula tool onto the canvas and create a new field labeled outgoing. Notice this is the same name we gave the final control parameter of our batch macro. As mentioned previously, we'd like to put our payslips in a new directory called payslips. We can easily do this with a replace formula. We'll set this formula to replace the timesheet portion of the full path field with payslips.
Our final step is to connect the batch macro to the workflow. We'll right click on the canvas, select insert, then macro, and finally our batch macro.
We'll run the workflow and then navigate to the payslips directory and see that individual payslip files have been created for employees one, two, and three.
It's quite clear that this tool could save us a lot of time if our company has hundreds of employees. Let's take a moment to recap this lesson. First, we connected a control parameter tool to the input data tool and specified the location of the incoming timesheets for our macro. We then connected a second control parameter tool to the output data tool and specified the location for the generated payslips. Finally, we ran the batch macro and generated payslips for all our employees. So far we've looked at how to create simple macros that perform one task, and batch macros that automatically perform a specified task multiple times. In the next couple of lessons we'll look at a third type of macro known as iterative macros.