String Formula help needed

[CARLEENHOP]

I have a comments field that i am extracting from. I have set up the structure of these comments with a name and '-' as each of them relate to specific information that I would like to extract in crystal and isolate.

eg.
name - xxxxxxxx
size - xxxxxx
colour - xxxx

What can i use in crystal to say anything that follows "name" and is before "size" print, and so on. so i can extract this data via 3 fields in crystal.

thanks

 

[MCuthill]

CarleenHop,

You will need to use InStr() and Mid() for this.

InStr() will return the character position of the first character of a sought string. Use this to define your starting point. You can do the same for your ending postion by searching the next heading and subtracting the number of characters.

The formulas should look something like this. Though these will work, even if something is entered before your example above as well.

{@ExtractName}
Note: "name - " is 7 characters

Mid({YourField},InStr(1,{YourField},"name - ")+7,(InStr(1,{YourField},"size - ")-(InStr(1,{YourField},"name - ")+7)-1))

The last "-1" assumes a space between all your text. Remove if uneeded.

If you will NEVER have text before the name, you can use:

Mid({YourField},7,(InStr(1,{YourField},"size - ")-(InStr(1,{YourField},"name - ")+7)-1))

{@ExtractSize}
Note: "size - " is 7 characters

Mid({YourField},InStr(1,{YourField},"size - ")+7,(InStr(1,{YourField},"color - ")-(InStr(1,{YourField},"size - ")+7)-1))

The last "-1" assumes a space between all your text. Remove if uneeded.

{@ExtractColor}
Note: "colour - " is 9 characters

Mid({YourField},InStr(1,{YourField},"colour - ")+9)

With the # of characters clause left out, this will return all characters in the memo field after "colour - ". You will need to flag the number of characters the same as the other formulas if there is data after this in the memo field.

I tested with:
"name - [red]MyTestName[/red] size - [red]TestSize[/red] colour - [red]FavColour[/red]"
each formula returned the text marked in red.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

PS:

Syntax for the above functions as follows:

MID:
Mid({Field},StartingPoint,Length)
or
Mid({Field},StartingPoint) <-- return all after point.

INSTR:
InStr(StartingPoint,{Field},StringSought)

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[CARLEENHOP]

Thx, I will give it a go and let you know.

 

[CARLEENHOP]

I am getting a message stating that "string length is less than 0 or not an integar"

Should I have mentioned that to get my comments data I am using SQL expression field rather than just displaying a field? SQL is "get_blob(supplier.blobidx)" where you mentioned {your field} i used this SQL expression field name.

 

[MCuthill]

CarleenHop,

I am not familiar with using SQL expressions, but I don't think it should matter. The expression *should* be no different then a database field...

I would assume this error is when you run the report, not when saving the formula? If so, is it possible that the expression could be empty? (ie a null memo field) I am unsure what else could cause a non-integral return.

If this is the case, edit the formula's to only do the above when memo is not null.

IF Not(IsNull({YourSQLExpression})) THEN ...

Hope this helps.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

You could also try formulas like this:

//{@Name}:
extractstring({%comments},"Name - ", chr(13))

//{@Size}:
extractstring({%comments},"Size - ", chr(13))

//etc.

If chr(13) (return) doesn't work, you could test with chr(10) (line feed).

-LB