Skip to Content
avatar image
Former Member

How to Combine Two Fields: Numeric and String

Trying to obtain the result in Bold below


IF {'Sales_Data_'.Product Code} STARTSWITH "BR"

THEN {'Sales_Data_'.Qty} + Bottle (if singular) or Bottles (if more than one)

ELSE IF NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR")

THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} + Case (if one) or Cases (if more than one)

ELSE IF {'Sales_Data_'.Product Code} STARTSWITH "DP"

THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case}

+ Case (if one) or Cases (if more than one)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    avatar image
    Former Member
    May 09, 2017 at 07:41 PM

    Hello

    I figured it out!

    Found the solution here

    http://stackoverflow.com/questions/25767315/crystal-reports-trying-to-use-startswith-and-looking-for-2-values

    The solution is

    STARTSWITH ["BR","DP"]

    then I fiddled with the parenthesis.

    Thank you all for your help!

    Add comment
    10|10000 characters needed characters exceeded

  • May 09, 2017 at 03:26 PM

    Hi Rony,

    Try:

    IF {'Sales_Data_'.Product Code} STARTSWITH "BR"
    THEN {'Sales_Data_'.Qty} & if({'Sales_Data_'.Qty} = 1 then " Bottle" Else " Bottles")
    ELSE IF NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR") OR STARTSWITH "DP" 
    THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} & if({'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} = 1 then " Case" Else " Cases")

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 09, 2017 at 03:41 PM

    Thank you again.

    Upon checking it for error before saving, I get the following error message:

    "The ) is missing" on the bold THEN below.

    IF {'Sales_Data_'.Product Code} STARTSWITH "BR"

    THEN {'Sales_Data_'.Qty} & IF ({'Sales_Data_'.Qty} = 1 THEN "Bottle" ELSE " Bottles")

    ELSE IF NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR") OR STARTSWITH "DP"

    THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} & IF ({'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} = 1

    THEN " Case" ELSE (" Cases")

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 09, 2017 at 04:04 PM

    I suppose it has to do with either misplaced parenthesis or missing curly brackets somewhere, but I can't figure out where...

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 09, 2017 at 05:19 PM

    Thank you Brian.

    After amending the formula as per your instruction, I get an error message that "A number, currency amound, boolean, date, time, date-time, or string is expected here" on the bold below:

    IF {'Sales_Data_'.Product Code} STARTSWITH "BR"
    THEN {'Sales_Data_'.Qty} & (if{'Sales_Data_'.Qty} = 1 then " Bottle" Else " Bottles")
    ELSE IF NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR") OR STARTSWITH "DP"
    THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} & if({'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} = 1 then " Case" Else " Cases")

    Add comment
    10|10000 characters needed characters exceeded

  • May 09, 2017 at 05:09 PM

    Hi,

    On the second line, the open bracket should be before the IF like:

    (IF {'Sales_Data_'.Qty} = 1 THEN "Bottle" ELSE " Bottles")

    Brian

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      You need to encapsulate the entire IF condition in brackets like:

      ELSE IF (NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR") OR STARTSWITH "DP")

      Otherwise the logic expects the Or to be a new condition.

      Brian

  • avatar image
    Former Member
    May 11, 2017 at 09:54 AM

    Hi Abhilash and Brian

    Thank you for your help on this matter!

    Add comment
    10|10000 characters needed characters exceeded