formula returns duplicate information sometimes...

[rbrownie]

Hello,

I have a formula to calculate invoice total based on invoice line plus misc charges only if invoice code is one of the following 3 codes (which is a string): (3,7 or 8). The formula below works perfectly except when there is an instance of the misc charges occuring in more than one of the chosen codes, in which case it repeats the invoice and calculation for each instance (it only needs to do it once). I think this should be simple, but I am at a loss...



if {InvcDtl.TotalMiscChrg}>0 then

Select {MiscChrg.MiscCode}
Case "3":
{InvcDtl.ExtPrice}+{InvcDtl.TotalMiscChrg}
Case "7":
{InvcDtl.ExtPrice}+{InvcDtl.TotalMiscChrg}
Case "8":
{InvcDtl.ExtPrice}+{InvcDtl.TotalMiscChrg}
Default:
{InvcDtl.ExtPrice} else
{InvcDtl.ExtPrice}

Any help would be appreciated!

thanks!

 

[fisheromacse]

IF {MiscChrg.MiscCode} IN [3,7,8]
THEN {InvcDtl.ExtPrice}+{InvcDtl.TotalMiscChrg}
ELSE {InvcDtl.ExtPrice}

 

[rbrownie]

Fisheromacse,thank you!

I have discovered that I don't think it was my formula after all, it looks like any time there is an instance of more than one miscellaneous charge on the invoice, whether it is added in or not, the invoice is repeated that many times...any ideas on how to remedy that?

Thanks again for any help you can offer!

 

[fisheromacse]

Yes, in the details section it will print a result for each record, so if you have the following:

INVOICE #s: 1001, 1002, 1003
and # 1001 has 2 qualifying misc charges, while 1002 has a qualifying misc charge and a non-qualifying misc charge, and 1003 has no misc charges, you would end up with something like the following:

Detail Record 1 = 1001 & qualifying misc charge = {InvcDtl.ExtPrice}+{InvcDtl.TotalMiscChrg}
Detail Record 2 = 1001 & qualifying misc charge = {InvcDtl.ExtPrice}+{InvcDtl.TotalMiscChrg}
Detail Record 3 = 1002 & qualifying misc charge = {InvcDtl.ExtPrice}+{InvcDtl.TotalMiscChrg}
Detail Record 4 = 1002 & non-qualifying misc charge = {InvcDtl.ExtPrice}
Detail Record 5 = 1003 & no misc charge = {InvcDtl.ExtPrice}

Is this how your data is displaying?


You can try to group by Invoice # and place the formula in the group footer, but will require some formula changes/additions.


If you go with the group footer, you will need a way to evaluate all the records in the details for compliance with your criteria.
This is easiest done with a variable placed in the details section:
//{@CriteriaMet}
numbervar critmet;
IF {MiscChrg.MiscCode} IN [3,7,8]
THEN critmet := 1
ELSE critmet := 0;

You then need to reset the variable before the next invoice # using the below placed in the group header:
//{@ResetCriteriaMet}
numbervar critmet := 0;

Create a variable for total charges (if the criteria is met, then a new total is calculated otherwise the previous value is kept):
//{@TotalCharges}
numbervar totalch;
IF {@CriteriaMet}=1
THEN totalch := {InvcDtl.ExtPrice}+{InvcDtl.TotalMiscChrg}
totalch := totalch;

And you will need a reset for {@TotalCharges}, also placed in the group header:
//{@ResetTotalCharges}
numbervar totalch := 0;


Create a Summary of {@CriteriaMet} per Invoice # by selecting 'Insert Summary', choose the formula you created {@CriteriaMet}, choose 'SUM' as the option under 'Calculate this summary' and Summary location should be the group footer.

In the Group Footer, evaluate the Summary to determine if any of the Invoice records had qualifying misc charges:
//{@GroupFooterDisplayValue}
IF (Sum({@CriteriaMet},{TableName.Invoice#}))>0
THEN {@TotalCharges}
ELSE {InvcDtl.ExtPrice}



I may have misinterpreted something in your request, it has been a low octane caffeine morning and I have not had opportunity to test any of the above on dummy data...it is all from memory, so i apologize if it is not helpful or contains silly syntax errors.

 

[rbrownie]

Fisheromacse,

Thank you very much for your help, I sent it off to our outside developers, and did a web-session with him he basically did what you said above, grouped by invoice, and we moved down to the footer, also, we had to do running totals for all of my sums to calculate. I had grouped by Customer Group, then by Customer Name and then by Customer ID with a Grand total at the end. Actually watching and helping him do it was a huge help and now seeing what you have shown me above has been a big learning experience for me...thanks again for your help!!!

RB