1

The following query works fine. What I need from it is...

  1. To get the total count of the ORG__c field and then for each value, what is the percentage of the total. I.e. if there are 50 records with ORG__c, 20 have a value of 'a', 14 have a value of 'b' and 16 have a value of 'c', I need the percentage of each value vs. the total.

  2. How to display the fields in the query in a HTML table on a VF page grouped by BOG_Membership_Type_New__c then by ORG__c?

  3. How to display #1 on the bottom of the same VF page.

Here is the query...

Select BOG_Membership_Type_New__c, ORG__c, contact__r.Name 
FROM BOG_Membership__c 
WHERE BOG_Membership_Status__c = 'Active' 
AND BOG_Membership_Type_New__c <> '' 
ORDER BY BOG_Membership_Type_New__c 
  • 1
    Have you tried something? What is the problem you are facing? – Sergej Utko Mar 15 '16 at 15:42
  • I am too green to do this but if you get me pointers or simple sample code I would be happy to modify. I need to know how to iterate through the result set and do the math that I explained above. Then I need to know how to show it in a VF page. Like where/how to put the fields in a <td></td> and headers (org__c) in a <th></th>. Once I have an idea I can work on it. – Apex N-u-b Mar 15 '16 at 15:56
  • I'm assuming 'a', 'b', and 'c' are just examples and in reality there could be an unknown number of values? – Nick Cook Mar 16 '16 at 0:49
  • for step 1 - use an aggregate query to get counts of org__c by value. Then, you'll need some inner classes (aka wrapper classes) so you can use a VF iterating component (pageBlockTable, dataTable, or repeat) to display – cropredy Mar 16 '16 at 1:24
2

A few quick notes before the sample code:

I'm still fairly new to VF myself, so I'm not going to attempt to help you with that portion of it. What I can say is that from my understanding, you can't do everything you're trying to do in that query, nor can you do it in the VF page itself. You'll need to do it in a controller or a controller extension (depending on your situation and what else you're trying to accomplish in this project) that the VF page uses.

Lastly, when you say "..total count of Org__c" are you referring to the total number of BOG_Membership__c records, since unless you're using Record Types that have different fieldsets, all of your records will have that field, or are you referring to BOG_Membership__c records that have a value versus not having a value at all? For the time being, I'm going to assume you mean all BOG_Membership__c records, and use that list size.

Also, I don't know what type of field BOG_Membership_Type_New__c is, but it sounds like a Boolean (checkbox), so I'm going to treat it as such. The same goes for Org__c which sounds like an Id even though your examples show a string.

The code:

List<BOG_Membership__c> bogQueryList = *your query here*;

Map<Boolean, List<BOG_Membership__c>> membershipByStatus = new Map<Boolean, List<BOG_Membership__c>>();
Map<Id, List<BOG_Membership__c>> membershipByOrg = new Map<Id, List<BOG_Membership__c>>{'a' => new List<BOG_Membership__c>{}, 'b' => new List<BOG_Membership__c>{}, 'c' => List<Bog_Membership__c>{}};

Decimal a = 0.00;
Decimal b = 0.00;
Decimal c = 0.00;

for(BOG_Membership__c record : bogQueryList){
   if(record.Org__c == 'a'){membershipByOrg.get('a').add(record);}
   else if(record.Org__c == 'b'){membershipByOrg.get('b').add(record);}
   else if(record.Org__c == 'c'){membershipByOrg.get('c').add(record);}

   if(membershipByStatus.containsKey(BOG_Membership_Type_New__c)){membershipByStatus.get(BOG_Membership_Type_New__c).add(record);}
   else{membershipByStatus.put(BOG_Membership_Type_New__c, new List<BOG_Membership__c>{record};} 
}

if(bogQueryList.size() > 0){
   a = membershipByOrg.get('a').Values().size() / BOG_Membership__c.size();
   b = membershipByOrg.get('b').Values().size()/ BOG_Membership__c.size();
   c = membershipByOrg.get('c').Values().size()/ BOG_Membership__c.size();
}

How you'll want to list the variables with additional getter/setter methods is a different story. You'll want to familiarize yourself with that terminology/their usage to know what's appropriate for your use case.

Regardless, by putting the BOG_Membership__c objects in a map grouped the way you want them, you can use a repeat tag to build a table from the values that accurately reflects that grouping.

More on using Maps in VF:

https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_dynamic_vf_maps_lists.htm

EDIT:

I didn't clarify this originally, since I wasn't sure of how you'd want to present a "percent" to the user, but if you wanted to display the value as text formatted like a percent (i.e. "20%" versus "0.20"), straight from variable to page using something like outputText, you'd want to make this slight adjustment:

String a = '0%';
String b = '0%';
String c = '0%';

if(bogQueryList.size() > 0){
   a = String.ValueOf((membershipByOrg.get('a').Values().size() / BOG_Membership__c.size()) * 100) + '%';
   b = String.ValueOf((membershipByOrg.get('b').Values().size() / BOG_Membership__c.size()) * 100) + '%';
   c = String.ValueOf((membershipByOrg.get('c').Values().size() / BOG_Membership__c.size()) * 100) + '%';
}
| improve this answer | |
  • Many thanks KB145. Now I have questions. Remember I do know much but I like this stuff. First, I get 'unexpected token List' error on this line, why? Map<Id, List<BOG_Membership__c>> membershipByOrg = new Map<Id, List<BOG_Membership__c>>{'a' => new List<BOG_Membership__c>{}, 'b' => new List<BOG_Membership__c>{}, 'c' => List<Bog_Membership__c>{}}; – Apex N-u-b Mar 16 '16 at 9:13
  • Also, in the For statement you write record.Org__c == 'a'. I am not sure what 'a' is supposed to be. What I need is to somehow, go through the result set and get a list,map,set of BOG_Membership_Type_New__c and ORG__c and then do my sorting them into arrays. I am not sure how this is being done. This is very helpful as it is helping figure what I need to do. – Apex N-u-b Mar 16 '16 at 9:17
  • Regarding the membershipByOrg map... When you mentioned 'a', 'b', and 'c' in your original post, are those the actual values you're looking for, or just examples of values to make your question easier to read? The map I wrote in the code was written with the assumption that you would replace 'a', 'b', and 'c' with ids that you needed. If you really are looking for 'a', 'b', and 'c', and not ids, then you should swap out everywhere "Id" is listed in that map to instead say "String". Also, to clarify, if Org__c is a lookup, it's referencing an Id, even if you see a name on the frontend – KB145 Mar 16 '16 at 16:30
  • On a side note - It's bad practice to statically reference any Id because it can change between environments. So if you have to use some statically declared value, you're better off referencing a Name over an Id. E.g. if(Org__r.Name == 'Some Name'){...} is better than... if(Org__c == '001234567891011' || Org__c == '001234567891011123'){...} – KB145 Mar 16 '16 at 16:36
  • Re-reading your second comment, I think I'm understanding where the miscommunication occurred. Are you saying you don't know what the values assigned to Org__c are, and you want to make a map of BOG_Membership__c records organized by ALL possible Org__c values? Not a map of BOG_Membership__c records organized by three specific Org__c values that you're looking for? – KB145 Mar 16 '16 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.