Sage 100

 View Only
Back to discussions
Expand all | Collapse all

Need help with a formula in Crystal - v2020, please

  • 1.  Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 17:38
    Some inventory items have no descriptions (don't ask).  I'm building a report for the client to upload their catalog to a website.  They have asked me to use a default description if the parts have no description.  I need the formula to do something like this;
    If {CI_Item.ExtendedDescriptionKey} <> "0000000000" then {CI_ExtendedDescription.ExtendedDescriptionText} else {CI_Item.ItemCodeDesc}

    and if the part number has no item description, print "XXXX part or Accessory"


    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------


  • 2.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 17:48
    I might start with this:

    If trim({CI_Item.ItemCodeDesc})="" then "XXXX part or Accessory" else If {CI_Item.ExtendedDescriptionKey} <> "0000000000" then {CI_ExtendedDescription.ExtendedDescriptionText}



    ------------------------------
    Therese Logeais, Technology Integrators
    ------------------------------



  • 3.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 17:50

    YES,

    Same as what Therese said but also make sure you have Default values for Nulls set

     

    Don't miss out on the 2022 Basic Crystal Reports for Sage 100 online Training: View the details

    Don't miss out on the 2022 Intermediate Crystal Reports for Sage 100 online Training: View the details

     

     

    David Overholt

    DWD Technology Group, Inc.

    9921 Dupont Circle Drive West, Suite 300 | Fort Wayne, IN 46825

    260.423.2414 main

    800.232.8913 toll free

    sagesupport@dwdtechgroup.com

    www.dwdtechgroup.com

     




    Original Message


  • 4.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 18:04
    I've had mixed results with how CR handles NULL values (even when set to use Default Values), but this usually works.
    Outer join.
    Then use a NULL test in your formula.


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 5.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 17:52
    And a final "else {CI_Item.ItemCodeDesc}" to catch short descriptions that aren't blank

    ------------------------------
    Beth Bowers
    Mom to Peanut, ChiChi, Canton, Cagney and Daisy (I miss you, Sammy)
    Tennessee Software Solutions
    ------------------------------

    Original Message


  • 6.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 18:18
    Kevin's suggestion gives me short descriptions and the text string, but no extended descriptions.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------

    Original Message


  • 7.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 18:24
    I leave the Exceptions for Nulls as is, which might be the issue.


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 8.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 18:30
    Ruh Roh, no, that made the text string disappear.  Now I only have short descriptions.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------

    Original Message


  • 9.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 18:35
    Add in another null test to check for null short descriptions:
    if isnull({CI_Item.ItemCodeDesc}) or trim({CI_Item.ItemCodeDesc}) = ""
    then "XXXX part or Accessory"
    else
    if isnull({CI_ExtendedDescription.ExtendedDescriptionText}) or {CI_Item.ExtendedDescriptionKey} = "0000000000"
    then {CI_Item.ItemCodeDesc}
    else{CI_ExtendedDescription.ExtendedDescriptionText}

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 10.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 18:44
    No, no love.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------

    Original Message


  • 11.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 18:53
    Odd... works for me. :-(


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 12.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 19:02
    2nd set of eyes on my formula?
    If isnull ({CI_Item.ItemCodeDesc}) or trim ({CI_Item.ItemCodeDesc}) = ""
    then "Lemo Part of Accessory"
    else
    If isnull ({CI_ExtendedDescription.ExtendedDescriptionText}) or {CI_Item.ExtendedDescriptionKey} = "0000000000"
    then {CI_Item.ItemCodeDesc}
    else {CI_ExtendedDescription.ExtendedDescriptionText}

    And Default Values for Nulls is set.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------

    Original Message


  • 13.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 10:01
    What's the symptom?  Perhaps your outer join is the wrong way?  (Left vs Right depends on which side CI_Item is listed).

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 14.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 18:30
    Edited by Kevin Moyes 03-31-2022 18:33

    if isnull({CI_Item.ItemCodeDesc}) or trim({CI_Item.ItemCodeDesc}) = ""
    then "XXXX part or Accessory"
    else
    if isnull({CI_ExtendedDescription.ExtendedDescriptionText}) or {CI_Item.ExtendedDescriptionKey} = "0000000000"
    then {CI_Item.ItemCodeDesc}
    else{CI_ExtendedDescription.ExtendedDescriptionText}
    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 15.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 19:20
    Populating the missing description with the requisite "filler" in CI_item is not an option?

    ------------------------------
    Jeff Schwenk
    Bottomline Software, Inc.
    ------------------------------

    Original Message


  • 16.  RE: Need help with a formula in Crystal - v2020, please

    Posted 03-31-2022 19:56
    I'm going to run this by the client.  Thanks, Jeff.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------

    Original Message


  • 17.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 10:50
    Here's another approach using Crystal syntax that had worked for me:

    IIF (LEN({SO_InvoiceWrk.ExtendedDescriptionText})>LEN({SO_InvoiceWrk.ItemCodeDesc}),{SO_InvoiceWrk.ExtendedDescriptionText} , {SO_InvoiceWrk.ItemCodeDesc})

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 18.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 10:59
    When I get really frustrated, I break things down into different formulas. 
    • One for short description, with null cleaning (converted explicitly into ""... or in your case, the alternate text).
    • One for the extended description with null cleaning (converted explicitly into "").
    • Then the formula to use the cleaned extended description when not "", otherwise use the cleaned short description.
    To troubleshoot, drag the three formulas onto the report and make sure the different pieces work as expected.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 19.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 11:48
    I am in agreement with you, @Kevin Moyes ... nearly all my issues with this sort of thing went away with a proper left outer join of the tables.  Another technique I have had pretty good success with is creating a sub report ... In the main report I try to have a single table... no joins at all, and then simply pass in the field that would normally be joined on to the sub-report.  In this case, the main report using the CI_Item table, pass in the Item Code to the "Description" sub-report that is built on the Extended description table ... ​

    ------------------------------
    Randy Marion
    ------------------------------

    Original Message


  • 20.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 11:01
    Substituting your fields:

    IIF (LEN({CI_ExtendedDescription.ExtendedDescriptionText})>LEN({CI_Item.ItemCodeDesc}),{CI_ExtendedDescription.ExtendedDescriptionText} , {CI_Item.ItemCodeDesc})


    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 21.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 11:59
    @Doug Higgs, thanks.  I don't get what the entire formula should look like, however.  Looks like you posted a portion of a formula.​

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------

    Original Message


  • 22.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 12:03
    This IS the entire formula:

    IIF (LEN({CI_ExtendedDescription.ExtendedDescriptionText})>LEN({CI_Item.ItemCodeDesc}),{CI_ExtendedDescription.ExtendedDescriptionText} , {CI_Item.ItemCodeDesc})

    In pseudocode:  If the length of the extended description is greater than the length of the regular description then return the extended description else return the regukar description.

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 23.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 12:13

    Got it.  This will work as long as I import the text string into the parts with no description.

    Note:  Table linkage: from CI_Item to CI_ExtendedDescription w/ a L. Outer join.



    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------

    Original Message


  • 24.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 12:22
    Try it without the Alias table.  That is likely the culprit.  (I usually use a sub-report for aliases, since the join is not guaranteed to be 1:1...).

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 25.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 12:31
    Thanks all.  I need the alias table, and the report needs to be exported to .CSV.  I tried having 2 fields, one on top of the other (one for the description, one for the text string, which worked great for the report, but it didn't export properly.  The same will likely occur if I use a subreport.  I appreciate all the assistance, but I'm pressing for importing the text string into the items with no description.  Doesn't make sense to not have that field populated anyway.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------

    Original Message


  • 26.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-01-2022 12:54
    Removing the Alias table would be for troubleshooting.
    Sub-reports can be configured to be silent, passing values out to the main report using shared variables, for export to CSV nicely... and the sub-report strategy fixes wonky table join issues.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 27.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-05-2022 19:31
    As it turns out, the parts are not required to have item descriptions for the purposes of this report.

    ------------------------------
    Barbara Goldstein
    Sr. Consultant
    DSD Business Systems
    ------------------------------

    Original Message


  • 28.  RE: Need help with a formula in Crystal - v2020, please

    Posted 04-05-2022 20:12
    Well, hopefully they pay the invoice for your efforts!!!!

    ------------------------------
    Jeff Schwenk
    Bottomline Software, Inc.
    ------------------------------

    Original Message


Related Content