Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Total of values in fields. (add different values only)


louattara
 Share

Recommended Posts

Hi there,

 

I'm trying make a total of a field called (aggregated cost). The Aggregated cost of a certain record might be the same for several others; I don't want to add all of them I only want one instance of that aggregated cost in a summary field like Total of aggregated cost or some sort of calculation. This might be straight forward but I can't figure it out. So basically.

 

Record1 Aggregated cost= 870

Record2 Aggregated cost= 200

Record3 Aggregated cost= 870

Record4 Aggregated cost= 870

Record5 Aggregated cost= 870

Record6 Aggregated cost= 300

Record7 Aggregated cost= 390

 

For example here, I don't want the sum of all 4 instances of 870. just one.

What is the easiest way to do this? better yet, what is a way to do this?

 

Thanks!

Link to comment
Share on other sites

Well, first time through I didn't understand your question, so I just wrote a rather large post trying to figure out what it meant, and after having written it all, it finally clicked what you were after, so here goes post #2:

 

First, I've attached an example file that you can download and see exactly how my explanation works. Good thing you are a subscribing member so you can get it, well done!

 

Right, So you want to sum this field, yet you don't want to sum all occurrences of the same number, just one of them. What if........ there was another field you summed instead. However this field will either contain the Aggregated Cost, or it won't in certain situations.

 

The situation is this: If there are more than 1 record with the same aggregated cost, then only one of those records will actually contain the value in this new field, the rest will not contain it. Therefore, when you create a summary field to total the records, it's going to total only one occurrence of the number.

 

To put it another way, your original example was:

 

870 +

200 +

870 +

870 +

870 +

300 +

390

 

If we were to use this new field, it would look like this:

 

870 +

200 +

0 +

0 +

0 +

300 +

390

 

Summing it this way gives you the result you want. So the next question is, how do you create this new calculated field so that only one of the records which has a duplicate aggregated cost actually contain that cost, and the rest contain zero?

 

To do this, you'll of course need to create this calculated field. The other CRUCIAL thing your table will need is a unique identifer field (which i'll call zRecord ID). Typically it's a primary key / serial number - something for which each record has a unique value.

 

Lastly you are going to need to create a new table occurrence for the table in question.

 

Using the example from the file - the table is called "Aggregated Costs". So you will need to have a new table occurrence which I'll call "Aggregated Cost Unique Check". Now establish a relationship between the two, the predicate is :

 

Aggregated Cost = Aggregated Cost

 

By establishing this relationship, you have created a link between a record, and all other records (including itself) which have the same aggregated cost.

 

Now back to your new calculation - "Aggregated cost Unique" , it's going to look like this:

 

if ( zRecord ID = Aggregated Cost Unique Check::zRecord ID ; Aggregated Cost ; "" )

 

For each record the calculation evalutes. Each record compares its own record ID to the first related records ID thru the relationship. However only ONE of these records is actually going to match that first records ID. For the one that does, we set our calculation to the aggregated cost, for the rest, they won't match on record ID, so they get set to blank.

 

End result, is that only one of the records gets the value. Now all you need is a summary field to sum up this new calculation and you're done.

 

One thing to note about this method is that you are summarising an unstored calculation (since the calculation is referencing a related field, it cannot be indexed). Depending on the size of your foundcount, this could decrease efficiency because the calculation will need to be evaluated for each record when the summary field is doing it's thing.

 

Anyways, it's not nearly as complicated as this post might make out to be, I just tend to write a whole lot to try and explain it as best I can. Hopefully you were able to follow it.

Link to comment
Share on other sites

This works, wow. Yes I'm glad you took the time to write it all out the next man might need more detailed instructions than I do, so again thanks. The sum does pickup a little slow though there are 5000x records...smiley-smile

Link to comment
Share on other sites

I will look into it when I start deploying this module to fm10.

maybe that event trigger stuff will help, but anyway thanx a lot for your help.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use