Hi, I’m looking to use the ‘Database Column Filter’ with flow variables. I notice that this node doesn’t have the enforce inclusion and exclusion options that exist in the ordinary column filter (nor other handy features such as Regex filtering), which I’ve found is making it hard to get the results that I’m looking for. Specifically, what I’m looking to do is to remove three specific columns from a table on each iteration of a loop. The names of these columns change on each iteration, and are stored in a flow variable. All other columns I wish to keep, including columns that the node hasn’t seen before (which is where force inclusion would come in very handy).
As per the screenshot below, I have a set of flow variables defining which columns I wish to exclude. However, I’m finding that when I run the loop, new columns are automatically excluded, which of course isn’t the behaviour that I want. It won’t work to try to add column names to an include list, because this is a recursive loop (writing out to an external database on each iteration), so the list of new columns grows with each iteration of the loop. I’ve also tried:
i) adjusting the (apparently undocumented) keep_all_columns setting (setting this to 1)
ii) specifying the ExclList array size as 3 (as per the screenshot)
…but haven’t found these adjustments to result in any change to the behaviour of the filter.
There are a couple of potential workarounds for this issue:
i) first writing the table to my database, and then building using the flow variables to build an SQL statement to perform a series of ‘ALTER TABLE <table> DROP COLUMN <column>’ statements
ii) on each iteration, performing an inclusive filter with the column filter node (which does work using flow variables, showing that the behaviour of this node is asymmetric in that respect), and then using the database table join to join this back to the table coming into the loop (on row_id)
..nonetheless it seems like by far the me simplest and most readable approach would simply be to be ab able to use the node in a way that enforces inclusion. So, in sum, my question is – is there something I’m missing here? Is there actually a way to do this?