Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Enter Database/Table name Automatically

Shakzz
7 - Meteor

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!!!

9 REPLIES 9
Garrett
11 - Bolide

Sounds like a good application for Workflow Constants.

Claje
14 - Magnetar

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.

Shakzz
7 - Meteor

@Claje

 

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

 

Thank you!!!

Claje
14 - Magnetar

Hi,

Sorry - I should have called out the quotes better!
The issue is that Alteryx thinks that thie value you have in your formula is a field, constant or formula currently.


If you put quotes around that value it should fix this error!

Shakzz
7 - Meteor

Hi,

 

Thank you so much for your step by step instructions, that helped a lot and i appreciate it.

 

one last thing is :- So i changed the table name in the formula and actually it also loaded the table, which i wanted. But in the output it still shows previous table name. if i want to change that also with the formula table name, then what we need to do??

 

Attached Pic.

 

Thank you!!!

 

Claje
14 - Magnetar

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.

Shakzz
7 - Meteor

Awesome, That make sense!!

 

and if i do not want to insert on one server and run on the other three server, What should i do to 'Switch off'??

 

Thanks!!!

 

 

Shakzz
7 - Meteor

@Claje

 

Hi, I got the last answer by using 'Tool Container' to Disable any flow.

 

Thank you so much for all your Help today!! I really appreciate it.

 

Thanks!!

Claje
14 - Magnetar

I was just about to recommend tool containers! :)

Glad I could help

Labels