How can I clip out portions of Field data 2

[cinbh]

I have a situation in Crystal 8.5 using VisualFoxPro 7.0 where the Database programmers set up a descriptive field with a type within it. EXAMPLES:
College Paper 8X11
College Paper 11X14
College Paper Yellow Lined
Highlite Markers Fluorescent Yellow
Highlite Markers Fluorescent Pink

So I want to print ONLY "College Paper" or "Highlite Markers Fluorescent" a grouping line as I have the the "Types" set up with formulas as columns within my report.
Any suggestions?
Thank you,
Cindy

 

[synapsevampire]

Since there doesn't appear to be a means to readily distinguish what is a type, and what is a group, you'll have to create a formula and explicitly state what they are.

The alternative is to teach your VFP coders about normalizing data

Examples of creating formulas to group on:

select left({table.descriptive},instr({table.descriptive}," &quot-1))
case "College" :"College Paper"
case "Highlite" : "Highlite Markers Fluorescent"
... do the rest
default :"Unknown"

If you wanted to let the formula just take the first word:

left({table.descriptive},instr({table.descriptive}," &quot-1))

-k

 

[cinbh]

Frankly I gave a quick example to get the point across it's slightly more complicated for our growers business our descriptions are different:
Eckespoint Freedom Red Unrooted Cutting
Eckespoint Freedom Red Callused Cutting
Eckespoint Freedom Red Rooted Cutting
Peter Jacobsens Pepride Marble Unrooted Cutting
Peter Jacobsens Pepride Marble Callused Cutting
Peter Jacobsens Pepride Marble Rooted Cutting
and then I split the Unrooted, Callused, Rooted cuttting types in columns across and the name being the cultivar. Of course I only have over 150 different cultivars. If there was a way to strip out the type based on that rather than starting with the cultivar that would be great. I'm more a novice in some of the complicated functions, so I wasn't sure about the logic you proposed and how that worked.
Thank you,
Cindy

 

[synapsevampire]

Hmmm, well that was a waste of time then.

Try:

if instr({table.description},"Unrooted&quot > 0
then
left({table.description},instr({table.description},"Unrooted&quot-1)
else if instr({table.description},"Callused&quot > 0 then
left({table.description},instr({table.description},"Callused&quot-1)
else if instr({table.description},"Rooted&quot > 0 then
left({table.description},instr({table.description},"Rooted&quot-1)
else
"Dunno"

-k

 

[cinbh]

It worked perfectly for me, THANK YOU SO MUCH!! I'm sorry I didn't mean to waste your time. I oversimplified my example, but it is difficult to gauge exactly how much information to include.
THANK YOU

 

[cinbh]

Okay has worked great until the endusers started to abbreviate the product forms. So now I have a situation where not only do I have the orginal product forms of Unrooted Cutting in the description, when a new item is added some are now being abbreviated to URC (rather than typing out the whole "Unrooted Cutting").

So right now I have items in our inventory table that are described as
Unrooted Cutting
URC
Rooted Cutting
RC
Callused Cutting
CC
Where my biggest problem arises is trying to get the previous post's formula working on the URC versus the RC products. Any suggestions?
Thanks,
Cin

 

[lbass]

Why not first convert the abbreviations to the full text and then use the converted field in the original formula:

//{@convdesc}:
replace(replace(replace({table.description},"URC","Unrooted Cutting"),"RC","Rooted Cutting"),"CC","Callused Cutting")

if instr({@convdesc},"Unrooted") > 0 then
left({@convdesc},instr({@convdesc},"Unrooted")-1)
else if instr({@convdesc},"Callused") > 0 then
left({@convdesc},instr({@convdesc},"Callused")-1)
else if instr({@convdesc},"Rooted") > 0 then
left({@convdesc},instr({{@convdesc},"Rooted")-1)
else
"N/A"

-LB

 

[cinbh]

Again, thank you. I am humbled by your expertise/knowledge of this subject. I learn something new every time I get on this site.