How to display a substring using "LIKE" and specific words

[LWTri]

Hey all,
Using Crystal XI. Does anyone know the best way to do this?

Please see the 3 scenarios (invoices) below:
(1) The whole invoice is free-form text in {Invoice.InvoiceText} field of data type, MEMO.
(2) I need to grab the Length (Size of Accessory). EX: For Invoice #1, that would be 5.0
(3) I will then need to transform the string (5.0) into number type, then calculate the Average Length for all invoices.

Solution:
(1) I just need to grab the LENGTH (first dimension) for Size of Accessory. I want to grab the substring (5.0) AFTER "Size of Accessory" and before the first instance of "cm" (associated with Size of Accessory). I want to grab a substring LIKE "Size of Accessory*cm", but that won't work because there are numerous instances of "cm" afterwards on the invoice. Or...I could grab a substring LIKE "Size of Accessory*Item Type" (to limit substring and avoid other instances of "cm" in invoice), but sometimes there are Additional Dimensions for Size of Accessory (ex: Invoice #2 and #3).

Problem: Also, I can't grab the first "number" after Size of Accessory because they're actually characters in MEMO field.

Thanks for your help!



------------
INVOICE #1
------------

Item: Dominoes
Color: Black,White
Size of Item: Length:2.5cm
Size of Accessory: Length:5.0cm
Item Type: Toy
Quantity: 100
Size of Box: Length:60cm



------------
INVOICE #2
------------

Item: Dominoes
Color: Black,White
Size of Item: Length:2.5cm
Additional dimensions: 1.0cm X 0.5cm
Size of Accessory: Length:4.0cm
Additional dimensions: 4.0cm X 4.5cm
Item Type: Toy
Quantity: 100
Size of Box: Length:60cm




------------
INVOICE #3
------------

Item: Dominoes
Color: Black,White
Size of Item: Length:2.5cm
Comment(s): A. 1.0cm B. 0.5cm
Size of Accessory: Length:7.0cm
Comment(s): A. 7.0cm B. 7.5cm
Item Type: Toy
Quantity: 100
Size of Box: Length:60cm

 

[lbass]

You should be able to use:

extractstring({table.memo},"Size of Accessory: Length:","cm")

Not sure what you would want to see when there is an additional dimensions line--are you wanting to see more than one value returned? If so, what would you expect to see for your second example.

-LB

 

[LWTri]

Thanks lbass!

For all three scenarios, I only want to grab the Length from Size of Accessory (even when there are extra dimensions). My task is to ignore all the other sizes or "cm"s on the invoice.

I'll need to extract for Invoice #1: "5.0"
Invoice #2: "4.0"
Invoice #3: "7.0"

I will try the extractstring function! I hope it works. I'm not 100% sure because there are many occurrences of "cm" in the memo field, and I need to grab the measurement associated with the FIRST occurrence of "cm" after "Size of Accessory" (Length), which is "5.0" (Invoice #1).

Once I can do that for all invoices, I will convert the extract strings into numbers and average them. For the 3 invoices, the avg would be (5+4+7)/3 ~= 5.33 (that's the easy part). Thanks!

 

[lbass]

Extractstring will return the value between the second argument and the first instance of the third argument.

-LB

 

[LWTri]

I did try it this morning and indeed it worked! (returns string up to first instance of third argument)...Yahoo. Thanks AS USUAL, lbass!

 

[LWTri]

Hi lbass (or anyone else), I've had a tough time finding online a simple solution for this next question. I'm also trying to extract/display a string that STARTS with a certain string pattern and ENDS with a certain string pattern. There is no consistent start or end position in string/array (so I don't think we can use LEFT/MID/RIGHT functions). Wonder if there's an ExtractString-like function that can be combined with LIKE function.

Want to display the full string starting with "Size of Accessory:" and ending with the FIRST instance of "cm". For the scenarios above, we would display this below. Thanks!


------------
INVOICE #1
------------
Size of Accessory: Length:5.0cm

------------
INVOICE #2
------------
Size of Accessory: Length:4.0cm

------------
INVOICE #3
------------
Size of Accessory: Length:7.0cm

 

[lbass]

mid({table.memo},instr({table.memo},"Size of Accessory:"),31)

-LB

 

[LWTri]

Thanks lbass! Actually, I didn't give the best example. Here are 3 more scenarios:


------------
INVOICE #4
------------
Item: Stuffed Animal
Color: Yellow
Item Type: Infant toy
Toddler toy
Children's toy
Quantity: 5
Size of Box: 60cm x 60cm x 60cm

------------
INVOICE #5
------------
Item: Bike
Color: Blue
Size of Item: Medium
Item Type: Sporting equipment
Quantity: 1

------------
INVOICE #6
------------
Item: Acetaminophen
Item Type: Pain reliever medicine
Fever reducer medicine
Quantity: 3
Comment(s): If child under 3 months, consult with physician.


Want to display the full string starting with "Item Type: " and ending with "Quantity:". There is no consistent start or end position in string/array.

For the scenarios above, we would extract this below. Thanks!


------------
INVOICE #4
------------
Item Type: Infant toy
Toddler toy
Children's toy
Quantity:

------------
INVOICE #5
------------
Item Type: Sporting equipment
Quantity:

------------
INVOICE #6
------------
Item Type: Pain reliever medicine
Fever reducer medicine
Quantity:

 

[lbass]

stringvar x := {table.memo};
mid(x,instr(x,"Item Type:"),instr(x,"Quantity:")-instr(x,"Item Type:")+9)

-LB