function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
miasolemiasole 

Using AND / OR statements in Excel Connector

When creating a query with Table Query Wizard, how are you able to create a "AND" or a "OR" statement in between the clauses?
CRM08CRM08
Hi,
B1 cell->Account Name
c1-> like

To construct an "OR" query, you may use comma seperated values in the value cell, for example "3M,Abbott" in cell D1 would generate the following string to SOQL:
where ( name like '%3M%' or name like '%Abbott%' )

Valid operators for cell C1 include all SOQL operators and a few more including: equals, contains, not equals,greater than, less than these are converteed into their SOQL versions "=,like,!=,>,<"

Also available are begins with, ends with, regexp which construct strings to pass to SOQL with wildcards, or in the case of regexp you pass in the wildcards. Example:
Account Name | regexp | "3M%"
will construct the following SOQL: name like '3M%'

Thanks..
wkaikwkaik

What about this example? How would you handle this.

 

Field1     equals     ABC      OR     Field2 =    123

 

 

Thanks for the help.
gamer632gamer632

I don't have a solution for your query just the workaround.  I pull the field 1 equals abc on Sheet 1 and then Field2 equals abc on sheet 2.

I combine the sheets and use a 3rd party duplicate remover to delete the duplicates.

 
JDE ADMINJDE ADMIN

I bet a guy could open up the VB script and make some changes to allow for this.

 

I do the Sheet 1 Sheet 2. I combine them together.  Then I highlight them all and Go to Data>Filter>Advanced Filter> and Filter Duplicates.

I then copy this to a new sheet.

 

Its silly that they dont allow to OR between Fields.
JDE ADMINJDE ADMIN

OK So If you open up the VBScript Editor, Goto The SForce Module.

Search for the sfQuery Sub.

 

Search for  " and "   include the quotes and space in your search.

 

You will find 2 places where this matches. One or both must be changed to " or "  including quotes and space.

 

It is most likely the second instance of this because the first involves joins and if you are doing that and want you where clauses in your join to be OR change the first instance.

If you dont know what a joine is then just change the second instance.

 

Scott
JDE ADMINJDE ADMIN

Ahh I would just change both. The first instance definately works ignore all my Join talk.

 
bunrothabunrotha

Zombie thread rises....

 

Thanks for the pointers on this.  The next steps in my work involve being able to implement something closer to the "Advanced filter criteria" of SFDC reports, so I can run a query with nested filters.  Example of a filter criteria that works in SFDC reports, of the type I can see use for:

 

WHERE (datefield >= 09/01/10 AND datefield <= 09/13/10) OR (booleanfield = TRUE AND stringfield = 'Just make it work')

 

Right now I'm using the inelegant dodge of running a report with the advanced filters, exporting the printable view to Excel, and pasting that into the worksheet where I need it, so the rest of the worksheet can access it.

 

I've experimented with using seperate queries in Excel, but consolidating the results in Excel is proving tricky.

 

Has anyone else done something like this, either by changing the VBA, or by means of Excel wizardry?