Sage 100

 View Only

  • 1.  Need help with CR formula

    Posted 10-20-2022 19:27
    Edited by Michael McDonald 10-21-2022 14:38
      |   view attached
    I have a client running SAGE 100 v2019. I created a CR report that reads the AR Histiory Detail file and summarized all the QTY SOLD amounts and Extended Amounts and prints a total line for each CI_Item break. I don't believe I labeled my formula field correctly, but when I print a total line on my report, I divide the Sum of all Extended Amounts by the Sum of QTY SOLD to create an Average Unit Sales Price. However, some months I get an error message because the total line is trying to divide when Sum of QTY SOLD = 0.00. How can I change my formula to test for and fix the "Division by ZERO" error message?  I have attached my formula field below.

    Thank for any help,

    ------------------------------
    Michael McDonald - Consultant
    Michael T McDonald CPA
    (616) 813-8887 - mike@mcdcpa.com
    ------------------------------


  • 2.  RE: Need help with CR formula

    Posted 10-20-2022 19:49
    Make it an IF statement.
    IF <>0
    THEN (original formula)

    Hopefully my cutting and pasting made sense

    ------------------------------
    Beth Bowers
    (269) 358-0989
    ------------------------------



  • 3.  RE: Need help with CR formula

    Posted 10-21-2022 08:30
    I also add another criteria to the above formula in case the extended price is 0:

    IF ({AR_InvoiceHistoryDetail.QuantityShipped}, {AR_InvoiceHistoryDetail.ItemCode}) <> 0
    AND ({AR_InvoiceHistoryDetail.ExtendedAmount}, {AR_InvoiceHistoryDetail.ItemCode}) <> 0
    THEN (original formula)
    else 0

    ------------------------------
    Susan Pawlowic
    Sage 100 Solutions Architect
    RKL eSolutions, LLC
    spawlowic@rklesolutions.com
    (717) 735-9109 Ext. 4352
    ------------------------------

    Original Message


  • 4.  RE: Need help with CR formula

    Posted 10-21-2022 08:35
    I think dividing 0 by anything is still 0 and won't cause an error, but it's not WRONG to do that, either.  Usually you only have to worry about what is after the division sign.

    ------------------------------
    Beth Bowers
    (269) 358-0989
    ------------------------------

    Original Message


  • 5.  RE: Need help with CR formula

    Posted 10-21-2022 09:15
    Correction to the above:  It's OR, not AND.

    ------------------------------
    Susan Pawlowic
    Sage 100 Solutions Architect
    RKL eSolutions, LLC
    spawlowic@rklesolutions.com
    (717) 735-9109 Ext. 4352
    ------------------------------

    Original Message


  • 6.  RE: Need help with CR formula

    Posted 10-21-2022 13:33
    My HERO of the week @Beth Bowers!  Your suggestion worked perfectly! No more error messages. And thanks @Susan Pawlowic for your contribution. It does not apply to my particular Crystal Report, but I will add your formula to my database of "Tips & Tricks".....Happy Halloween!  ​​

    ------------------------------
    Michael McDonald - Consultant
    Michael T McDonald CPA
    (616) 813-8887 - mike@mcdcpa.com
    ------------------------------

    Original Message


Related Content