| Author |
Message |
macaco
Newbie
Joined: 15 Apr 2009
Location: United States
Online Status: Offline
Posts: 4
|
  
Topic: Capturing only part of text in a memo field
Posted: 15 Apr 2009 at 8:04am |
I am trying to capture text data from a memo field type in crystal. The problem is that all the formatting text is returned in the field as text data as well and makes my results unusable. I was researching this topic and found a similar post that provided the following to use to trim the data :
NumberVar FollowUpsLoc;
FollowUpsLoc := Instr({table.fieldname}, 'FOLLOWUPS:');
IF FollowUpsLoc > 0 then Mid ({Table.FieldName} , Instr(FollowUpsLoc, {Table.FieldName} ,chr(13))+1)
Here is a sample of the data i am returning and the bold portion is what i would like to return
<HTML><HEAD><META NAME="GENERATOR" Content="Microsoft DHTML Editing Control"><TITLE></TITLE></HEAD><BODY><DIV>Shortage - Recording - Okemos</DIV></BODY></HTML>
Can anyone offer any help on this please?
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Apr 2009 at 8:55am |
If your text is always bracketed by "<DIV>" and "</DIV>" and these strings never appear more than once in the total string then the following formula should extract the text body:
Of course someone else may have a more elegant solution...
|
| IP Logged |
|
macaco
Newbie
Joined: 15 Apr 2009
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 15 Apr 2009 at 9:15am |
|
That seems to have fixed the issue and was the path i was planning on taking. Thank you for the help! It seems like the <DIV> is there however i'm not sure it will always be there. I may replace one of the <Div> with "Shortage" as they have been instructed to preface all notes with shortage in this process. Thanks again
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Apr 2009 at 9:45am |
Glad it is working.
If you change this to looking for "shortage" you will have to address the "+5" portions of the formula as well. If you want to include the word shortage then just remove those form the formula, if you do not wan it to appear change it to + 8
FYI you can also add an if then statement to make sure they all have the "<Div>" to help you weed the items out
as in:
else "ERROR CONVERTING TEXT"
Good luck. Edited by DBlank - 15 Apr 2009 at 9:46am
|
| IP Logged |
|
macaco
Newbie
Joined: 15 Apr 2009
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 20 Apr 2009 at 6:41am |
I am now running into a new issue with this. I am getting "string length is less then 0 or not an integer.
Here is my formula:
if instr({SE_Acct_TrialBalance;1.NotesOnFile}, '<DIV>') > 0 then mid({SE_Acct_TrialBalance;1.NotesOnFile},instr({SE_Acct_TrialBalance;1.NotesOnFile},"<DIV>")+5, (instr({SE_Acct_TrialBalance;1.NotesOnFile},"</DIV>"))-(instr({SE_Acct_TrialBalance;1.NotesOnFile},"<DIV>")+5)) else {SE_Acct_TrialBalance;1.NotesOnFile} |
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Apr 2009 at 7:06am |
without the if statement it works ok? Not sure why it is not working but try and add extra parenthesis aroun the entire inside formula like so...
if instr({SE_Acct_TrialBalance;1.NotesOnFile}, "<DIV>") > 0 then (mid({SE_Acct_TrialBalance;1.NotesOnFile},instr({SE_Acct_TrialBalance;1.NotesOnFile},"<DIV>")+5, (instr({SE_Acct_TrialBalance;1.NotesOnFile},"</DIV>"))-(instr({SE_Acct_TrialBalance;1.NotesOnFile},"<DIV>")+5))) else {SE_Acct_TrialBalance;1.NotesOnFile}
|
| IP Logged |
|
macaco
Newbie
Joined: 15 Apr 2009
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 23 Apr 2009 at 6:33am |
|
it does not work right without the if statement and i keep getting the error about the results not being > 0 or an integer. The data is from a memo field and the results typically are not integers so do you have any other suggestions?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Apr 2009 at 8:28am |
I still think the code is correct as I have tested it on a sample data. It is choking on an the interger that it is looking for to find where to start or stop within the string. My guess is that you might have some records where there is a "</DIV>" before the first "<DIV>".
To test this try and preclude that in the beginning of the if statement. This will skip any records where the DIV does not exist and the /DIV is before the DIV:
if
instr({SE_Acct_TrialBalance;1.NotesOnFile}, '<DIV>') > 0
and
instr({SE_Acct_TrialBalance;1.NotesOnFile},"</DIV>") > instr({SE_Acct_TrialBalance;1.NotesOnFile}, '<DIV>')
then
mid({SE_Acct_TrialBalance;1.NotesOnFile},instr({SE_Acct_TrialBalance;1.NotesOnFile},"<DIV>")+5,
(instr({SE_Acct_TrialBalance;1.NotesOnFile},"</DIV>"))-(instr({SE_Acct_TrialBalance;1.NotesOnFile},"<DIV>")+5))
else {SE_Acct_TrialBalance;1.NotesOnFile}
|
| IP Logged |
|
JohnT
Groupie
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
|
Posted: 23 Apr 2009 at 8:38am |
|
Are you sure you are using the same data when you test with the if and without ? You will get that message if the <DIV> and </DIV> are there but there is nothing between the two. This is because the length parameter on the MID will be zero. Do you have multiple sets of data you are testing with ?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Apr 2009 at 9:05am |
Hi John, Good point that this if does not handle it if no text exists beteween the 2 items. I do not have multiple data sets to do this as I do not have any of the data that macaco is working with. The error can occur, as you noted, if there is no text between the two items or if there is no "/DIV" or if there "/DIV" appears before the first "DIV". Below adjusts it to handle all of these possibities.
if
instr({SE_Acct_TrialBalance;1.NotesOnFile}, '<DIV>') > 0
and
instr({SE_Acct_TrialBalance;1.NotesOnFile},"</DIV>") > (instr({SE_Acct_TrialBalance;1.NotesOnFile}, '<DIV>')+6)
then
mid({SE_Acct_TrialBalance;1.NotesOnFile},instr({SE_Acct_TrialBalance;1.NotesOnFile},"<DIV>")+5,
(instr({SE_Acct_TrialBalance;1.NotesOnFile},"</DIV>"))-(instr({SE_Acct_TrialBalance;1.NotesOnFile},"<DIV>")+5))
else {SE_Acct_TrialBalance;1.NotesOnFile}
The if portion was added to handle the possiblility of exceptions to what the percieved standard of DIV - text - /DIV. Since there does not appear to be any other logically standard in the text string I would recommend adjusting the if portion to handle more possible exceptions to tease out items that it does not work for. From there you can analyze these exceptions and see if adding another if then statement could work for these.
|
| IP Logged |
|
|
|
