This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Wondering if this is possible. Here's the situation -
1. Inserting data into a sql table that builds a primary key in the sql table itself (so data from Alteryx goes in, but the primary key is incremented according to the sql table logic)
2. Need to reference this primary key (in the table above) later in the workflow as it is a foreign key in another table
3. The primary key in step #1 of course isn't built until the data is inserted, so the insert needs to come before the input data tool grabs those keys back later in the workflow
The problem is that Alteryx doesn't run the input tools within the 'flow' of the workflow - so when a lengthy workflow is executed, you'll see the input data tools start grabbing data way down the workflow path, even though the flow of data isn't close to getting there yet. Essentially the input data tools aren't synced with the workflow. I can see the purpose here for general performance, but there doesn't seem to be a delay mechanism if needed.
Anyone have an idea? There are of course workarounds here but looking for the most simplistic approach possible. Thanks!
You could place the Input tool within a macro as a shell. The macro could pass a dummy record so that it can be positioned wherever you'd like in the workflow and executed accordingly. Macro inputs and outputs don't always have to be related.
Also, don't forget about the Block Until Done tool in this situation.
Apparently if you use the input tool and load a sql table direct (so no custom sql essentially), the data will start loading at run time vs. when the workflow progresses to that point.
However, if you put a custom sql statement in the input tool, it doesn't end up running that statement until later in the workflow.
So in this case, the custom sql ends up working, because it delays the input tool from running until the data flow gets to that point. Why this works like this I do not know, but my workflow definitely works as expected after this adjustment.
I could be wrong, but I believe the tools execute in index order unless the flow predicates mandate otherwise (like a blocking tool). In other words, all things being equal, the tool added last will run last. If you cut the input tool and paste it back, it should get a new index (highest as well) and thus not execute until its data is required by a lower index tool. If you want to guarantee execution order for any tool which has no input connector, like the input tool, then you can put that tool in a batch macro. The tool can output straight to a macro output and a parameter tool which does nothing is added to give the input tie in. Just put a Sample tool with last 1 records before the call to the macro and it won't execute until the last record is received from whatever you feed it. You want only one record so the batch only executes once.
Thanks, I think you might be right on the index situation. It does look like the number of the tool plays a role in when that tool gets executed (with respect to multiple input tools in this case).
Thanks for the tip on the macro - that's definitely an option but I think the preference here is deleting and recreating the input tool with a latter index so the workflow doesn't get more complicated with macros. But will definitely keep that in mind for the future - thanks again