Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Dynamic query - deal gracefully with empty record sets

Currently - in workflows which make active use of dynamic queries - if the record set is empty then we end up with errors such as "a record was created with no fields".   This creates issues  when a dynamic query pumps rows out to a macro output, or where dynamic queries go into a join.


Could we change the result of the dynamic query so that if it returns an empty record set it still has columns but no rows and therefore doesn't cause errors in workflows?


Let me know if we need to provide a worked example?


Thank you



While I agree with you that the application should be able to handle this situation better, there is a simple solution that has worked for me.

Right before the dynamic input, add a union and have a single row of dummy data feed into it.  After the dynamic query, have a filter remove the dummy set.


🙂 Hey @patrick_mcauliffe - you're absolutely right - I've been doing precisely this (adding dummy rows), thank you for suggesting this!


it does work but becomes increasingly messy when the columns of the output are also dynamic (not known at design time)

For example - a macro that copies new rows in table X from database A to database B.  Because the table columns are not yet known at design time, it's painful (doable but painful) to keep on adding dummy rows because that also requires you to add dummy columns and then take them back out again.


Again - like you say - you can work around it, but it becomes increasingly messy.




+1. I'm doing a lot of JSON data parsing with dynamic columns and it's messy.


Totally agree with @SeanAdams, in the original post.


The dynamic tools should be able to handle empty data sets just the way they handle non-empty ones, or at least have a tick box on them to state if you want it to allow empty result sets without grumping.  The beauty of a tool like Alteryx is that it handles things without worrying about the edge cases. So when it doesn't it is frustrating. My workflows become big enough without additional controls and comments!


However, full marks to @patrick_mcauliffe for the above workaround, which is exactly what I need to do to handle the error around my Dynamic Replace tool.  🙂


I'm getting that error message when saving a workflow with a dynamic output in-db component to the gallery.  I'm not getting that error during execution but this is because it always returns rows.  I agree with all that is being reported here.  This is a bit shabby and below the usual Alteryx standards!