Control Input on Inserted Macro

[nbayly]

I have created a Batch Macro that takes as input 4 fields, including a date. Based on this date there are some Dynamic Inputs that query an external database, extract and transform data and finally store it into a Data Warehouse (MySQL).

I have then created a workflow that queries a database and pulls numerous rows of the 4 input fields and feeds it to the input of the inserted macro I created. The intention is for the Macro to iterate through the rows running the Macro Workflow using each as input. No output is necesary but it outputs the same inputted row with some fields modified. The problem is that the macro throws an error stating that it requires a Control Input. It does not run without this inputed. If I connect the same input to the control input, the workflow runs with errors and returns the square root of the number of rows inputted (e.g. 5 inputted, 25 returned). I can see that the macro has inputted something into the Data Warehouse but it doesn't run correctly, probably due to the duplication I suspect.

Could someone explain what the requirement of a control input means and how to configure correctly?

[fmvizcaino]

Hi @nbayly ,

The control parameter is used to guide your batch macro. 

The simplest way of explaining its use is to say that for each line entering in the control parameter, the macro will run one time.

So if the idea is to run one time per date you have available in your dataset, you should summarize your date and use it as your control parameter.

There are a lot of things you can do with a batch macro, I tried to explain in the easiest way possible and focused exactly in your need.

Hope this helps you.

Best,

Fernando Vizcaino

[nbayly]

Hey @fmvizcaino , thanks for your reply.

Sadly this still leaves me confused. If I remove the input connection and only have the control input connection the Macro does not run and the Workflow errors out. To clarify, my database input that is pulling 5 rows of 4 fields connected to either the Input or Control Input (one OR the other) errors out. If I connect the DB Input to the Control Input, what do I feed into the Input of the Macro?

What do you mean by summarize my date? Each row is unique and represents a separate time frame. Or are you saying that it can only accept 1 field as input?

[fmvizcaino]

Hi @nbayly ,

Only to understand a little better about your problem and to answer you correctly.

This 5 rows of 4 fields contains information for you to use in a dynamic input tool, right? 

If yes, as you query the data, you use an output tool and for all 5 queries you make, you insert everything in the same table or one of the fields carry information about where to insert?

Because depending of your use case, you don't need a batch macro to do it. If you can, share your macro or image of it please.

Best,

Fernando V.

[nbayly]

Yes, I use one of the date fields in a Dynamic Input among 3 major steps I take with the Input data. It uses the date to filter a query to a specific timeframe and then outputs the results into a Data Warehouse.

For simplification though, I have disabled all steps except for as screenshot. It takes the 4 fields inputed, adds a completed_by datetime to one of the fields and changes a completed flag. It then updates these values to the database that was used initially queried. If I run this based on the 5 rows (sample screenshot below) it should update each row with a completed_by and completed flag in the Data Warehouse.

Workflow

Sample Data

Macro Workflow

I hope this clarifies my current situation. Please let me know if you need any further details.

[fmvizcaino]

Ok, so for the workflow you showed me, there is no need of a batch macro (batch macro demands a control parameter).

So the only thing you need to do is to transform it into a standard macro and the control parameter will just disappear.

If you are not sure in how to turn to a standard macro, see the image below.

Let me know if that works for you or if I understood everything wrong once again hahahahaha

Best,

Fernando Vizcaino

[nbayly]

@fmvizcaino Thanks again for your reply. I see now that my simplification by disabling some steps has led us astray. This just pushes the 5 rows in one go and updates my database. But I need it to iterate through each one as they are the string replace inputs for a Dynamic Input that I didn't show in the screenshots. Same results as from your solution but doing each row individually. Apologies for the confusion and warm regards.

EDIT: just need to clarify that I also pass the full 4 fields to some of the datastream so need more than just the datetime.

[fmvizcaino · Accepted solution]

Hi @nbayly ,

I have created an example to help you understand the influence of a control parameter in a batch macro with or without an input tool.

If you have 4 fields you need to use each time your workflow runs, you can use 4 control parameters to do it.

Lets hope now I got it right! hahahha

Best,

Fernando Vizcaino

[nbayly]

Hey Fernando,

This totally helped in understanding how the Batch Macro's work. As soon as I have removed the Macro Input and only had the Control Parameter with a correct replacement string then the Macro only required 1 input.

That being said I don't get why the Macro Input doesn't just feed in the data stream that you feed the macro. It even has you input sample data for testing but then totally doesn't work how I would expect after. You have to make some complicated string manipulation through a Control Parameter. Seems a bit convoluted. In any case got it workingl Thanks for your extended support.