Extracting multiple tables from Salesforce

[asteryx]

I've successfully extracted one table of data from Salesforce using the Salesforce Input tool. However, my goal is more complex and I haven't found anything similar in any other posts.

I would like to pull a bunch of Salesforce tables into a SQL database on a regular basis. So, that means changing the table name dynamically - but not manually - in the input tool.

My guess is that this is a macro that uses a parameter. But I can't figure out how to feed a bunch of different table names into the parameter tool and get it to repeate the same steps for each table in my list. I'm guessing the macro has to use the 'select all' option since the tables keep changing each time.

In addition, if I could pull the table names from Salesforce as metadata first, that would be great, but I haven't seen that explained anywhere.

All help and suggestions are greatly appreciated!

[BlytheE · Accepted solution]

Hi @asteryx,

If you have a list of the tables you need to pull from Salesforce, that can be used as a Control Parameter in a batch macro. The macro will contain the Salesforce connector, a Formula tool to change the name of the table going into SQL, and the Output tool. The Control Parameter connects to the Salesforce connector and the Formula tool so that the table name will change and the formula tool can create a field used to change the file/table name in the output tool. To grab all of the output fields, a checkbox set to update that part in the Salesforce connector does the trick. Then it's a matter of connecting a text input or regular input that lists the tables needed to upload to the macro and selecting the table name field as the control parameter. The tables listed will be copied to the SQL database one at a time without having to change any configurations in between. The screenshot below shows how to set the macro up and was completed in 10.1.6

[asteryx]

Thanks,

Sorry it's taken a while to accept your solution.

I did get it to work - though deciphering some of the properties of the tools took extra time. I get, for example, that an Action tool can update a target value, but looking at the tool's properties, it's very hard to see which value is the target and how to configure the replacement text (e.g. with "" marks or without). The @value - xxx... code is very vague.

In any case, your solution got me a bunch of database tables.

Note to others: if you output to the workflow instead of writing to a database, you will only be able to see one of the tables at a time.

[patlaff728]

Hi @BlytheE,

Thanks so much for this solution. This looks like almost exactly what I'm looking for as well.  However, as I'm still pretty new to batch macros, would you by any chance have a sample file you could attach for me to take a deeper look at how each piece is configured?

Thanks very much,

Pat

[aestrada]

I haven't been able to make the macro work.  Will you please take a look at the following and provide guidance?   

Specifically , the Action tool that changes the table on the SF connector is not updating.  Note: the output is currently pointing to a temp folder but I also attempted to output to a sql database. 

thank you!

[amti]

I'm also facing the same problem: it seems the tablename is not being updated with the control parameter and action, at least on the Query Builder for SFDC connector. I'm not able to use Dynamic Input with SFDC, don't know why. But this would probably work right? Update value for Table=.

In other connectors this works ..

[aestrada]

I found that the current Alteryx SF connector does not support this feature anymore because SF changed something on their side. Although if you use the previous version of the connector , it works with the parameter call.

To access the previous version go to the connector tab >  right click on the tool bar > select "Show Deprecated Tools" > select "OK" to the prompt.   You should see a second Salesforce Input tool with a orange ribbon "Deprecated". 

Good luck !

[amti]

@aestrada 
Did you reached out to @Alteryx support team? I'm facing the same issue: I had a very nice workflow calling a macro like the below one, with old connector. I then changed the select all param, tablename and my filter param as well, all good.
Now, I'm getting this 400 error because this actions simply do not work with the new connector anymore and downgrading it's not an option for me (old connector calls a really old and soon to be deprecated SFDC API, the latest one calls v42 SFDC API and the latest is 54!!!).

[aestrada]

I had limited luck with the Alteryx connector afterward therefore moved to Power BI dataflows,  please repost if you find a solution!  thank you 

[amti]

hi, again @aestrada .

No luck either ...yet. Truly disappointed with this ...
I would expect , at least, bare min., for the new connector to keep or improve features, not downgrading it!
Meaning , control parameters worked before for scenarios like update value in string, update value using checkbox control apram to select all fields ... in v4.1.0 it worked in latest version (v4.2.4) it doesn't ...so, this sounds more like a beta version :S

I've contacted Alteryx support ....let's see. But I certainly cannot wait for the new connector to be released.
Maybe the old connector was fine/better and maybe they could try, I don't know, to call a recent version instead of really old ...probably this would be,in theory, easier, from a development PoV.