In-DB Tools Interaction with Container Controls

[john_watkins]

We have seen some very different output since using container controls on Alteryx version 2023.1.    I know that the Tool-Container input should be the signal to activate the container control after all records are written, but when doing some in-db, we find it doesn't always work.   For the most part I have had no issues except with some processes that are asynchronous with Alteryx.

The best example I have is loading yesterdays results to a history table where I wish to simply Update/Merge the new results with the old to create a new output table containing both. 

Tool Container 1 --  Query NEWRECORDS and write the output back to a database table

Tool Container 2 -- WHEN TC1=Done then read from the current HISTORY table and the NEWRECORDS and to find out which records to keep/update and retain history and store into a TEMP-1

Tool Container 3 -- Use the combined dataset in TEMP-1 to overwrite the HISTORY table we started with giving me a complete dataset with current data now included.

We are seeing tool containers kick off before they are supposed to so we never know which step is complete before the next fires.  This is the opposite of what the tool container is supposed to do.   We have joined from the output of on control to the input of the next and seen this.  We have passed the entire result set from one container to the input of the next and seen this.    It seems to work if you incorporate the TC output with some input from the result datastream to force the in-db query to execute before going on.   I've also tricked it by joining one record from the previous result set and joining it to the tools in the next container and then just unselecting them all.   It feels like a waste of effort if the control just worked correctly.

Anyone know if there are any caveats when dealing with Tool Controls with in-db tools???

[apathetichell]

I personally don't use/wouldn't touch control containers - but I can a) see odd behavior with In-DB b) I can recommend two things:

1) disable your In-DB  by default. disabled by default will mean that it only turns on if some criteria is met. 2) if that doesn't work use dyanmic input in-db so your in-db is triggered from on canvas activity.

3) if that doesn't work drop control containers.

[john_watkins]

We used to use a collection of methods to try to control flow within the workflows.   Block Until Done, CReW Parrallel Block Until Done, batch macros, etc.   The  Control Containers seemed like it should be the answer to that mashup of more hacky solutions and make it easier to debug a workflow instead of multiple nested batch macros.   Not using either one is probably not a realistic option for some of my scenarios.  We only use In-DB when it warrants it now anyway.   Container Controls work fine with the normal gallery tools from what I can see.

Anyone else have any first-hand experience with In-DB tools and Container Controls?

[CailinS]

@john_watkins Thanks for posting this, sharing your work around, and even posting again here! I wanted to add that it may be worth logging a case with Customer Support through Mission Control (my.alteryx.com) to report this behavior and see if they can confirm if it is expected and/or if there is a workaround in addition to the one you found. Again, thanks for taking the time to engage here and share your experience!

[CailinS]

Based on what I see in the Help Documentation this may be expected if the flow described above has IN-DB connections 'spanning' the containers:

"In-Database tools work inside Control Containers. However, an In-Database connection can’t cross the Control Container boundary. In other words, you can’t have a Connect In-DB tool outside of your Control Container and then connect that to a Filter In-DB tool inside the container."