+ Start a Discussion
go_bear_2001go_bear_2001 

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 

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

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

sfdcfoxsfdcfox

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.

 

This was selected as the best answer
go_bear_2001go_bear_2001

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 

Message Edited by go_bear_2001 on 01-22-2010 10:58 AM
Message Edited by go_bear_2001 on 01-22-2010 11:15 AM
CaptainObviousCaptainObvious

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:

GuyClairboisGuyClairbois

@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