+ Start a Discussion
Zaheer KhanZaheer Khan 

How to do conditional selection of SOQL field in Apex class or Visualforce page?

Hi,

I would like to get a data from one of the two fields based on the value of third field. So it is conditional output.

Three Fields:
1. Currency - currency values could be either “US” or “CAD”.
2. NetUS – this field holds total sale in US dollars.
3. NetCAD – this field holds total sale in Canadian dollars.
 
IF Currency = “CAD” THEN
Net_Sale_in_CAD
ELSE
Net_Sale_in_US
 
I want to publish the results in Visualforce Page, based on currency either “NetUS” or “NetCDN” but not both. 
 
What would be the best way to accomplish this? I would appreciate, if you include the code. Please see my Apex class and Visualforce page below. Thanks.
 
Apex Class
 
public with sharing class SaleSum {
 
    public Summary[] Summaries { get; set; }
 
 public SaleSum() {
        AggregateResult[] results = [
        Select Brand__c,
CALENDAR_MONTH(Invoice__r.Invoice_Date__c) InvMn,
CALENDAR_YEAR(Invoice__r.Invoice_Date__c) InvYr,
            SUM(Net_Amount_US__c) NetUS,
            SUM(Net_Amount_CDN__c) NetCDN,             
            Invoice__r.Account__r.Currency_Id__c,     
            Invoice__r.Account__r.Customer_Id__c
        From Invoice_Line__c
        Group By Brand__c,
            CALENDAR_MONTH(Invoice__r.Invoice_Date__c), 
            CALENDAR_YEAR(Invoice__r.Invoice_Date__c),
            Invoice__r.Account__r.Currency_Id__c,
            Invoice__r.Account__r.Customer_Id__c
        ];
       
        Summaries = new List<Summary>();
        for (AggregateResult gr : results) {
            Summaries.add(new Summary(gr));
        }
    }
 
    public class Summary {
        public Decimal NetUS { get; private set; }
        public Decimal NetCDN { get; private set; }
        public String Brand { get; private set; }
        public Integer InvMn { get; private set; }
        public integer InvYr { get; private set; }
        public String Curren { get; private set; }
        public String Customer { get; private set; }
       
   public Summary(AggregateResult gr) {
            NetUS = (Decimal) gr.get('NetUS');
            NetCDN = (Decimal) gr.get('NetCDN');
            Brand = (String) gr.get('Brand__c');
            InvMn = (Integer) gr.get('InvMn');
            InvYr = (Integer) gr.get('InvYr');
            Currency = (String) gr.get('Currency_Id__c');
            Customer = (String) gr.get('Customer_Id__c');
        }
    }
 
}
             
 
Visualforce Page
 
<apex:page controller="SaleSum">
<apex:form >
<apex:repeat value="{!Summaries}" var="summary">
{!summary.Customer}
{!summary.Brand}
{!summary.Series}
{!summary.Currency}
{!summary.InvMn}
{!summary.InvYr}
{!summary.NetUS}
{!summary.NetCDN}
<br/>
</apex:repeat>
</apex:form>
</apex:page>
 
JeffreyStevensJeffreyStevens
I think you could do it by doing two soql's.  One for US and one for CA. 
IshwarIshwar
You can use 'rendered' attribute in VF. Using one SOQL looks fine. Update you VF as below:
<apex:page controller="SaleSum">
<apex:form >
<apex:repeat value="{!Summaries}" var="summary">
{!summary.Customer}
{!summary.Brand}
{!summary.Series}
{!summary.Currency}
{!summary.InvMn}
{!summary.InvYr}

<apex:outputText value="{!summary.NetUS}" rendered="{!IF(summary.Currency='US',true,false)}"/>

<apex:outputText value="{!summary.NetCDN}" rendered="{!IF(summary.Currency='CAD',true,false)}"/>

<br/>
</apex:repeat>
</apex:form>
</apex:page>

 
JeffreyStevensJeffreyStevens
Ya - but I think his problem is that in the controller - it's SUMming the US Dollars & CA dollars for each record.  That's why I think he will need two SOQL's to get it done - if he's using a aggregrateResult.  The other option would be to get a detail list, and loop through each record, with logic in the loop to add two different totals.  I think the aggregrateResults is a better way to do it.
Zaheer KhanZaheer Khan
Thanks for all the replies.

Ishwar, your help brings me very close to the output i am looking for. End result of this visualforce page should look like following:

Customer         Brand          Series     Year    January   February  March   April      May      June                     
Best Solution    Apple          S5          2015    $20,500   $15,300   12,200  16,000   23,700   28,900
Best Solution    Samsung    A6          2015    $21,000   $23,400   15,440   19,700  25,300   32,430
Best Solution    Apple          S5          2016    $18,200   $11,100   18,900  11,000   24,600   

With render attribute it shows the sale based on customer region (US or Canada), which is good. My question is how should i layout fields to get this kind of end results, as each month should show only only one net sales based on customer region. I will appreciate your help, if you can include code to show to how to lay out the fields to get the above output. Thanks.