[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4072
  • Last Modified:

Crystal Reports: Extract Data From String with Delimiters

I have a string of text data CustomerName:Job Number:SubJobNumber
Example ACME:25001:25001-001

I need to make a formula to extract each piece to its own field IE

Everything is against:
{Customer.FullName}

Formula 1:
All Characters left of the first :
Formula 2:
All Characters between the first and last :
Formula 3:
All Characters to the right of the last :

Formula 4 (Most Important)
Taking the Last 3 numbers if they occur after a - (not everything has subjobs) and being able to sort that as an integer.
0
knausscpa
Asked:
knausscpa
  • 5
  • 4
1 Solution
 
James0628Commented:
Will the field _always_ contain two ":"s?

 If so (and assuming that you don't want the substrings to include any of the ":"s), then the first 3 formulas are quite simple.  The 4th is a bit trickier.

// formula 1
Split ({Customer.FullName}, ":") [ 1 ]


// formula 2
Split ({Customer.FullName}, ":") [ 2 ]


// formula 3
Split ({Customer.FullName}, ":") [ 3 ]


// formula 4
Local StringVar t;

t := Split ({Customer.FullName}, ":") [ 3 ];

if InStrRev (t, "-") > 0 then
  Val (Mid (t, InStrRev (t, "-") + 1))
else
  0


 If there could be fields that do not contain the two ":"s (say, something like "ACME:25001"), that complicates things a bit.  The formulas above will produce errors if there aren't two (or more) ":"s in a string.

 James
0
 
knausscpaAuthor Commented:
Not all will have the 2nd : I want to have it as an integer so that I can use it to filter by not ISNULL as I managed to get the things separated but was unable to use it as a filtering tool.
0
 
James0628Commented:
 > I want to have it as an integer so that I
 > can use it to filter by not ISNULL ...

 I'm not sure what you mean by that.  The CR IsNull function is normally used on fields, or maybe parameters.  Whether the end of the string is an integer or not has nothing to do with being null.

 But that brings up a point.  If {Customer.FullName} could be null, you may need to handle that.  The simplest thing would be to go into File > "Report Options" and check the "Convert Database NULL Values to Default" option.  Then if the field is null, these formulas will see it as an empty string instead.  If you don't want to include records where the field is null, you can add (not IsNull ({Customer.FullName})) to the record selection.

 Ignoring the null stuff for the moment:

 formula 1 should always work.  If there is no ":" at all, it will just return the entire string.


// formula 2
if UBound (Split ({Customer.FullName}, ":")) > 1 then
  Split ({Customer.FullName}, ":") [ 2 ]


// formula 3
if UBound (Split ({Customer.FullName}, ":")) > 2 then
  Split ({Customer.FullName}, ":") [ 3 ]


// formula 4
Local StringVar t;

if UBound (Split ({Customer.FullName}, ":")) > 2 then
(
  t := Split ({Customer.FullName}, ":") [ 3 ];

  if InStrRev (t, "-") > 0 then
    Val (Mid (t, InStrRev (t, "-") + 1))
  else
    0
)
else
  0


 In case you haven't tried it yet, formula 4 should return any digits after the last "-" in the 3rd segment of the field as a number.  For 25001-001, you'd get 1.  I used Val to simplify things a bit.  It will convert any digits at the start of the specified string to numbers, so if you have something like 25001-3A4, it will see 3A4 and give you 3.  It saves having to check to make sure that the characters after the "-" are all numeric.

 If these formulas won't give you what you need, can you explain why?

 James
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
knausscpaAuthor Commented:
The real problem is I need to export these to a CSV file and later take out special characters so that it can be imported into another program reading CSV files.  I need to separate Job Number | SubJob# and show only entries that have a job number.  I am using formulas below.  I would like to know how to nest them (or if crystal handles that differently) how thats done rather than running a formula on a formula etc. Names are strange it is still work in progress.  

Problem the report generates and shows jobs without subjobs and I cant filter out NOT ISNULL({@Trim}) I can't figure out how to turn that data into a number so I can filter out blanks.
@Trim
MID({@SubJob}, 7, len({@SubJob}) - 4)

@SubJob
Right({@FullJob},(Length({@FullJob})-(Instr({@FullJob},chr(58)))))

@FullJob
RIGHT({Customer.FullName},(Length({Customer.FullName})-(Instr({Customer.FullName},chr(58)))))

Open in new window

0
 
knausscpaAuthor Commented:
Your formula 4 works but I need it to display as 3 fixed 3 digits with leading zeros IE 1 = 001, 11 = 011, 111 =111
0
 
knausscpaAuthor Commented:
I used your formula 4 and (below) how would I combine them?
right('000' + totext({@SubJobSuf},0,''),3)

Open in new window

0
 
mlmccCommented:
You can also use a format string like

totext({@SubJobSuf}, '000')

mlmcc
0
 
James0628Commented:
Something like your formula (Right ("000" + etc.) would work, but the simplest thing would be to use a format when you convert the number to a string, as mlmcc suggested.


 > Problem the report generates and shows jobs without
 > subjobs and I cant filter out NOT ISNULL({@Trim}) I can't
 > figure out how to turn that data into a number so I can
 > filter out blanks.

 Checking for a null subjob is one thing, but hopefully that's not really required.  If you're simply trying to skip the records without subjobs, and if 0 is not a valid subjob, then using formula 4 and checking for a value of 0 or "000" should be all you need.

 One other thought.  If you're trying to select only the records that have a subjob, and you have a lot of records to deal with, and a significant portion of them do not have subjobs, it might be worthwhile to see if the subjob is being checked on the db server (assuming that you're using a db server) or by CR.  If CR is doing the checking, which I suspect would probably be the case for something like this, that means that the server will be sending all of the records to your report, and then CR will filter the records and discard the ones with no subjob.  If that's the case, there may be ways that you can get the server to do it.  It depends on your db, etc.  Just something to keep in mind, especially if the report is taking a long time.  If the report runs quickly, or "quickly enough", then it may be a moot point.

 James
0
 
knausscpaAuthor Commented:
All records are being pulled to the report and then filtered its a Quickbooks enterprise 10 database.  And yes I am just trying to get jobs that have a subjob.  I have everything working fine now, was curious on how to combine the 2 formulas for general use it seems like running a formula on a formula rather than embedding is a lot of crystal red tape.
0
 
James0628Commented:
Glad to hear that things are working.


 When you say "combine the 2 formulas", do you mean something like the following?

right('000' + totext({@SubJobSuf},0,''),3)


 If @SubJobSuf is something like my "formula 4", and if you don't need that formula to produce a numeric value, you could just change that formula to produce a string result, instead of a number.  For example, the middle section of "formula 4" could be:

  if InStrRev (t, "-") > 0 then
    CStr (Val (Mid (t, InStrRev (t, "-") + 1)), "000")
  else
    "000"


 Actually, if the part after the "-",  if any, would always be 3 digits (eg. something like "001", and not "01" or "1"), you could skip the conversion to a number and just use those characters.

  if InStrRev (t, "-") > 0 then
    Mid (t, InStrRev (t, "-") + 1)  // A string with everything after the "-"
  else
    "000"


 You'd also need to change the 0 at the end of the formula to "000" (a formula can only produce one type of result).

 James
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now