Apex Code Development You need to sign in to do that
Don't have an account?
levi6db 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.
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
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+'%\'';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:
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.
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'; } }
Thank you for all your help Bob!!