+ Start a Discussion
shan876shan876 

Move Next Record??PLEASE INDEED OF HELP..

Hi all I have the following code:

How do I move it to next record when the SFD_WEEK_No__c changes...

Basically I have two recordsets of data performing the same thing twice.. but I needed to get totals by week..

This kind of works but it does not sum up the records if the week no is the same.. it just keeps it the same as the first record...

 

 

 

 

for(SFDCFEDFLE__c o:[Select SFD_WEEK_NO__c,SFD_MOFR_NET_AVG__c,True_Draw_MOFR__c,amount__c from SFDCFEDFLE__c where User__c ='00550000000mLBs']) { if(o.SFD_WEEK_NO__c != a.SFD_WEEK_NO__c) total.SFD_MOFR_NET_AVG__c+= o.SFD_MOFR_NET_AVG__c; tot.amount__c += o.True_Draw_MOFR__c;

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
WhyserWhyser
Hmm... what you could do is create a dummy custom field in your SFDCFEDFLE__c custom object that is of the same type of field (ie. Double, Currency, etc) as the True_Draw_MOFR__c field, but not a custom field. And you can use that dummy field to do summary calculations since it won't be constrained like a formula field would be.

All Answers

WhyserWhyser

I'm having trouble trying to figure out what you're asking for... how do you determine whether SFD_WEEK_No__c changes? What are these 'two recordsets performing the same thing twice'? where is the second record set and how is it doing the same thing as the first?

Sorry but your post is kind of confusing.

shan876shan876

Hi thank you for replying back to me... My scenario is as follows:

I have a table that goes as follows:

Week No     User     Draw   NET

   1             me        1         3

   1             me        4         6

   1             me        2        4

   2             me        15      20

and so on...

I know we can't use SUM()...

so when I display it out I need the final result to be:

Week_no   user    draw   net

1              me       7        13

2                me     15       20

 

 

public class fedWithuserCon { public List<AccountTotal> getaccounttotals(){ List<AccountTotal> accounttotals = new List<AccountTotal>(); for(SFDCFEDFLE__c a:[select SFD_WEEK_NO__c,User__r.Name,SFD_MOFR_NET_AVG__c, True_Draw_MOFR__c FROM SFDCFEDFLE__c Where User__c ='00550000000mLBs']) { accounttotals.add(new AccountTotal(a)); } return sortAccountTotals(accounttotals); } public class accountTotal { public SFDCFEDFLE__c account { get; private set; } public SFDCFEDFLE__c total { get; private set; } public SFDCFEDFLE__c tot { get; private set; } public accountTotal(SFDCFEDFLE__c a) { account = a; total = new SFDCFEDFLE__c(SFD_MOFR_NET_AVG__c = 0); tot = new SFDCFEDFLE__c(amount__c=0); for(SFDCFEDFLE__c o:[Select SFD_WEEK_NO__c,SFD_MOFR_NET_AVG__c,True_Draw_MOFR__c,amount__c from SFDCFEDFLE__c where User__c ='00550000000mLBs']) { if(o.SFD_WEEK_NO__c != a.SFD_WEEK_NO__c) total.SFD_MOFR_NET_AVG__c+= o.SFD_MOFR_NET_AVG__c; tot.amount__c += o.True_Draw_MOFR__c; } } } private List<AccountTotal> sortAccountTotals(List<AccountTotal> totals) { List<AccountTotal> returnList = new List<AccountTotal>(); Map<Decimal, List<AccountTotal>> totalMap = new Map<Decimal, List<AccountTotal>>(); for(AccountTotal t:totals) { if(totalMap.get(t.total.SFD_MOFR_NET_AVG__c) == null) { totalMap.put(t.total.SFD_MOFR_NET_AVG__c, new List<AccountTotal>()); } totalMap.get(t.total.SFD_MOFR_NET_AVG__c).add(t); } List<Decimal> keys = new List<Decimal>(totalMap.keySet()); keys.sort(); /* Sort puts things in ascending order so for descending iterate over the keys backwards. */ for(Integer i = (keys.size()-1);i >= 0; i--) { returnList.addAll(totalMap.get(keys.get(i))); } return returnList; } }

 

 

 

 

WhyserWhyser

Thanks for the clarification

If you were trying to summarize the table by grouping it by the Week No, maybe you should approach it like this:

// When obtaining the list, order it by the Week No, that way it will be in the order similar to the example you gave me 

List< SFDCFEDFLE__c > sList = [select SFD_WEEK_NO__c,User__r.Name,SFD_MOFR_NET_AVG__c, True_Draw_MOFR__c FROM SFDCFEDFLE__c Where User__c ='00550000000mLBs' Order By SFD_WEEK_NO__c];

 

// Now create a final list that will contain your summarized information List< SFDCFEDFLE__c > sListSummary = new List< SFDCFEDFLE__c >();

// Instantiate a variable that will contain the current field you are comparing against (in this case it would be WEEK NO)

// I'm assuming Week No is a Double value? Double WeekNoCurrent;

 

// Now iterate through your resultset and summarize the information for ( SFDCFEDFLE__c s : sList ) {

if ( s.SFD_WEEK_NO__c == WeekNoCurrent ) {

// If the week is the same, then add the fields you want to summarize sListSummary[sListSummary.size()-1].SFD_MOFR_NET_AVG__c += s.SFD_MOFR_NET_AVG__c;

sListSummary[sListSummary.size()-1].True_Draw_MOFR__c += s.True_Draw_MOFR__c; } else {

// If the week has changed, put in a new summary object into the summary list, update the WeekNoCurrent sListSummary.add( s ); WeekNoCurrent = s.SFD_WEEK_NO__c; } }

 

// Now sListSummary will contain your summarized data. You can return sListSummary if this code was in a method.

 

 

Message Edited by Whyser on 03-03-2009 08:47 AM
shan876shan876

thank you so much for helping me out... I do have a question one of my fields is a formula so I am trying to get a total of that using your method.. I keep getting this:

Error: Compile Error: Field is not writeable: SFDCFEDFLE__c.True_Draw_MOFR__c at line 29 column 45 

this is my code now:

public class fedWithuserCon { public fedWithuserCon() { } private List<SFDCFEDFLE__c> mytot; public fedWithuserCon(ApexPages.StandardController controller) { //When obtaining the list, order it by the Week No, //that way it will be in the order similar //to the example you gave me List<SFDCFEDFLE__c> sList = [select SFD_WEEK_NO__c,User__r.Name,SFD_MOFR_NET_AVG__c, SFD_FRI_DRAW__c, True_Draw_MOFR__c FROM SFDCFEDFLE__c Where User__c ='00550000000mLBs' Order By SFD_WEEK_NO__c]; // Now create a final list that will contain your summarized information List<SFDCFEDFLE__c> sListSummary = new List<SFDCFEDFLE__c>(); //Instantiate a variable that will contain the current field you are comparing //against (in this case it would be WEEK NO) //I'm assuming Week No is a Double value? Double WeekNoCurrent; //Now iterate through your resultset and summarize the information for(SFDCFEDFLE__c s:sList ) { if ( s.SFD_WEEK_NO__c == WeekNoCurrent ) { // If the week is the same, then add the fields you want to summarize sListSummary[sListSummary.size()-1].SFD_MOFR_NET_AVG__c += s.SFD_MOFR_NET_AVG__c; sListSummary[sListSummary.size()-1].True_Draw_MOFR__c += s.True_Draw_MOFR__c; } else { // If the week has changed, put in a new summary object into the summary list, //update the WeekNoCurrent sListSummary.add( s ); WeekNoCurrent = s.SFD_WEEK_NO__c; } mytot = sListSummary; } } public List<SFDCFEDFLE__c> getfed(){ return mytot; } }

 

Basically the tru draw is a combination of fields within the table... but we place a formula in there to calculate the field automatically for one record...

Thanks

WhyserWhyser
Hmm... what you could do is create a dummy custom field in your SFDCFEDFLE__c custom object that is of the same type of field (ie. Double, Currency, etc) as the True_Draw_MOFR__c field, but not a custom field. And you can use that dummy field to do summary calculations since it won't be constrained like a formula field would be.
This was selected as the best answer
shan876shan876

So I tried doing this:

added a field (True_Draw_MOFR2__c) to my object.. same as the True_draw_mofr__c

then placing it in the if then statement as

sListSummary[sListSummary.size()-1].True_Draw_MOFR2__c += s.True_Draw_MOFR__c;

 

That did not work... Is that correct???

 

WhyserWhyser

Two things to try:

 

1) You may have to query for True_Draw_MOFR2__c, even though it is a null value.

2) You need to instantiate True_Draw_MOFR2__c with an initial value when you create a new SFDCFEDFLE__c object into the sListSummary List, because otherwise you will be adding a null value to a Double, which is illegal I believe:

else { // If the week has changed, put in a new summary object into the summary list, //update the WeekNoCurrent sListSummary.add( s );

sListSummary[sListSummary.size()-1].True_Draw_MOFR2__c = s.True_Draw_MOFR__c; WeekNoCurrent = s.SFD_WEEK_NO__c; }