+ Start a Discussion
Steven MSteven M 

Sforce Connector - how to query related tables?

I have accounts with specific qualities and these accounts have contacts. I want to run a query on contacts where the accounts have a certain quality. How can I set conditions that relate to a related table? I hope someone can help me :)
If you get the Apex Explorer software http://wiki.apexdevnet.com/index.php/Apex_Explorer
Then from here you should be able to hack the sql around a bit to make your link.
This basicly the kinda thing your after?

Select c.id, c.name, c.email from Contact c where account.name = 'TESTACCOUNTNAME'

 I think that should work...
Steven MSteven M
Thank you but unfortunately as a small(er) company, we run the Professional edition and only have acces to Sforce Connector. I'd be grateful if anyone could help me find a way to construct a query in Excel that lets me update fields between two related tables.
I don't think the Excel connector supports relationship queries, unfortunately.
Actually, it does support relationship (join) queries.  To read about it, use the online help.  To get there, click on the sforce connector button, then Help on sforce Excel add-in.  Then go to the last advanced feature in the table of contents - joining tables.
Steven MSteven M
Great...I immediately tried this but ran into another issue. When I first run a query on the contact table. The Account ID shows up as the name of the account, not as the alphanumerical ID. When I place the Account table next to it (including the word refresh net to Account, it gives me the error "invalid range, missing data type, or other error, type is: Account bad id xxx"

I must be doing something wrong...help very much appreciated since this will help me maintain our database so much more efficient. Happy to send you this excel spreadsheet if that helps.



Well, here's how I handle this and admittedly it's kind of a pain, but does work.  I, like you, generally have my options set to display the name instead of the ID.  But, to do the join, and also sometimes even for updating data if the name is more than 15 characters, you need to use the ID.  So, simply change the options to use the ID (Options | Related Names tab).  Then refresh the query so the the IDs are displayed.  If you want to see the names, once the joins / queries are complete, change the options and do a refresh again.  Or, sometimes I copy the column of names and put it to the right of the table, but you need to leave a blank column when doing this so that the names are maintained, instead of the IDs when you do the refresh.

Let me know if this isn't clear.  Kinda hard to write up how to do it -- easier to do it :smileyhappy: