Sage 100

Sage v4.45 SQl. Working on an AR Statement. Have suppressed line items using section expert. Also removed the aging at the bottom of the form. Now need to total the remaining lines. Tried using running totals and a variety of other formulas, but can't seem to exclude the suppressed transactions from the totals. Any trick to this?

I have found that you usually have to make new formulas to calculate suppressed items as ""0"". For example for open invoice amount if you would create a formula rather than use the field in the table, such as ""If (ar_invoice.type)=CM then 0 else {openinvoiceamount}. I know my syntax isn't right as I'm not looking at MAS field names right now, but basically whatever the rule is for the suppressed field you need to specify the amount as 0 so that when it's subtotaling that column those suppressed line values will be zero. Either that or exclude those suppressed items in the Select Expert if that is possible - then you won't have to suppress them.

Jane's right on the money here Doug. Or you could use shared variables in the formulas to total the amount you want. But I think Jane's first idea is the easiest to implement. Btw, I believe I warned you about going down this road in the first place. (It can be a real battle chasing down all the little loose ends when you start suppressing lines on statements.)

Thanks @JaneScanlan . I have tried using a similar formula than the one I used the detail suppression to exclude the records from the running total but that doesn't seem to work. I'll take another look at the syntax. @ BrettLyder - yes you did warn me... I'm already on this road so I will find the way to the end.

The best way to do this is to use a formula for the detailed line suppression logic in the Selection Expert (i.e. @SuppressDetailLine) and then use same formula wrapped in a Not function ( i.e. @Not(@SuppressDetailLine) ) for the Evaluate formula in the running total.

Thanks everyone for the great help! the form looks great and is totaling as intended. For future reference, here is what I did to exclude credits older than 180 days: First, added detail suppression logic to the detail line. Next, created a running total formula (not a crystal running total). The formula I used is: WhilePrintingRecords; NumberVar x; if not((({#InvoiceBalance} < 0) AND (DateAdd (""d"",180,{@InvoiceOrTransactionDate}) < CurrentDate)) or {AR_StatementWrk.OpenItemCustomer} = ""N"" or {AR_StatementWrk.LineNumber} = ""0000000000"") then x := x + {@newbalance} else 0; One of the bugs I had that kept the records from accumulating was I originally initialized the variable x as local Numbervar. I then tried Shared Numbervar... neither would accumulate the totals in x. NumberVar x was the answer... still not entirely clear why.. I would think defining x as a local variable would work but it doesn't. Finally, display the final value of x on the totals line (group total): WhilePrintingRecords; NumberVar x; thanks again for the help!

In what section did you use this running total formula? What is in your Crystal Running Total - {#InvoiceBalance}?

@DanBurleson - The running total from my previous post was used in the Details A section. Also, {#InvoiceBalance} is from the STANDARD report setting. It is located in the far right column of the Details A section. See attached screen shots of the detail used in the final formulas. The NEWBALANCE formula is simply the value of each line item. It should be the same as the #INVOICE running total but ""crystal"" running totals can't be used in certain operations lie a ""formula"" running total can. The NEWBALANCE formula is used in the line item running total formula detailed in my previous post. I understand this could have been done a bit more elegantly but some of this was used to take steps to the final goal.