dynamic join multiple

[Mikis]

Hello,

I've built a flow that outputs a certain number of tables (currently 3, but the number will grow).

I want to join all these tables on a field with a variable name.

So what I think I need is a "join multiple" tool, but with an additional input that allows me to enter the join field.

Also, it would be great if I didn't have to modify it for 4, 5, 6... tables

I tried to build a batch macro like below (and attached), but it's not working for 2 reasons:

1) my "replace field1" (the join field) isn't working, I think because I need to update the entire XML 

2) I need the number of inputs to be a variable (just like this tool expects, but I don't know how to model that in a batch macro)

Anyone with an idea?

Thanks in advance,

Mikis

[DavidP]

Hi Mikis,

I played around this morning to see if I can get a solution for your problem. I think trying this with the Join Multiple tool will be problematic for at least 2 reasons I can think of:

 1. I think you can only update one value in a tool with Control Parameter/Action Tool, and you need to update the Join field for each input.

 2. As you add more sources, their default join fields will be blank.

I therefore tried something else, but it might not be what you want. If your sources all have the same fields/structure, you could use  a Union tool and then a Summary tool and Group on the fields you want to Join on and then bring every field through with Concatenate. You then need to split all the comma separated values with Text to Column Tools and put the whole lot back together again, for which you can use the Join Multiple tool and join by record position.

You can then insert a control parameter on the Summarize tool to enable you to dynamically decide which field to group (join) on. I can't yet figure out how to make it so that the field structures of the sources can be different. This method also means you have to set up the Summarize and Text to Columns tools manually, so if you have lots of them this might be a pain.

I attach my solution anyway - maybe you can draw some inspiration from it. In my example I generated some dates as test data.

[DavidP · Accepted solution]

Sorry, I take it back. You can have multiple control parameters or action tools updating values in the same tool. I've updated your original workflow. In the configuration window of the 3 action tools, you just have to select the field you want to update in for each of the join conditions.

You still have the problem that you'll have to add more action tools is you add more sources.

Attached is an updated version of your macro that works for the 3 sources.

[Mikis]

Thanks DavidP, it might not be 100% what I hoped, but it's definitely quite close :)