+ Start a Discussion
Thomas FullerThomas Fuller 

Apex/Visualforce - Issue with SOQL Conditional Statements

Hey everyone,

I am currently working on a project that our organization is going to implement in our salesforce org, in order to increase user’s efficiency searching for specific contacts and leads. I proposed to create a visualforce page and an Apex controller that would allow users to search for contacts and leads in real time using javascript/jQuery/ajax. Everything seems to be working flawlessly, except for one of the two picklist fields, Revenue and Number of Employees. For both of these fields, I gave them both a list of ranged options, each representing a different tier for their respective values. After the user selects an option, the page’s Apex Controller would handle all values that were passed using conditional statements. Here are the two picklists:
<tr>
<td>
  <p>Revenue:</p>
</td>
<td>                         
  <select id="revenue_mL" onchange="runSearch();">
   <option value=""></option>
   <option value="tier1_mL">Less than $1 Million</option>
   <option value="tier2_mL">$1 million - $5 million</option>
   <option value="tier3_mL">$5 million - $10 million</option>
   <option value="tier4_mL">$10 million - under $25 million</option>
   <option value="tier5_mL">$25 million - under $50 million</option>
   <option value="tier6_mL">$50 million - under $100 million</option>
   <option value="tier7_mL">$100 million - under $250 million</option>
   <option value="tier8_mL">$250 million - under $500 million</option>
   <option value="tier9_mL">$500 million - under $2.5 billion</option>
   <option value="tier10_mL">Over $2.5 billion</option>
  </select>
</td>
</tr>
<tr>
<td>
  <p>Employees:</p>
</td>
<td>
  <select id="employNum_mL" onchange="runSearch();">
   <option value=""></option>
   <option value="tier1_mL">1-100 Employees</option>
   <option value="tier2_mL">101-500 Employees</option>
   <option value="tier3_mL">501-3,500 Employees</option>
   <option value="tier4_mL">3,500+ Employees</option>
  </select>
</td>
</tr>
Both picklists have the event, “onchange”, attributed to them. So, anytime the picklist is changed to a different value, the runSearch() function is executed. The function, runSearch(), is used to “scoop” all the data from the form using an <apex:actionFunction> tag. Here is the runSearch() javascript function:
function runSearch() {
	var e = document.getElementById("employNum_mL");
	var strUser = e.options[e.selectedIndex].value;
	var f = document.getElementById("revenue_mL");
	var strUser2 = f.options[f.selectedIndex].value;
	mainSearch(
		document.getElementById("title_mL").value,
		document.getElementById("industry_mL").value,
		document.getElementById("company_mL").value,
		strUser2,
		strUser,
		document.getElementById("zipInput").value
	);
}
Here is the actionFunction tag that sends the form parameters to the apex controller:
<apex:actionFunction name="mainSearch" action="{!runMarketingSearch}" rerender="results, resultsLead">
	  <apex:param name="title_mL" value="" />
	  <apex:param name="industry_mL" value="" />
	  <apex:param name="company_mL" value="" />
	  <apex:param name="revenue_mL" value="" />
	  <apex:param name="employNum_mL" value="" />
	  <apex:param name="zipInput" value="" />
  </apex:actionFunction>
Finally, all the data ends up in the Apex Controller. Here is the apex controller:
public with sharing class mLSearchController{
private String soqlCon {get;set;}
private String soqlLead {get;set;}
public List<Contact> contacts {get;set;}
public List<Lead> leadList {get;set;}

public mLSearchController() {
  runQuery();
}
 
public void runQuery() {
  try {
   contacts = Database.query(soqlCon + ' limit 20');
   leadList = Database.query(soqlLead + ' limit 20');
  } catch (Exception e) {
   ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
  }
}

public PageReference runMarketingSearch() {
  String title_mL = Apexpages.currentPage().getParameters().get('title_mL');
  String industry_mL = Apexpages.currentPage().getParameters().get('industry_mL');
  String company_mL = Apexpages.currentPage().getParameters().get('company_mL');
  String revenue_mL = Apexpages.currentPage().getParameters().get('revenue_mL');
  String employNum_mL = Apexpages.currentPage().getParameters().get('employNum_mL');
  String zipcode = Apexpages.currentPage().getParameters().get('zipInput');
  soqlCon = 'select firstname, lastname, Title, Email, Phone, account.Industry, MailingCity, MailingState, MailingPostalCode from Contact where account.name != null';
  soqlLead = 'select firstname, lastname, Title, Phone, Email, Industry, Company, NumberOfEmployees, AnnualRevenue, City, State, PostalCode from Lead where Company != null';
       if (!title_mL.equals('')) {
        soqlCon += ' and Title LIKE \'%' + String.escapeSingleQuotes(title_mL) + '%\'';
        soqlLead += ' and Title LIKE \'%' + String.escapeSingleQuotes(title_mL) + '%\'';
       }
       if (!industry_mL.equals('')) {
        soqlCon += ' and account.Industry LIKE \'%' + String.escapeSingleQuotes(industry_mL) + '%\'';
        soqlLead += ' and Industry LIKE \'%' + String.escapeSingleQuotes(industry_mL) + '%\'';
       }
       if (!zipcode.equals('')) {
        soqlCon += ' and MailingPostalCode LIKE \'' + String.escapeSingleQuotes(zipcode) + '%\'';
        soqlLead += ' and PostalCode LIKE \'' + String.escapeSingleQuotes(zipcode) + '%\'';
       }
       if (!company_mL.equals('')) {
        soqlLead += ' and Company LIKE \'%' + String.escapeSingleQuotes(company_mL) + '%\'';
       }
 
       // ===============================================================================================
  // THIS IS THE REVENUE CONDITIONAL STATEMENTS ALL SEEM TO WORK EXCEPT FOR tier9_mL AND tier10_mL
  // ===============================================================================================
       if (revenue_mL == 'tier1_mL') {
        soqlLead += ' and (AnnualRevenue < 1000000)';
       } else if (revenue_mL == 'tier2_mL') {
        soqlLead += ' and (AnnualRevenue >= 1000000 AND AnnualRevenue < 5000000)';
       } else if (revenue_mL == 'tier3_mL') {
        soqlLead += ' and (AnnualRevenue >= 5000000 AND AnnualRevenue < 10000000)';
       } else if (revenue_mL == 'tier4_mL') {
        soqlLead += ' and (AnnualRevenue >= 10000000 AND AnnualRevenue < 25000000)';
       } else if (revenue_mL == 'tier5_mL') {
        soqlLead += ' and (AnnualRevenue >= 25000000 AND AnnualRevenue < 50000000)';
       } else if (revenue_mL == 'tier6_mL') {
        soqlLead += ' and (AnnualRevenue >= 50000000 AND AnnualRevenue < 100000000)';
       } else if (revenue_mL == 'tier7_mL') {
        soqlLead += ' and (AnnualRevenue >= 100000000 AND AnnualRevenue < 250000000)';
       } else if (revenue_mL == 'tier8_mL') {
        soqlLead += ' and (AnnualRevenue >= 250000000 AND AnnualRevenue < 500000000)';
       } else if (revenue_mL == 'tier9_mL') {
        soqlLead += ' and (AnnualRevenue >= 500000000 AND AnnualRevenue < 2500000000)';
       } else if (revenue_mL == 'tier10_mL') {
        soqlLead += ' and (AnnualRevenue >= 2500000000)';
       } else {
       
       }
      
       if (employNum_mL == 'tier1_mL') {
        soqlLead += ' and (NumberOfEmployees >= 1 AND NumberOfEmployees <= 100)';
       } else if (employNum_mL == 'tier2_mL') {
        soqlLead += ' and (NumberOfEmployees >= 101 AND NumberOfEmployees <= 500)';
       } else if (employNum_mL == 'tier3_mL') {
        soqlLead += ' and (NumberOfEmployees >= 501 AND NumberOfEmployees <= 3500)';
       } else if (employNum_mL == 'tier4_mL') {
        soqlLead += ' and (NumberOfEmployees > 3500)';
       } else {
       
       }
       runQuery();
     return null;
}

}
   The problem that I am faced with is that the Revenue picklist is not functioning correctly, while the Number of Employees picklist is working fine. The apex seems to be appending the correct data from the revenue picklist, except for the last two values, tier9_mL and tier10_mL. Here are two lead use cases that are in my sandbox I do have two Lead cases. One lead has an annual revenue of $10,000,000, and another has $4,000,000,000. For some reason, the query is able to pull the $10 million lead, but not the $4 billion. I was hoping that any more experienced developers might be kind enough to look at my problem and help guide me to where the issue is. Any suggestions, ideas, etc. are greatly appreciated. Thanks so much for your time.
Best Answer chosen by Thomas Fuller
Jim JamJim Jam
I think this is an issue with querying numbers greater than 32-bit in Salesforce. Not sure why, but putting a decimal point and 00 at the end of the number seems to make it work. For tier9_mL and tier10_mL try changing the numbers to 500000000.00 and 2500000000.00

All Answers

Jim JamJim Jam
I think this is an issue with querying numbers greater than 32-bit in Salesforce. Not sure why, but putting a decimal point and 00 at the end of the number seems to make it work. For tier9_mL and tier10_mL try changing the numbers to 500000000.00 and 2500000000.00
This was selected as the best answer
Thomas FullerThomas Fuller
Nice! That really did the trick. Thanks!