Enter Database/Table name Automatically

Hi Guys,

I have a workflow, In which i am inserting some rows from an Excel file to same table in Different servers.

So, same table structure, but that table is in 4 servers. So loading at once from the file.

I am looking for:- If i want to change the Database/Table name automatically at once on all server. how do i do it?? Right now i am doing it manually at each output in the worflow.

Please let me know if i am not clear or any question.

Worlflow is also attached!!!

Thank you!!!

Alteryx Pic.PNG

Workflow

Output

Tips and Tricks

Dynamic Processing

Database Connection

Accepted answers

Claje

Constants would definitely work for this, although I think that a single formula tool will make this configurable enough for your current use case.

To do this:

1) insert a formula tool immediately after the input file, and create a column with a name like "TableName".

2) define the table name in the formula - for now it would be : "PROD_MART_MIDS.SHPG_TLR_LD_STS_CT_INF"

3) on each output tool, check off the box that says "Take File/Table Name From Field"

4) Choose the option "Change File/Table Name"

5)Choose your "TableName" field as the "Field Containing File Name or Part of File Name", and uncheck "Keep Field in Output"

6)Then, to change what table it writes to, you can simply change the value in your "TableName" formula.

My opinion on why this is easier is that to my knowledge you would still need a formula tool in order to use the Constant for a table name, and so at that point you may as well define it in your singular formula.

If you were going to use this value in multiple different places a Constant would be more appropriate.

Claje

That's related to two things: 1) The base output tool is still pointing to the PROD_MART_MIDS table, even though it will never write there (without your formula pointing to PROD_MART_MIDS). You can change this to a more generic value in your output tool (since the table is being overwritten anyway). This will remove the reference to that table from the code, which could be helpful.

The other piece, is that this is showing due to the Annotation on the Output tool.

You can change a tool's annotation by clicking on that tool, and then clicking the "Annotation" button on the left side of the Configuration Pane, which looks like a sales tag.

In this window, you can type anything you want. I've screenshotted a quick example of this pane (attached).
While you won't be able to update this value dynamically, you can state that the formula sets this value, which should make it easy for someone to follow what the workflow is doing.

ExampleAnnotation.png

All comments

Garrett

Sounds like a good application for Workflow Constants.

Claje

Constants would definitely work for this, although I think that a single formula tool will make this configurable enough for your current use case.

To do this:

1) insert a formula tool immediately after the input file, and create a column with a name like "TableName".

2) define the table name in the formula - for now it would be : "PROD_MART_MIDS.SHPG_TLR_LD_STS_CT_INF"

3) on each output tool, check off the box that says "Take File/Table Name From Field"

4) Choose the option "Change File/Table Name"

5)Choose your "TableName" field as the "Field Containing File Name or Part of File Name", and uncheck "Keep Field in Output"

6)Then, to change what table it writes to, you can simply change the value in your "TableName" formula.

Shakzz

@Claje

I am not sure, that i am doing correctly step 2. Could you take a look at attached screenshot.

Thank you!!!

Pic1.PNG

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703