| Author |
Message |
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
   Topic: Eliminating Duplicates from different rows
Posted: 25 Oct 2011 at 3:26am |
I have a report with a summary value that is repeating in row 6 and then it repeats in row 9 and I used the 'suppress if duplicate' under 'format field' option but it didnt work. So I used a formula instead.
if {table.amount} = next({table.amount}) then 0 else {table.amount}
The formula above only suppresses if two consecutive rows are showing duplicates, but does not if duplicates are occuring in a different row. Below is what is showing from the formula. It's a numeric variable.
Amount
1. 2789.89
2. 89233.89
3. 7892.89
4. 0
5. 89343.78
5. 0
6. 627322.56
7. 23232.36
8. 78292.35
9. 627322.56
The rows in bold is the problem. Row 9 should be suppressed because its duplicating row6, but the above formula is not recognizing it. So what other alternatives are possible for this? Please...any ideas?
|
 IP Logged |
|
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 25 Oct 2011 at 3:55am |
|
You can try creating a string variable that attaches each amount to it. Something like:
whileprintingrecords;
global stringvar sAmount:=sAmount & ',' & {table.amountfield};
This field will have to be placed on the detail line and you can suppress it so it doesn't show.
Then try setting the detail line to suppress if {table.amountfield} in global stringvar sAmount.
The suppression part might not work properly though because it may evaluate the amount field before it does the suppression and then every detail line will be suppressed.
If that is the case, you could create a new formula field to display the amount field. Something like:
if {table.amountfield} in global stringvar sAmount
then ""
else {table.amountfield}
If you use this scenario, instead of setting the string variable field to whileprintingrecords set it to evaluateafter({@AmountField}).
Hope that helps.
Edited by FrnhtGLI - 25 Oct 2011 at 3:56am
|
|
|< /\ '][' ( )
|
|
IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 25 Oct 2011 at 5:03am |
|
Thanks for your suggestion but I dont understand why I have to use 'stringvar'?...the amount field is a numeric...and even if I use the stringvar I have to convert the rest of the {table.amount} using 'totext'...which I did and where do I use the evaluateafter(table.amount}...I didnt clearly get what you meant by that.
|
|
IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 25 Oct 2011 at 6:17am |
|
Well, with a numbervar, it's not going to allow you to keep a list, which is basically what the stringvar will do. Like you said, you will have to convert to text.
the evaluateafter function will be used in the first formula:
evaluateafter({@AmountField});
//instead of whileprintingrecords//
global stringvar sAmount:=sAmount & totext({table.amountfield}) & ', ';
The {@AmountField} will be the new formula you created:
if totext({table.amountfield}) in global stringvar sAmount
then ""
else totext({table.amountfield})
Then you can suppress the detail sections when {@AmountField}=""
Edited by FrnhtGLI - 25 Oct 2011 at 6:19am
|
|
|< /\ '][' ( )
|
|
IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 25 Oct 2011 at 7:30am |
Ok. I created the new formula @amountfield and applied the 'evaluateafter({@amountfield});' in the string variable but I get an error which says 'A formula cannot refer to itself, either directly or indirectly'.
|
|
IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 25 Oct 2011 at 8:03am |
|
You need two formulas: You can name them whatever, but for the sake of this post, I will name them AmountField and StringVarField.
Create them both before adding anything to them. Then, for the StringVarField, put in the first formula:
evaluateafter({@AmountField});
global stringvar sAmount:=sAmount & totext({table.amountfield}) & ', ';
Then add the next formula in AmountField:
if totext({table.amountfield}) in global stringvar sAmount
then ""
else totext({table.amountfield})
|
|
|< /\ '][' ( )
|
|
IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 25 Oct 2011 at 8:26am |
|
Got it. It's working now. Thanks alot buddy!...appreciate your time :) !
|
|
IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 25 Oct 2011 at 8:30am |
|
One last question. There are other fields in the report that are also showing the duplicates as the amountfield was showing, do I have to use the same method for all the fields because now the amounbt field is working fine now but the other adjoining fields are showing the duplicates. Is there a formula that I can apply for all the fields in the section expert?.
|
|
IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 25 Oct 2011 at 8:38am |
|
Are the adjoining fields on the same detail line as the Amount field? Are the duplicate adjoining fields related to the duplicated Amount fields?
I would think that if the fields are on the same detail line and they are related, that they would be suppressed if you go to the Section Expert and set the suppression of the detail line based off the "" value of the @AmountField formula.
|
|
|< /\ '][' ( )
|
|
IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 25 Oct 2011 at 8:59am |
Its all working fine now. Thanks again!
Edited by moontide - 26 Oct 2011 at 4:27am
|
|
IP Logged |
|
|
|
