Skip to Content
0

How to Combine Two Fields: Numeric and String

May 09, 2017 at 03:02 PM

87

avatar image

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)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

7 Answers

Abhilash Kumar
May 09, 2017 at 03:26 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Dirty Rony May 09, 2017 at 03:41 PM
0

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")

Share
10 |10000 characters needed characters left characters exceeded
Dirty Rony May 09, 2017 at 04:04 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Dirty Rony May 09, 2017 at 05:19 PM
0

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")

Share
10 |10000 characters needed characters left characters exceeded
Brian Dong May 09, 2017 at 05:09 PM
0

Hi,

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

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

Brian

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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")

0

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

0
Dirty Rony May 09, 2017 at 07:41 PM
0

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!

Share
10 |10000 characters needed characters left characters exceeded
Dirty Rony May 11, 2017 at 09:54 AM
0

Hi Abhilash and Brian

Thank you for your help on this matter!

Share
10 |10000 characters needed characters left characters exceeded