Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mike Schinkel.ax196Mike Schinkel.ax196 

Excel Connector: Possible to query for accounts given a list of opportunities?

Using the Excel connector, is it possible to query for the accounts associated with a list of opportunities? Alternately, is it possible to query for accounts given a list of account IDs?
ScotScot

There are several ways to retrieve the account information that corresponds to a queried list of opportunities.

One, the most manual, but very straightforward, uses Query Selected Rows

   > on a different sheet from your existing query of opportunities, build the column headins for an account.  (could be with the wizard, stopped before adding criteria, or by using the "Describe Sforce Object" menu choice)

   > copy the account id's from the opportunity query into the first (id) column of the account table

   > highlight these in the account table, and use the "Query Selected Rows" menu choice.

The second is to use the callable functions.

Here, you'd add a formula column next to your opportunity table. The formula will use one of the lookup utilities documented for the connector.  I've not used this recently, and there are some functions which are currently broken because of breakages in the underlying office toolkit.  Therefore ... might take a little debugging.

The third would be to build two related queries using the Joining Tables technique.

For that, I'd recommend the documentation.

The documentation (for both the functions and the joining) is on sourceforge at:

       http://sforce.sourceforge.net/excel/index.htm

 
flescor2flescor2
I want to match a user's Excel list of marketing names to existing contacts in SFDC (don't ask). The obvious way to match is by Email Address. When I attempt a query on the Contacts table using an ON or IN join to the existing data, I get an error message: Unexpected token: Email, Exception 5087. Is it not possible to join datasets via the Email Address field?
foghornfoghorn
ON and IN are not support SOQL.

You can do a like or equal though, so

select id from contact where email LIKE('bob@myhouse.com')

which will give you the contact(s).
Mike Schinkel.ax196Mike Schinkel.ax196
foghorn wrote:
ON and IN are not support SOQL.

You can do a like or equal though, so

select id from contact where email LIKE('bob@myhouse.com')

which will give you the contact(s).

Actually it is possible to use ON and IN via the Excel SForce Connector. See "Joining two tables" in the online help for the Excel Sforce connector.
Ron HessRon Hess
i think you will find this function inside the sforce_connect.xla

=sfemail(cellref)

this will return the contact ID for for the given email address or cell containing email

if the email address is missing from sf.com, you get a #N/F

or you could export all email addresses, then vlookup() this list against your marketing list, this is what i used to do before writing the sfemail() function.