You need to sign in to do that
Don't have an account?
Display data from multiple objects as a grid
Hi,
I have a unique requirement and could not figure out ways to get around to display and edit data:
-We have Opportunity Products and then we created another custom object called AgreementOrder. This AgreementOrder object does have a lookup reference to the Opportunity Product standard object. The AgreementOrder is used to track agreement order by month. So basically this AgreementOrder object has 3 fields:
1) ProductID(lookup to Opty Product)
2) Month
3) Quantity
-So for ex.:
------------------
***Product 1: Laptop_ABC
*There are 3 AgreementOrder records:
-AgreementOrder record 1: Month: Jan-2010, Quantity=10
-AgreementOrder record 2: Month: Feb-2010, Quantity=15
-AgreementOrder record 3: Month: Mar-2010, Quantity=20
***Product 1: Laptop_XYZ
*There are 2 AgreementOrder records:
-AgreementOrder record 1: Month: Mar-2010, Quantity=3
-AgreementOrder record 2: Month: Apr-2010, Quantity=50
-----------------
What I want is to display on VF something like a summary of Quantity from AgreementOrder:
ProductID Jan-2010 Feb-2010 Mar-2010 April-2010
Laptop_ABC 10 15 20 -
Laptop_XYZ - - - 50
I could not figure out a way to iterate over all AgreementOrder records since each AgreementOrder record gives me the Quantity for only a single month. In other words, iterate over all AgreementOrder records and display one-by-one is not going to work.
I also thought about building a map where I map ProductID to a list of Quantity values, but then I'm not sure how to iterate over this map, and it seems complicated.
Anyone experience anything similar. Any input is highly appreciated !
-Klein
Complicated hardly does it justice. It's a very convoluted process given the current design of Visualforce (particularly, the lack of support for Map objects in value attributes). Best I can come up with is this:
Controller:
public class gridController {
public gridController() {
grid = new list<gridrow>();
loadItems();
}
// stores one row of data-- a product name and a list of cells.
public class gridrow {
public gridrow(string productname) { cells = new list<gridcell>(); name = productname; }
public list<gridcell> cells { get; set; }
public string name { get; set; }
}
// stores the quantity of a single cell
public class gridcell {
public gridcell(integer qty) { quantity = qty; }
public integer quantity { get; set; }
}
// visualforce needs a list in order to operate
public list<gridrow> grid { get; set; }
// Allows us to render a dynamic number of columns.
private date startDate, endDate;
// Get a list of month names, i.e. "July 2010", "August 2010", etc.
public List<String> getMonths() {
list<string> months = new list<string>();
if(startdate==null || enddate==null)
return null;
for(date currentDate = startdate; currentDate <= endDate; currentdate = currentdate.addmonths(1))
months.add(datetime.newinstance(currentdate.year(),currentdate.month(),1).format('MMMM yyyy'));
return months;
}
// load the items into memory.
public void loadItems() {
// organize by product id and month
map<id,map<date,integer>> dateMap = new map<id,map<date,integer>>();
map<id,string> nameMap = new map<id,string>();
// loop through all the items, organizing them using maps.
for(object__c o:[select id,date__c,product__c,product__r.name,quantity__c from object__c]) {
if(dateMap.get(o.product__c)==null)
dateMap.put(o.product__c,new map<date,integer>());
if(dateMap.get(o.product__c).get(o.date__c.tostartofmonth())==null)
datemap.get(o.product__c).put(o.date__c.tostartofmonth(),o.quantity__c);
else
datemap.get(o.product__c).put(o.date__c.tostartofmonth(),datemap.get(o.product__c).get(o.date__c.tostartofmonth())+o.quantity__c);
if(namemap.get(o.product__c)==null)
namemap.put(o.product__c,o.product__r.name);
if(startdate==null || o.date__c.tostartofmonth()<startdate)
startdate = o.date__c.tostartofmonth();
if(enddate==null || o.date__c.toendofmonth()>enddate)
enddate = o.date__c.tostartofmonth();
}
grid.clear();
// at this point, if we have any records, we create the list that visualforce needs.
if(startdate==null || enddate==null)
}return;
for(id prodid:datemap.keyset()) {
grid.add(new gridrow(namemap.get(prodid)));
for(date currentdate = startdate; currentdate <= enddate; currentdate = currentdate.addmonths(1))
if(datemap.get(prodid).get(currentdate)==null)
grid[grid.size()-1].cells.add(new gridcell(0));
else
grid[grid.size()-1].cells.add(new gridcell(datemap.get(prodid).get(currentdate)));
}
}
Page snippet:
<table>
<tr>
<td></td>
<apex:repeat value="{!months}" var="month">
<th>{!month}</th>
</apex:repeat>
</tr>
<apex:repeat value="{!grid}" var="row">
<tr>
<th>{!row.name}</th>
<apex:repeat value="{!row.cells}" var="cell">
<td>{!cell.quantity}</td>
</apex:repeat>
</tr>
</apex:repeat>
</table>
Complex? Yes. I expect it will probably take you at least as long to understand it as it took me to create it-- the logic is actually quite simple, but is made complex by the limits of visualforce. Note that if you wanted to sort the products in a particular order, that would require yet more work.
Best of luck to you on your endeavors. Please feel free to message me if you need further assistance.
All Answers
Complicated hardly does it justice. It's a very convoluted process given the current design of Visualforce (particularly, the lack of support for Map objects in value attributes). Best I can come up with is this:
Controller:
public class gridController {
public gridController() {
grid = new list<gridrow>();
loadItems();
}
// stores one row of data-- a product name and a list of cells.
public class gridrow {
public gridrow(string productname) { cells = new list<gridcell>(); name = productname; }
public list<gridcell> cells { get; set; }
public string name { get; set; }
}
// stores the quantity of a single cell
public class gridcell {
public gridcell(integer qty) { quantity = qty; }
public integer quantity { get; set; }
}
// visualforce needs a list in order to operate
public list<gridrow> grid { get; set; }
// Allows us to render a dynamic number of columns.
private date startDate, endDate;
// Get a list of month names, i.e. "July 2010", "August 2010", etc.
public List<String> getMonths() {
list<string> months = new list<string>();
if(startdate==null || enddate==null)
return null;
for(date currentDate = startdate; currentDate <= endDate; currentdate = currentdate.addmonths(1))
months.add(datetime.newinstance(currentdate.year(),currentdate.month(),1).format('MMMM yyyy'));
return months;
}
// load the items into memory.
public void loadItems() {
// organize by product id and month
map<id,map<date,integer>> dateMap = new map<id,map<date,integer>>();
map<id,string> nameMap = new map<id,string>();
// loop through all the items, organizing them using maps.
for(object__c o:[select id,date__c,product__c,product__r.name,quantity__c from object__c]) {
if(dateMap.get(o.product__c)==null)
dateMap.put(o.product__c,new map<date,integer>());
if(dateMap.get(o.product__c).get(o.date__c.tostartofmonth())==null)
datemap.get(o.product__c).put(o.date__c.tostartofmonth(),o.quantity__c);
else
datemap.get(o.product__c).put(o.date__c.tostartofmonth(),datemap.get(o.product__c).get(o.date__c.tostartofmonth())+o.quantity__c);
if(namemap.get(o.product__c)==null)
namemap.put(o.product__c,o.product__r.name);
if(startdate==null || o.date__c.tostartofmonth()<startdate)
startdate = o.date__c.tostartofmonth();
if(enddate==null || o.date__c.toendofmonth()>enddate)
enddate = o.date__c.tostartofmonth();
}
grid.clear();
// at this point, if we have any records, we create the list that visualforce needs.
if(startdate==null || enddate==null)
}return;
for(id prodid:datemap.keyset()) {
grid.add(new gridrow(namemap.get(prodid)));
for(date currentdate = startdate; currentdate <= enddate; currentdate = currentdate.addmonths(1))
if(datemap.get(prodid).get(currentdate)==null)
grid[grid.size()-1].cells.add(new gridcell(0));
else
grid[grid.size()-1].cells.add(new gridcell(datemap.get(prodid).get(currentdate)));
}
}
Page snippet:
<table>
<tr>
<td></td>
<apex:repeat value="{!months}" var="month">
<th>{!month}</th>
</apex:repeat>
</tr>
<apex:repeat value="{!grid}" var="row">
<tr>
<th>{!row.name}</th>
<apex:repeat value="{!row.cells}" var="cell">
<td>{!cell.quantity}</td>
</apex:repeat>
</tr>
</apex:repeat>
</table>
Complex? Yes. I expect it will probably take you at least as long to understand it as it took me to create it-- the logic is actually quite simple, but is made complex by the limits of visualforce. Note that if you wanted to sort the products in a particular order, that would require yet more work.
Best of luck to you on your endeavors. Please feel free to message me if you need further assistance.
sfdcfox,
Thanks a lot for your time in providing a valuable and detail response. Yes, the nature of the problem is not extremely difficult. It's some time the challenging of finding a solution that works with the current framework and its limitation. I also thought about constructing map of map, but then I struggle in the rendering part on VF as I just learn up Visualforce recently. Your post is very helpful !
Taking one step further, if we have to make this VF page in such a way it also allows users to update Quantity values and save those values back to the AgreementOrder records. If we go w/ your solution as I would do, I guess I probably have to keeping track for any cell in the grid that got changed and then make an update call back to the AgreementOrder object. Do you have any thought on this?
thanks,
Klein
This is great! Thanks for the hard work sfdcfox! I was able to quickly implement this in a new project i'm working on.
Is toendofmonth() a custom date method?
Now if I could just figure out how to get even/odd row attributes to style the table... :smileyvery-happy:
@sfdcfox - thank you for sharing. Extremely useful piece of code.
For who's interest: I made the resulting table look like a salesforce table by adding the following style tags to my table components. Not sure if I'm following the conventions here, but it works fine for me.
<table class="list" border="0" cellpadding="0" cellspacing="0" width="100%">
<tr class="headerRow">
<tr class="dataCell">
Guy