Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parse memo field to to next space 3

Status
Not open for further replies.
zigstick

zigstick

Technical User
Jun 1, 2006
50
US
Hello, I've scoured the boards but can't find (or missed) anything specific to my needs.

I'm using Crystal Reports 2008 and am trying to use mid and instr to extract specific data.
Here is a sample of my code, which works, but doesn't cut off the data at the next space.

if {CAServiceDesk_query.Description} like ["*Size -*"] then mid ({CAServiceDesk_query.Description},instr ({CAServiceDesk_query.Description},"Size -")+6,4) else " "

The problem with my query is that 4 is not always the correct number of characters to return. The length of characters following "Size -" is variable. I would like it to stop at the next space following the data I need, as there are several more lines of data in the memo field following.

Ex: if it pulls MARK, that's good, but if it pulls ED Th ... not good.

Any help would be GREATLY appreciated.

mark
 
Sort by date Sort by votes
Thanks Brian, here are 3 records from the memo field.



Service Catalog Request# 667665

Details
----------------------------------------------
Requestor_Name - Shinall, Damon
Contact_Number - xx/xxx-xxx
Requestor_Email - John.Doe@gmail.com
Business_Segment - Segment2
Division - Portfolio Solutions
Initiative - Not Budgeted/Forecasted
ITG_BU - n/a
E:\STG_LDF - 40 GB
E:\STG_MDF - 185 GB
Physical - new
Rack - n/a
PhysicalVirtual - Physical
Domain - NWC
Environment - Dev
IP3 - n/a
Subnet - n/a
OS - Windows 2008
type - Database
HBA1 - n/a
HBA2 - n/a
Clustered - No
LUNQuantity - n/a
Size - 225
Characteristics LUN - EqualReadToWrite
YesSelected7 - numeric
YesSelected12 - numeric
YesSelected14 - If additional locations required, please attached document at checkout


Service Catalog Request# 667669

Details
----------------------------------------------
Requestor_Name - Shinall, Damon
Contact_Number - xx/xxx-xxx
Requestor_Email - John.Doe@gmail.com
Business_Segment - Segment1
Division - Portfolio Solutions
Initiative - Not Budgeted/Forecasted
ITG_BU - n/a
E:\STG_LDF - 40 GB
E:\STG_MDF - 185 GB
Physical - new
Rack - n/a
PhysicalVirtual - Physical
Domain - NWC
Environment - Dev
IP3 - n/a
Subnet - n/a
OS - Windows 2008
type - Database
HBA1 - n/a
HBA2 - n/a
Clustered - No
LUNQuantity - n/a
Size - 1
YesSelected7 - numeric
YesSelected12 - numeric

Service Catalog Request# 667702

Details
----------------------------------------------
Requestor_Name - Shinall, Damon
Contact_Number - xx/xxx-xxx
Requestor_Email - John.Doe@gmail.com
Business_Segment - Segment8
Division - Portfolio Solutions
Initiative - Not Budgeted/Forecasted
ITG_BU - n/a
E:\STG_LDF - 80 GB
E:\STG_MDF - 185 GB
Physical - new
Rack - n/a
PhysicalVirtual - Physical
Domain - NWC
Environment - Dev
IP3 - n/a
Subnet - n/a
OS - Windows 2008
type - Database
HBA1 - n/a
HBA2 - n/a
Clustered - No
LUNQuantity - n/a
Size - 64GB
Characteristics LUN - EqualReadToWrite
YesSelected7 - numeric
YesSelected12 - numeric
YesSelected14 - If additional locations required, please attached document at checkout
 
Upvote 0 Downvote

I think it's often easier to assign the numeric start/end values to variables before assembling the final piece:

whileprintingrecords;
numbervar v_start;
numbervar v_end;

v_start := instr({YourMemoField},"Size -");
v_end := instr(mid({YourMemoField}, v_start + 7)," ");

if {YourMemoField} like ["*Size -*"] then mid({YourMemoField},v_start,(6 + v_end)) else
 
Upvote 0 Downvote
Brian, looks good, but it's still pulling in the subsequent line of data.
Example, on the below record, it pulls Size = 64B, but when I set the field to "can grow", it also shows "Characteristics LUN" on the 2nd line. Since I will be exporting this to the client in excel, the 2nd line would show.

Really appreciate your helping out.


Details
----------------------------------------------
Requestor_Name - Shinall, Damon
Contact_Number - xx/xxx-xxx
Requestor_Email - John.Doe@gmail.com
Business_Segment - Segment8
Division - Portfolio Solutions
Initiative - Not Budgeted/Forecasted
ITG_BU - n/a
E:\STG_LDF - 80 GB
E:\STG_MDF - 185 GB
Physical - new
Rack - n/a
PhysicalVirtual - Physical
Domain - NWC
Environment - Dev
IP3 - n/a
Subnet - n/a
OS - Windows 2008
type - Database
HBA1 - n/a
HBA2 - n/a
Clustered - No
LUNQuantity - n/a
Size - 64GB
Characteristics LUN - EqualReadToWrite
YesSelected7 - numeric
YesSelected12 - numeric
YesSelected14 - If additional locations required, please attached document at checkout
 
Upvote 0 Downvote
i think brian's formula is working correctly but your data doesn't have another space until after LUN. I am going to guess that you should be able to take brian's formula and replace the " " with chr(13), as below.


whileprintingrecords;
numbervar v_start;
numbervar v_end;

v_start := instr({YourMemoField},"Size -");
v_end := instr(mid({YourMemoField}, v_start + 7),chr(13));

if {YourMemoField} like ["*Size -*"] then mid({YourMemoField},v_start,(6 + v_end)) else
 
Upvote 0 Downvote
I can see you're both on the right track, but I'm not quite there, and lack the skills to get there. :)
When I use fisheromacse's formula, all that is returned is "Size -".
 
Upvote 0 Downvote

I created a table in Access with one memo field, and pasted in your three records. My formula worked, but clearly there is a discrepancy in the way the records are stored between my database and yours.

Not sure how to resolve that, but my first thought is to create a formula in Crystal:

replace({YourMemoField}," ","$")

Then post the results of one of the records. Once we see where the spaces are actually falling out then it might give us some insight.

Nice idea fisheromacse, btw.
 
Upvote 0 Downvote

You guys are good. Is this what you're looking for Brian?

Service$Catalog$Request#$82824

Details$
----------------------------------------------
Requestor_Name$-$Marim,$Mark
Contact_Number$-$xxx/xxx-4453
Requestor_Email$-$John.Doe@xxxxx.com
Name$-$NA
ITG_BU$-$NA
Application_Owner$-$NA
CommentsandNotes$-$Please$Expand$\\jtcfscl3fs3\BIExtracts$on$\\jtcfscl3fs3\BIExtracts$by$15GB
Physical$-$Existing
ServerName$-$seesdg3
ServerLocationData_Center$-$Tampa$FL
RackLocation$-$na
PhysicalVirtual1$-$Physical
ServerEnvironment$-$Prod
ServerDomain$-$na
ServerIP$-$na
ServerSubnet$-$na
ServerOS$-$na
ServerType$-$Fileshare
HBAWWN1$-$na
HBAWWN2$-$na
ServerClustered$-$No
IfClustered$-$IF$SERVER$IS$CLUSTERED,$THE$FOLLOWING$INFORMATION$IS$REQUIRED
LUNidHostid$-$na
StorageGroup$-$na
Size$-$25GB
Target_size$-$40GB
ExpansionMethod$-$Unknown
YesSelected11$-$If$Additional$Expansions$Required,$Please$Attach$Document$At$Checkout

 
Upvote 0 Downvote

Yes, but I should have looked for both spaces and carriage returns:

replace(replace({YourMemoField},chr(13),"$")," ","%")

Also, what is your backend database?
 
Upvote 0 Downvote
This is odd ... it doesn't appear to see any carriage returns. ??

Service%Catalog%Request#%82824

Details%
----------------------------------------------
Requestor_Name%-%Marim,%Marcelo
Contact_Number%-%904/527-4453
Requestor_Email%-%Marcelo.Marim@lpsvcs.com
Name%-%NA
ITG_BU%-%NA
Application_Owner%-%NA
CommentsandNotes%-%Please%Expand%\\jtcfscl3fs3\BIExtracts%on%\\jtcfscl3fs3\BIExtracts%by%15GB
Physical%-%Existing
ServerName%-%jtcfscl3fs3
ServerLocationData_Center%-%Jacksonville%FL
RackLocation%-%na
PhysicalVirtual1%-%Physical
ServerEnvironment%-%Prod
ServerDomain%-%na
ServerIP%-%na
ServerSubnet%-%na
ServerOS%-%na
ServerType%-%Fileshare
HBAWWN1%-%na
HBAWWN2%-%na
ServerClustered%-%No
IfClustered%-%IF%SERVER%IS%CLUSTERED,%THE%FOLLOWING%INFORMATION%IS%REQUIRED
LUNidHostid%-%na
StorageGroup%-%na
Size%-%25GB
Target_size%-%40GB
ExpansionMethod%-%Unknown
YesSelected11%-%If%Additional%Expansions%Required,%Please%Attach%Document%At%Checkout

<A%href="
 
Upvote 0 Downvote

Still a little baffled as to what's going on, but this should work for you.

Create a new formula:

Code: 
//{@RedoText}

whileprintingrecords;
stringvar v_redo := "";
numbervar x := 1;

while x <= len({YourMemoField})

do
(if asc({YourMemoField}[x]) in [32 to 127]
then
v_redo := v_redo + {YourMemoField}[x] else v_redo;
x := x + 1;);

v_redo

That gets rid of all the debris. Then use the original formula, but base it on the new formula for input instead of the database field:

Code: 
whileprintingrecords;
 numbervar v_start;
 numbervar v_end;
 
v_start := instr({@RedoText},"Size -");
 v_end := instr(mid({@RedoText}, v_start + 7)," ");
 
if {@RedoText} like ["*Size -*"] then mid({YourMemoField},v_start,(6 + v_end)) else ""




 
Upvote 0 Downvote
Tried that Brian, but it's returning data other than the size data.
Here's the redo field:

Service Catalog Request# 82974Details ----------------------------------------------Requestor_Name - Djikounou, MensahContact_Number - 904/527-4021Requestor_Email - Mensah.Djikounou@lpsvcs.comBusiness_Segment - LPS DesktopDivision - Portfolio SolutionsName - NAITG_BU - #NAApplication_Owner - NACommentsandNotes - Please expand the mount point E:\PMNotes_ODS_03_ndf_01 in JTCDMARTCLNDE3A by 30 GB. Alert email is attached. Thanks.DR server: mndmartcl3sql1Physical - ExistingServerName - jtcdmartcl3sql1ServerLocationData_Center - Jacksonville FLRackLocation - NAPhysicalVirtual1 - PhysicalServerEnvironment - ProdServerDomain - NAServerIP - NAServerSubnet - NAServerOS - NAServerType - DatabaseHBAWWN1 - NAHBAWWN2 - NAServerClustered - YesIfClustered - IF SERVER IS CLUSTERED, THE FOLLOWING INFORMATION IS REQUIREDLUNidHostid - NAStorageGroup - NASize - 340Target_size - 370ExpansionMethod - UnknownYesSelected11 - If Additional Expansions Required, Please Attach Document At Checkout<A href=" target="blank">Click here to see Cat Request</A>

Here's the final formula result: idHostid - NA
 
Upvote 0 Downvote
Did you try fisheromacse suggestion to use this formula with Line feed chr(10) or vertical tab chr(11)

whileprintingrecords;
numbervar v_start;
numbervar v_end;

v_start := instr({YourMemoField},"Size -");
v_end := instr(mid({YourMemoField}, v_start + 7),chr(13));

if {YourMemoField} like ["*Size -*"] then mid({YourMemoField},v_start,(6 + v_end)) else ""


Ian
 
Upvote 0 Downvote

I agree with Ian - it almost has to be one of those additional characters. Unfortunately, since I can't replicate your data exactly I can't do any more testing - all the suggested solutions work at my end. Please post back with the final solution.
 
Upvote 0 Downvote
Got it! You guys are great to take the time to help me. I tried the above formula with chr(10) and it works perfectly. I'm not familiar with line feeds and such but I will be googling it shortly. Thanks again guys!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SarahSCPDAdmin
  • Locked
  • Question
Can I format data in a memo field? 1
Replies
5
Views
209
SarahSCPDAdmin
SarahSCPDAdmin
socalvelo
  • Locked
  • Question
Extract Phone Numbers from Text Field
Replies
0
Views
170
socalvelo
socalvelo
Cpreston
  • Locked
  • Question
Select Top 2 rows where field = 2,3,4 and 5
Replies
1
Views
182
Cpreston
Cpreston
mmck1964
  • Locked
  • Question
Using "OR" to select data
Replies
4
Views
317
mmck1964
mmck1964
jchewsmith
  • Locked
  • Question
Report filter to run report yeartodate through yesterday
Replies
0
Views
212
jchewsmith
jchewsmith

Part and Inventory Search

Sponsor

Back
Top