Please help me with InStr, Replace, or whatever I should use!

[mns117]

Please Help...

Heres what I am trying to do... there is an author field in my database. There can be multiple author names in the field, but the format is always the same...

Basically it looks like this:
J.T. Smith A.; B.A. Jones B., etc.

I need to do 2 things... On one report I want the author name to look like this

Smith J.T. A., et al.

(Basically showing only the first author, last name first, then initials)

On another report I want the author name to look like this:
Smith J.T. A., B.A. Jones B., etc.


Can anyone think of a way that I can do this... Thanks so much

 

[synapsevampire]

Very poorly managed data, go swat the DBA as this will likely present many problems later.

This doesn't even seem to have a unique qualifier for when a name ends (a semicolon seems to end the 1st, and a comma the second), so this will be tricky, unless your example data is bad. And in the desired output you don't seem to make the last name first for Jones...very complicated rules you seem to have here.

And considering that you may have last names like:

De Arta

So even if you elect to use a formula like:

whileprintingrecords;
global stringvar first;
global stringvar last;
global stringvar middle;
first := left({Customer.Customer Name}, instr({Customer.Customer Name}," &quot-1);
middle := mid({Customer.Customer Name}, instr({Customer.Customer Name}," &quot+1);
last := left(middle, instr(middle," &quot-1);
middle := mid(middle, instr(middle," &quot+1);

To strip out the first, last and middle names, you're going to have bad data in some cases.

Hope some of this might help, I used to write list cleaning routines, and this is a prime candidate. I'd rip the data out of this horrid design and create a real table.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mns117]

thanks for responding so quickly synapsevampire... Your right the data is terrible, however since it is imported from an outside source constantly there is no other way to keep it (with out developing a complexing algorithm which is impossible in rapid application development.)

Heres my question, how do I display that on a report? Where should I build it? I just started using Crystal Reports yesterday, however I am very familiar with other dev. environments!

 

[Naith]

This problem is a heck of a deep end for you to be chucked in at with no Crystal experience. However, it's possible - just a lot of work, which you could have been saved had your database not been in such a state.

I don't usually side with the "Hurt The DBA" empathisers, because if we all beat down on a DBA everytime he made a mistake, I'd be in traction every week. But in this case, beat him, and beat him good. Find out if he can insert a couple of extra columns as Author2 and 3, and split the data between them.

If he can't - or won't - then let's approach it like this:

Create a new formula. (Insert menu / Field Object / Right Click 'Formula Fields', and select New.)

Paste the following into your new formula text window:

WhilePrintingRecords;
StringVar Array MyArray := [""];
StringVar Array MyArra2 := [""];
StringVar OldName := {Author.Name};
StringVar Multiple := "";
StringVar NewName;

MyArray := Split (OldName, " ");

If InStr(OldName,';') < 1
Then
If UBound(MyArray) < 1 
Then ''
Else
If UpperCase(StrReverse(Mid(StrReverse(OldName),1,Instr(StrReverse(OldName),' ')-1))) in ['JR.','JR','SR','I','II','III']
Then 
NewName := MyArray[UBound(MyArray)-1] + ' ' + MyArray[UBound(MyArray)] + ', ' + 
Mid(OldName,1,(Length(OldName) - Length(MyArray[UBound(MyArray)-1]) - Length(MyArray[UBound(MyArray)])-2))
Else
NewName := MyArray[UBound(MyArray)] + ', ' + Mid(OldName,1,(Length(OldName) - Instr(StrReverse(OldName),' ')))
Else
Multiple := Mid(OldName,1,InStr(OldName,';')-1);

If Length(Multiple) > 0
Then
MyArra2 := Split (Multiple, &quot; &quot;)
Else
MyArra2 := [&quot;&quot;];

If Length(Multiple) > 0
Then
NewName := MyArra2[UBound(MyArra2)-1] + ', ' + 
Mid(Multiple,1,(Length(Multiple) - Length(MyArra2[UBound(MyArra2)-1]) - Length(MyArra2[UBound(MyArra2)])-2)) + ' ' + MyArra2[UBound(MyArra2)] + ', et al';

NewName;

I've made the assumption that if you only have one author, then you won't have the 'A.', 'B.' suffixes stuck on the end of the authors name. But if that assumption's incorrect, say so. Also, you should be aware that this formula will let 'two word' surnames like SynapseVampire mentioned before slip through the net. (I can't think how to capture those). But, most other names and title suffixes should make it.

This formula only works with CR v8+, and will only address your first report - the one where you want to capture the first author only.

For the other report, you just use the database field directly from the database, instead of using a formula like the one above.

Good luck with your reports,

Naith

 

[Naith]

I misread what you're doing for the 2nd report. You can't use the field direct from the database. But you can use a loop to cycle through the formula I already gave you, til you run out of authors in a field. Check out the looping functionality in F1. The syntax is like it would be for VB, and many other 3GLs, so if you're from a coding background, it should already be like sucking eggs for you.

Naith

 

[Reebo99]

Naith,

Are you trying to say programmers suck eggs? Reebo
Scotland (Raining)

 

[Naith]

You've never heard that expression?

 

[Reebo99]

Naith,

Yes, but only when a granny was involved. Reebo
Scotland (Raining)

 

[mns117]

How would I deal with this error...

String length is less than 0 or not an integer.

Thanks Mike

 

[Naith]

You might have some instances where the {Author.Name} field doesn't contain any data.

If this may be the case, insert this check;

where the formula says

MyArray := Split (OldName, " ");

replace it with:

If Not IsNull({Author.Name}) and
Length(Trim(OldName)) > 0
Then
MyArray := Split (OldName, " ")
Else MyArray := "";

If Not IsNull({Author.Name}) and
Length(Trim(OldName)) > 0
Then

This should smooth out your problem, and report any fields without a name as "". But, if you're still getting a little drama after making this change, can you reverse the change I've just given you and replace the line:

StringVar OldName := {Author.Name};

with

StringVar OldName := "Sir Arthur Conan Doyle A.; Some Other Guy B."

If you're still getting the error, let me know.

Thanks,

Naith