Replace Data

Hi:

I have a field {itemcode} this field contains the data almost in a memo format with possibly a return.  I need to replace the line in the field that starts with Contract and Off Contract for 30 characters with " " - can this be done?

Example of what field looks like on the form - (it is one field with Can Grow turned on and I need the Can Grow turned on for the items that are not contract items.)

Ultra Low Clear
Contract: 3000 GAL @ $2.2002
Off Contract: 4000 GAL @ $2.2800

I need to have the field for this item show on the Form as:

Ultra Low Clear

Any help is appreciated.

Thank you.
Erin LealeConsultant/ControllerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Will the text always have that format?

IE
CONTRACT:  #### GAL @ $#.####
OFF CONTRACT:  #### GAL @ $#.####

mlmcc
Erin LealeConsultant/ControllerAuthor Commented:
Yes.  It might have ##### for the gallons but that would be rare.

Thanks.
mlmccCommented:
I assume the field has data like

Ultra Low Clear
 Contract: 3000 GAL @ $2.2002
 Some text you want to keep
 Off Contract: 4000 GAL @ $2.2800
Other text you want to keep


You want to see
Ultra Low Clear
 Some text you want to keep
Other text you want to keep

mlmcc
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Erin LealeConsultant/ControllerAuthor Commented:
On those where the 2nd and 3rd line start with Contract: and Off Contract:  I want to eliminate those lines only.  They will always show Contract then right below Off Contract if the customer exceeded the contract gallons.

Thanks.
mlmccCommented:
Will there be carriage returns at the end of the lines?

If so try this idea

Create a formula

Local StringVar Array strLines;
Local NumberVar Index;

strLines := Split({YourFIeld},chr(13));
For Index := 1 to UBound(strLines;
  If InStr(strLines[Index], 'Contract:') = 1 then
     strLines[Index] := ''
  Else If InStr(strLines[Index], 'Off Contract:') = 1 then
     strLines[Index] := '';
Join(strLines,chr(13));

mlmcc
James0628Commented:
FWIW, you could also do this using functions like Mid, but mlmcc's approach seems simpler.

 There's a typo on the For line.  It should be:

For Index := 1 to UBound(strLines) do


 Also, you could simplify it a bit by combining the 2 tests:

  If InStr(strLines[Index], 'Contract:') = 1 or
   InStr(strLines[Index], 'Off Contract:') = 1 then
    strLines[Index] := '';


 FWIW, I think I'd use Left instead of InStr:

  If Left (strLines[Index], 9) = 'Contract:' or
   Left (strLines[Index], 13) = 'Off Contract:' then
    strLines[Index] := '';

 The result's the same either way, but Left would theoretically be more efficient, since InStr would search the entire line for the target string, but you're only interested in the first characters.

 However, having said all of that, I have also realized that the formula probably doesn't quite work the way you'd want.  You end up with blank lines where the "Contract" and "Off Contract" lines were.  I don't know if there's an easy fix for that.  My first thought would be to loop through the lines after one of those lines, and move them all up 1 spot in the array.  But maybe there's a simpler solution.

 James
James0628Commented:
I didn't have time to get into it in my last post, but now that I've given it a little more thought, the simplest thing is probably to copy the lines that you want to see to a new array, and output the new array at the end.  Here's a variation on mlmcc's formula (with the other changes from my last post) that does that:

Local StringVar Array strLines;
Local StringVar Array OutputLines;
Local NumberVar Index;
Local NumberVar OutputIndex;

strLines := Split ({your_field}, chr(13));
Redim OutputLines [ UBound (strLines) ];

For Index := 1 to UBound (strLines) do
  If Left (strLines [Index], 9) <> 'Contract:' and
   Left (strLines [Index], 13) <> 'Off Contract:' then
  (
    OutputIndex := OutputIndex + 1;
    OutputLines [ OutputIndex ] := strLines [Index]
  );
Redim Preserve OutputLines [ OutputIndex ];
Join (OutputLines, Chr (13))

Open in new window


 The Redim at the end is to remove any blank lines that would be created at the end by the exclusion of the "Contract:" and "Off Contract:" lines.

 As mlmcc mentioned, there is an assumption here that your lines are separated by a Carriage Return (and only a CR, without a Line Feed).  And if the field could be null or blank, you'll probably need to test for that.

 James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Erin LealeConsultant/ControllerAuthor Commented:
Thank you!  That worked!  I tested it with extended description information other than the Contract and Off Contract and it seems to work great.   Still testing but thank you - thank you!
James0628Commented:
You're welcome.  Glad I could help.

 James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.