Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2017
    Posts
    1

    Unanswered: how to exclude one of two info takes within table

    I have a report that reflects item info. I need to a column with UPC's. However, the table (itemalias) consists of two different info types UPC's and ShortCodes. Short codes consists of alphanumeric and UPC's are alpha only. We also have a description table (aliasdesc) that identifies the two UPC's vs ShortCodes. I am very new to Crystal and SQL so I am not able to figure out a formula to exclude ShortCodes and only give UPC info but leave a blank if there are no UPC's for that item.

    I went into selection export using itemalias and used "is between" 01 and 999999999999

    {CUSTITEMALIAS_NEW.ITEMALIAS} in "01" to "9999999999999999999"

    This is giving me UPC's and excluding ShortCodes but now I am getting multiple items with same UPC for ShortCode would be and excluding Items with no UPC's. Does anyone know of another way or formula I can use?

    Thank you
    George

  2. #2
    Join Date
    Jul 2007
    Posts
    61
    What columns does your itemalias table contain? Does it have item, UPC, Shortcode? Or does it have item, code, [UPCorShortcodeFlag] ? Or is it just item, itemalias?

    Can an item have no UPC or Shortcode? If so would it have an entry in itemalias? If not, then your code, as above, would exclude the NULL possibility where there is no entry and so you would need to add something like:

    IsNull({CUSTITEMALIAS_NEW.ITEMALIAS}) or {CUSTITEMALIAS_NEW.ITEMALIAS} in "01" to "9999999999999999999"

    Also you would need to be sure that you have a left-outer join to this table (I am assuming you are linking in Crystal, rather than in SQL).

    As to why you are getting multiple items with the same UPC against several parts, that implies some imperfect linking or imperfect data. Would need more info to comment further.

    A different approach would be to write a SQL View to sort your data out and then your Crystal report would be much simpler, but it depends on whether you are more comfortable with Crystal or with SQL.

    HTH,
    Caspar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •