Apex Code Development You need to sign in to do that
Don't have an account?
Christopher_J New to SOQL and Apex
Hi All,
I'm really enjoying the Salesforce platform but am very new to coding and have run into a few problems.
I have a custom object account_activity__c, that has several checkboxes on it that will be used to code interactions with our customers. It also has a category field as well as a lookup relationship to another custom object (Project_Manager) where the interactions (account_activities) will be "housed".
What I'm trying to do is create a visualforce page that will display a count of the "true" checkboxes for each question in each account_activity that is connected to the Project_Manager, grouped by category. I'm stuck on multiple fronts.
1. I'm trying to load this in a tab on the visualforce page I created to replace the detail view of the account_activity object. I have a custom controller made and a visualforce page I'm loading in the tabbed interface. Is there a simple way to pass the ID of the current record into Apex so I can use it in the SOQL query to filter account_activity by the lookup relationship ID?
My query right now is this and I need to somehow insert the ID of the page I'm loading in the where.
Select category__c, Count(id) from Account_Activity__c Where Project_Manager__r.Id='a18c0000000LU8V' and question1__c=true group by rollup
(category__c)
2. This query essentially gives me one row of data for my end product but it won't return a zero if a null is returned for any given category. Is there a way to overcome this?
I'm sure there are a million better ways to do what I'm trying to do, but I'm so new to coding I'm just trying to get it done any way I can.
Thanks!
Chris
Chris,
Welcome. You can pass in the object id into your VF page on the URL like so:
/apex/myVFPage?id=a18c0000000LU8V
Within your controller,
you'll need a statement like this (you can put this in your constructor)
recId = ApexPages.currentPage().getParameters().get('id');
Your SOQL can then use that id:
Select category__c, Count(id) from Account_Activity__c Where Project_Manager__r.Id=:recId and question1__c=true group by rollup
(category__c)
Best,
Ram
Ram, thank you very much for the quick reply! This worked after some tweaking. I noticed that I could insert that code directly into the where clause but it still didn't work. I realized that was because I was loading the page in an iframe so it couldn't see the id of the mian url. Rather than sort out how to grab that (I saw it's possible in javascript) I decided to create a controller extension and load the visualforce code wrapped in the tab tags. The controller ended up looking like this and it works.
public class retrieveAAcountsExtension {
ApexPages.StandardController stdCtrl;
public retrieveAAcountsExtension(ApexPages.StandardController std)
{
stdCtrl=std;
}
public List <account_activity__c> getNums() {
return [select category__c, Question1__c
from account_activity__c
Where Project_Manager__r.Id=:ApexPages.currentPage().getParameters().get('Id')];
}
}
So now the question is how do I use SOQL to correctly count the null as zero when there are no "true" records for a particular category.
Chris,
What you'll need to do is to have a list of all categories that you pull from somewhere - I'm assuming you have an object that stores these categories (and possibly as related to this particular PM).
Something like
Select Id, Name from Category__c where Id not in (Select category__c from Account_Activity__c Where Project_Manager__r.Id=:recId and question1__c=true)
Then you can take these and show these as having a zero count.
Ram
Thanks again Ram. Unfortunately I don't have that list anywhere, maybe I could create it? I thought I had this solved through a rudimentary method of doing a SOQL for each cell in the table I'm creating but it breaks when it hits 100 queries.
I was thinking I could possibly pull back all of the records for account_activity__c that associate to the project_manager__r.id and then in the controller do the counting, but I don't see a way to count in a list (assuming I'd be pulling back a list). Is it possible to do this type of counting post SOQL query in Apex?
Thanks for the help. I thought I was done with this, and then hit that limit.
Chris,
No problems. You can certainly do the counting/rollup in the apex code but you end up using a lot more script statements without any advantage. Is Category__c a picklist in the table you're trying to summarize? If it is, you can just build a list or Map with all possible categories. When you run through your results from the aggregate query that you already have, you can look for each category row you get and mark it off against the map - the ones that weren't marked off would be the ones with a zero count. If you have Category__c as a separate custom object (rather than just items in a picklist), you would want to fetch the items in it - assuming it is not a very high number (say less than a few hundred) and build your Map. You should do this once (should not have to do the SOQL for this for every row - just once). Let me know if that helps.
Ram
That's not your problem though, it's just how new I am to coding in general. I'll hit the books and figure it out.
Thanks again for all your help, it's appreciated!
Chris
Hey Chris,
I guess I did leave it a little fuzzy with checking off items :) Here's the main idea: If you are doing this against one PM at any given time, it would just need a Map<String, Integer> with the key being the category. You can initialize this Map with a zero count against each category. When you iterate through your resultset, you can put the actual count against the specific category. The ones that did not get a count would have a zero already. You could then build a list out of this map to have all categories.
So: Initial state:
C1 - 0
C2 - 0
C3 - 0
C4 - 0
Final State:
C1 -0
C2 - 5
C3 - 10
C4 - 0
If you do need this to work for multiple PM selections all at the same time, it does become a little more complicated. You'll probably need a Map<String, Map<String, Integer>> - the inner map would be the PM id to count for that category. But I guess we'll cross that bridge if needed.
Best,
Ram
Hi Ram,
I feel like I'm getting closer.
So here is how I'm initializing the map.
//Create the Map
Map<String,Integer> cntMap = new Map<String,Integer>{'Site Visit' => 0, 'Call' => 0, 'Email' => 0, 'Data Analysis' => 0, 'Planning' => 0, 'Check In' => 0};{
Here's where I'm getting confused.
My SOQL Query looks like this.
[Select category__c, Count(id) from Account_Activity__c Where Question1=true and Project_Manager__r.Id=:ApexPages.currentPage().getParameters().get('Id')group by category__c]){
This returns two columns Category__C and Count(id) based on whatever the Project Manager ID of the current page is. It works fine.
This is where I think I'm going off track. I know am trying to use this for statement using the query.
for(Account_Activity__c aa : [Select category__c, Count(id) from Account_Activity__c Where Question1=true and Project_Manager__r.Id=:ApexPages.currentPage().getParameters().get('Id')group by category__c]){
Is that not the way to do it?
After that I have the if to check and see if the category is present on the map and if it is to update the map.
if (cntMap.get(aa.Category__c) == null) {
//If null add cat to map
cntMap.put(aa.Category__c, Count(id));
The error I get with this approach is that Count(id) isn't a valid variable.
If it did map correctly the next issue would be getting the values onto the page. I thought this would be how that's done.
list<integer> cnts = cntMap.values();
Hopefully I'm not too far off. Thanks for the tutelage!
Hey Chris,
Yes - getting closer.
Use an alias for Count(Id) such as IdCount (you can choose what works for you) and then you can refer to it by the alias
for(Account_Activity__c aa : [Select category__c, Count(id) IdCount from Account_Activity__c Where Question1=true and Project_Manager__r.Id=:ApexPages.currentPage().get
Parameters().get('Id')group by category__c])
Then you just load the counts into the Map - no need to check for existence is you have loaded all categories into the Map.
for(Account_Activity__c aa : [Select category__c, Count(id) IdCount from Account_Activity__c Where Question1=true and Project_Manager__r.Id=:ApexPages.currentPage().get
Parameters().get('Id')group by category__c]) {
// update the count that was initialized to zero earlier
cntMap.put(aa.Category__c, aa.IdCount);
}
Yes - re: retrieving the counts, you have different ways to do it - either iterate thru' the keys and get the count for eack key or just get the values like you are doing. You could build a wrapper object out of your category name and count so that your VF page just deals with a list of this wrapper object.
List<CategoryWrapper> categoryWrapperList = new List<CategoryWrapper>();
for (String key : cntMap.keySet()) {
CategoryWrapper cw = new CategoryWrapper();
cw.categoryName = key;
cw.categoryCount = cntMap.get(key);
categoryWrapperList.add(cw);
}
// wrapper class inside controller
public class CategoryWrapper {
public String categoryName{ get; set; }
public Integer categoryCount{ get; set; }
}
} //end of controller class
That should get you even closer.
Best,
Ram
I'm wondering if I do something different because the count isn't an actual field but the result of the count? Thanks again!!
ok - since we're using aggregates in the SOQL, we will need to approach this slightly differently: modify that for loop as below
for(AggregateResult aa : [Select category__c, Count(id) IdCount from Account_Activity__c
Where Question1=true and
Project_Manager__r.Id=:ApexPages.currentPage().getParameters().get('Id') group by category__c]) {
// update the count that was initialized to zero earlier
String categoryName = String.valueOf(aa.get('category__c'));
Integer categoryCount = Integer.valueOf(aa.get('IdCount'));
cntMap.put(categoryName, categoryCount);
}
public List <integer> getcnts() {
return cntMap.values();}
and then <apex:repeat value="{!cnts}">
<apex:outputText value="{!cnts}" /><br/>
</apex:repeat>
which lists 6 out of order lists but the values look right. What I'd like to be able to do for lack of a better term is simply define each of these values into a variable that I can refer to on the visualforce page. That would allow me to display and sum the data albeit not in an elegant way.
Is there a way out of the map to simply declare a variable something like
Integer callcnt = (the value for the call key in the map?)
That wrapper class looks awesome but is way over my head,lol.
Thanks for your patience.
Chris,
A couple of things you can do to order this:
// define your list of categories
List<String> categoryList = new List<String>{'Site Visit', 'Call', 'Email', 'Data Analysis, 'Planning' , 'Check In'};
// and then build your map from the list
Map<String,Integer> cntMap = new Map<String,Integer>();
// and initialize it to zeros
for (String category : categoryList) {
cntMap.put(category, 0);
}
Then at the end:
List<CategoryWrapper> categoryWrapperList = new List<CategoryWrapper>();
// build the wrapper list in the correct order
for (String category : categoryList) {
CategoryWrapper cw = new CategoryWrapper();
cw.categoryName = category;
cw.categoryCount = cntMap.get(category);
cw.isChecked = false;
categoryWrapperList.add(cw);
}
ok - so re: summing up etc, what you could do is add a checkbox for each category and have a boolean in the wrapper class called isChecked.
// wrapper class inside controller
public class CategoryWrapper {
public String categoryName{ get; set; }
public Integer categoryCount{ get; set; }
public boolean isChecked{ get; set; }
}
Then you can have the ability to check certain categories and do totals on just the selected ones or other types of aggregations from the VF page using specific actions. So as an example:
public PageReference totalSelected() {
// make sure to declare total as a public variable at the top
total = 0;
for (CategoryWrapper cw : categoryWrapperList) {
if (cw.isChecked == true) {
total += cw.categoryCount;
}
}
return null;
}
Hopefully that makes sense.
Ram
Of course, you can always do something like this:
Integer callcnt = cntMap.get('Call');
Works ok with a hand ful of categories - but doing the checkboxes works in a more generic way and would be more flexible.
Ram
The beauty is I now have a solid framework to make this code more elegant and expand it which I will probably be asked to do once the finished product catches on.
Seriously, can't thank you enough :)
Chris
That's great! Glad to hear it.
Best!
Ram