Sorting by Last Name 1

[faush]

I'm trying to sort some badges and for some reason, what was working before isn't anymore with the formula I'm using.

I need to sort by last name.

Examples of last names

John Doe
John A. Doe
John Allen Doe

Another example, I have one name, LYLE VAN BUER, who is being dropped in the "B's" instead of the "V's"

The sort formula I'm using is:

Mid ({BADGES.BDNAME},InStrRev({BADGES.BDNAME}," &quot +1 )

This worked before under 8.0 and now I'm on 8.5..don't know if that's part of the problem...am I missing something, or do I need an additional file for this?

Thank you,
Leslie

 

[FoxG]

Hi Leslie,

Your results are only to be expected unless you have a way of determining the start of the surname.

If surname can be double barreled with spaces between there is no way to determine if it was supposed to be middlename or part of the surname!

The only way to do this is by holding the name either as
3 separate fields or
one field in the format Surname, Firstname Midlenames

I can not see any way you can overcome this issue with the name in the order you have it now.

Geoff

 

[Darrick]

Leslie,

Can you show some of the data in the {BADGES.BDNAME} field? I would love to see the LYLE VAN BUER field as well.

According to your formula, it should have found the first space in the field not the second one, that is why I want to see how this name is formatted in the field.

Darrick
darrick3@yahoo.com

 

[FoxG]

Hi Leslie/Darrick,

Is InStrRev a new feature to CR8 ??

I have not seen it in CR7.

Geoff

 

[Darrick]

Yes Geoff, it is a new feature in CR8.

Leslie,

Geoff did point out something that I didn't see. You need to just use the InStr function instead of the InStrRev function.

Does the data ever have a middle name or a middle initial?

Darrick
darrick3@yahoo.com

 

[faush]

The data does have a middle name/initial on occasion. Here is a sample of the data, straight out of the AS/400 file Crystal accesses. In the meantime, I'll try using the InStr, not the reverse and see what results I get. Thanks for your help.

DANA BARLOW
KELLY CHANG
JO SWINNEY
MARGARITA DAVIS
LEO DEVINE
JOHN DI MEO
MARK FREEDMAN
BRUCE FRIEDLAND
SAM LI
SHAWN LOFTUS
LEN LUBARSKY
STEPHEN ROOK
MATT SILVERSTEIN
LYLE VAN BUER
YIN WANG

 

[FoxG]

Hi,

InStr will still not work if any of your you names include middle names. If this is the case you will still not know if the second string is a middle name or a double barelled surname!!

Geoff

 

[Darrick]

Leslie,

Is it safe to say that if you have a middle initial in the name that it is ONLY a middle initial, not a middle name? We need to find some constant in order for Crystal to find the correct data.

Geoff,

InStr will work, but instead of grouping the middle initial with the first name, it will now group it with the last name.

Darrick
darrick3@yahoo.colm

 

[faush]

Yes, it is safe to say that.

Thanks for everyone's help on this!

 

[FoxG]

Hi Leslie,

local numbervar p = instr({BADGES.BDNAME}," &quot
local stringvar surname := Mid ({BADGES.BDNAME},p+1)
p := instr(surname," " if p = 2 surname := mid(surname,3);
p := instr(surname," " if p = 2 surname := mid(surname,3);
p := instr(surname," " if p = 2 surname := mid(surname,3);
surname

This formula will cater for your input field,
provided that...
all middle names are initials only (max of 3 initials!)

Geoff

 

[faush]

Thanks for all your help, I'll use this formula next time I have to run these and see what happens.

Thanks!!