Extracting multiple tables from Salesforce

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!

Input

Salesforce

Accepted answers

BlytheE

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

All comments

BlytheE

Hi @asteryx,

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

BGirkins

Can confirm this as well in 2021.4. Both the v4.0.0 and v4.2.4 SFDC Input versions do NOT work. previously, you could update the Table and the WHERE clause dynamically. The trick is replacing the part of the text string found in the 'query - value=*SELECT' lineitem within the Action tool. this builds out the SOQL query and you can review the whole string (assuming the table isn't 100s of fields). so replacing the FROM and WHERE clauses using 'Replace a specific string' within the Action config works.

there is acritical limitation though. these Input connectors don't allow you to use the asterisk (*) to select all columns in the table. so unless I can dynamically build out the full query, it throws a 400: Bad Request error since the fields in the default table are not identical to each of the other tables getting processed. same issue using a Custom Query. Would be great if the asterisk was usable.

best trick I've come up with is to use Field Info tool after the Input tool and then use a Summary tool to concatenate every field name in the table. of course, I can't do it dynamically so it's not really a solution.

BGirkins

Follow up:

v3.1 seems to still work. just be sure to leave all fields de-selected when you configure the Input tool within a macro. apparently that tells it to dynamically cycle through Field Names as the tables are opened within the Input tool itself. biggest issue is v3.1 is now deprecated in 2022.1 Designer which I'm sure is related to Salesforce retiring older API versions.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703