+ Start a Discussion
levi6dblevi6db 

Need help creating a picklist from a text field

Hi all,

 

I created a VF page that will allow reps to lookup a custom object called Pricing from another custom object called Products.

 

Everything works, I just need to be able to either auto populate the return value of the Terms field based on a range of numbers gathered from the Terms field input, or create a picklist from the text field.

 

1. Term lookup

a. This field is a text field.

b. the values can range from 1 to 60

1. Can I write code that will read - If the value in the field is less than or equal to 12, return the pricing information that has the Term            value of 12.  If the value in the field is greater than 12 but less than 24, return 24.  Etc.?

2. Term picklist - Can I create a picklist value that they select in the Term field that will only return pricing information that has the    Term value of 12, 24, 36, 48, 60?  Even though the Term field on the Products object is a text field?

 

Here is the Class and VF page.

 

public class PricingSearchController {
 
  // the soql without the order and limit
  private String soql {get;set;}
  
  // the collection of Pricing to display
  public List<AboveNet_Pricing__c> pricings {get;set;}
 
  // the current sort direction. defaults to asc
  public String sortDir {
    get  { if (sortDir == null) {  sortDir = 'asc'; } return sortDir;  }
    set;
  }
 
  // the current field to sort by defaults to Product Name
  public String sortField {
    get  { if (sortField == null) {sortField = 'Name'; } return sortField;  }
    set;
  }
 
  // format the soql for display on the visualforce page
  public String debugSoql {
    get { return soql + ' order by ' + sortField + ' ' + sortDir + ' limit 20'; }
    set;
  }
 
  // init the controller and display some sample data when the page loads
  public PricingSearchController() {
    soql = 'select CurrencyIsoCode, Part_Number__c, Product_Family__c, Name, Product_Description__c, Term_months__c, Qty_Mbps__c, List_NRC__c, List_MRC__c, Sales_Discount_MRC__c, Max_Discount_MRC__c, Notes__c from AboveNet_Pricing__c where Product_Family__c != null';
    runQuery();
  }
 
  // toggles the sorting of query from asc<-->desc
  public void toggleSort() {
    // simply toggle the direction
    sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
    // run the query again
    runQuery();
  }
 
  // runs the actual query
  public void runQuery() {
 
    try {
      pricings = Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' limit 100');
    } catch (Exception e) {
      ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
    }
 
  }
 
  // runs the search with parameters passed via Javascript
  public PageReference runSearch() {
 
    String ProductCurrency = Apexpages.currentPage().getParameters().get('ProductCurrency');
    String ProductFamily = Apexpages.currentPage().getParameters().get('ProductFamily');
    String Name = Apexpages.currentPage().getParameters().get('Name');
    String ProductDescription = Apexpages.currentPage().getParameters().get('ProductDescription');
    String PartNumber = Apexpages.currentPage().getParameters().get('PartNumber');
    String Term = Apexpages.currentPage().getParameters().get('Term');
 
    soql = 'select CurrencyIsoCode, Part_Number__c, Product_Family__c, Name, Product_Description__c, Term_months__c, Qty_Mbps__c, List_NRC__c, List_MRC__c, Sales_Discount_MRC__c, Max_Discount_MRC__c, Notes__c from AboveNet_Pricing__c where Product_Family__c != null';
    if (!ProductCurrency.equals(''))
      //soql += ' and CurrencyIsoCode includes (\''+ProductCurrency+'\')';
      soql += ' and CurrencyIsoCode LIKE \''+String.escapeSingleQuotes(ProductCurrency)+'%\'';
    if (!ProductFamily.equals(''))
      soql += ' and Product_Family__c LIKE \''+String.escapeSingleQuotes(ProductFamily)+'%\'';
    if (!Name.equals(''))
      soql += ' and Name LIKE \''+String.escapeSingleQuotes(Name)+'%\'';
    if (!ProductDescription.equals(''))
      soql += ' and Product_Description__c LIKE \''+String.escapeSingleQuotes(ProductDescription)+'%\''; 
    if (!Term.equals(''))
      soql += ' and Term_months__c LIKE \''+String.escapeSingleQuotes(Term)+'%\'';
    if (!PartNumber.equals(''))
      soql += ' and Part_Number__c LIKE \''+String.escapeSingleQuotes(PartNumber)+'%\''; 
 
    // run the query again
    runQuery();
 
    return null;
  }
 
  // use apex describe to build the picklist values
  public List<String> ProductCurrencies {
    get {
      if (ProductCurrencies == null) {
 
        ProductCurrencies = new List<String>();
        Schema.DescribeFieldResult field = AboveNet_Pricing__c.CurrencyIsoCode.getDescribe();
 
        for (Schema.PicklistEntry f : field.getPicklistValues())
          ProductCurrencies.add(f.getvalue());
 
      }
      return ProductCurrencies;          
    }
    set;
  }
 
}

 

<apex:page controller="PricingSearchController" sidebar="false">
 
   <apex:form >
      <apex:pageMessages id="errors" />
 
      <apex:pageBlock title="AboveNet Pricing Lookup" mode="edit">
         <table width="100%" border="0">
            <tr>  
               <td width="200" valign="top">
                  <apex:pageBlock title="Parameters" mode="edit" id="criteria">
                  
                     <script type="text/javascript">
                        function doSearch() {
                        searchServer(
                        document.getElementById("ProductCurrency").options[document.getElementById("ProductCurrency").selectedIndex].value,
                        document.getElementById("ProductFamily").value,
                        document.getElementById("Name").value,
                        document.getElementById("ProductDescription").value,
                        document.getElementById("Term").value,
                        document.getElementById("PartNumber").value);
                        } 
                     </script> 
 
                     <apex:actionFunction name="searchServer" action="{!runSearch}" rerender="results,debug,errors">
                        <apex:param name="ProductCurrency" value="" />
                        <apex:param name="ProductFamily" value="" />
                        <apex:param name="Name" value="" />
                        <apex:param name="ProductDescription" value="" />
                        <apex:param name="Term" value="" />
                        <apex:param name="PartNumber" value="" />
                     </apex:actionFunction>
 
                     <table cellpadding="2" cellspacing="2">
                        <tr>
                           <td style="font-weight:bold;">Product Currency<br/>
                              <select id="ProductCurrency" onchange="doSearch();">
                                 <option value=""></option>
                                 <apex:repeat value="{!ProductCurrencies}" var="prcs">
                                    <option value="{!prcs}">{!prcs}</option>
                                 </apex:repeat>
                              </select>
                           </td>
                        </tr>
                        <tr>
                           <td style="font-weight:bold;">Product Family<br/>
                              <input type="text" id="ProductFamily" onkeyup="doSearch();"/>
                           </td>
                        </tr>
                        <tr>
                           <td style="font-weight:bold;">Product Name<br/>
                              <input type="text" id="Name" onkeyup="doSearch();"/>
                           </td>
                        </tr>
                        <tr>
                           <td style="font-weight:bold;">Product Description<br/>
                              <input type="text" id="ProductDescription" onkeyup="doSearch();"/>
                           </td>
                        </tr>
                        <tr>
                           <td style="font-weight:bold;">Term<br/>
                              <input type="text" id="Term" onkeyup="doSearch();"/>
                           </td>
                        </tr>
                        <tr>
                           <td style="font-weight:bold;">Part Number<br/>
                              <input type="text" id="PartNumber" onkeyup="doSearch();"/>
                           </td>
                        </tr>
                     </table>
                  </apex:pageBlock>
               </td>
               
                 <td valign="top">
                 
                    <apex:pageBlock mode="edit" id="results">
                     
                       <!-- In our table we are displaying the pPricing records -->
                       <apex:pageBlockTable value="{!pricings}" var="p">
                      
                            <apex:column value="{!p.Part_Number__c}" />
                            <apex:column value="{!p.Product_Family__c}" />
                            <apex:column value="{!p.Name}" />
                            <apex:column value="{!p.Product_Description__c}" />
                            <apex:column value="{!p.Term_months__c}" />
                            <apex:column value="{!p.Qty_Mbps__c}" />
                            <apex:column value="{!p.List_NRC__c}" />
                            <apex:column value="{!p.List_MRC__c}" />
                            <apex:column value="{!p.Sales_Discount_MRC__c}" />
                            <apex:column value="{!p.Max_Discount_MRC__c}" />
                            <apex:column value="{!p.Notes__c}" />
                       </apex:pageBlockTable>
                    </apex:pageBlock>
                 </td>
            </tr>
         </table>
            <apex:pageBlock title="Debug - SOQL" id="debug">
               <apex:outputText value="{!debugSoql}" />           
            </apex:pageBlock>    
      </apex:pageBlock>
   </apex:form>
</apex:page>

 

Thank you for any help in advance.

 

Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

Yes, you should just be able to null protect the processing:

 

String Term = Apexpages.currentPage().getParameters().get('Term');
String anpTerm='';
if ( (null!=Term) && (0!=Term.length()) { Integer intTerm=Integer.valueOf(Term); if (intTerm>=1 && intTerm<=12) { anpTerm='12'; } else if (intTerm>13 && <=24) { anpTerm='24'; } }

 

All Answers

bob_buzzardbob_buzzard

So if I understand correctly, the term field on the AboveNet_Pricing__c is one of 12, 24, 36, 48 or 60, but the user enters a number as free text and you want to bucket 1-12 to return AboveNet_Pricing__c objects with a value of 12, 13-24 to return AboveNet_Pricing__c objects with a value of 24 etc.  

 

That being the case, I'd suggest that you process the term field entered by the user and convert it to the term for the AboveNet_Pricing__c as follows:

 

String Term = Apexpages.currentPage().getParameters().get('Term');
Integer intTerm=Integer.valueOf(Term);
String anpTerm='';
if (intTerm>=1 && intTerm<=12)
{
   anpTerm='12';
}
else if (intTerm>13 && <=24)
{
   anpTerm='24';
}
...

 

Then tweak your SOQL:

if (!anpTerm.equals(''))
      soql += ' and Term_months__c LIKE \''+anpTerm+'%\'';

 

 

levi6dblevi6db

That works great!  The only problem now is, if you do not put a value into the term lookup field first, I receive the following error on the VF page:

 

Invalid integer: 
 
An unexpected error has occurred. Your development organization has been notified.

 

Since the Term field is not mandatory, sometimes the reps will not need to use it.  Can there be a if the Term field is Null type of reference?

 

Thanks again.


bob_buzzardbob_buzzard

Yes, you should just be able to null protect the processing:

 

String Term = Apexpages.currentPage().getParameters().get('Term');
String anpTerm='';
if ( (null!=Term) && (0!=Term.length()) { Integer intTerm=Integer.valueOf(Term); if (intTerm>=1 && intTerm<=12) { anpTerm='12'; } else if (intTerm>13 && <=24) { anpTerm='24'; } }

 

This was selected as the best answer
levi6dblevi6db

Thank you for all your help Bob!!