You need to sign in to do that
Don't have an account?
Zaheer 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.
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');
}
}
}
<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>
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>
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.