Duplicate Names requiring Last Initial 2

[Crystalyzer]

Hi all!

I am using CR 9 with a CSV file as my database. I have the following data:

FirstName LastName PhExtension
Nancy Smith 5551
Nancy Jones 5875
Mary Poppins 5670
Joe Schmoe 5571

My report will only display FirstName and Extension, however I need a way to differentiate between the Nancy's and have chosen to include the initial of their last names. I know the string formulas to give me what I want, however I don't know quite how to compare the first names. The report can either be sorted (by the users) by FirstName OR Extension so check prev or next fields won't work when sorted by Extension and I will need the initial of the last name to appear all the time. Is there some other way to check for duplications?

Thanks for any help or guidance.
-Lloyd

 

[rosemaryl]

Dear Crysatlyzer,

Create a formula:

//Grouper Formula
If {?SortParam} = 'EXT"
then {Table.PHExtension}
else
{Table.FirstName} + ' ' + {Table.LastName}

Now,group on this field, but when you insert the group, customize the group using a formula

If {?SortParam} = 'EXT"
then {Table.PHExtension}
else
{Table.First Name}

Now, it will be grouped correctly and will only display first names.

If you think, but I don't want it grouped ... just suppress the details and the group footer and it will look like a detail report. Just move all the fields up to the group header that you had in the detail section.

Regards,
ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Crystalyzer]

Ro,

thanks for the speedy respons, however, I'm not having a sorting issue. Rather, I need a way to determine when a first name is duplicated and then if it is attache the last initial. I have the string concatenations worked out, just not the method by which I could compare the names. Again, because the list can be sorted either by extension OR by First Name the prev and next field options arent' going to do it for me.



Thanks and best regards,
-Lloyd

 

[rosemaryl]

Dear Lloyd,

Did you actually try what I stated?

You originally stated:

"My report will only display FirstName and Extension, however I need a way to differentiate between the Nancy's"

You stated that you decided to concatenate the last initial due to the fact that you needed a way to differentiate. I assumed that your optimum result would be to display the first name only. My solution does that, while placing them in order of their last name.

Since that now appears not to be the case... you can just add to my formula for the group name customization above:

If {?SortParam} = 'EXT"
then {Table.PHExtension}
else
{Table.First Name} & ' ' & {Table.LastName} [1]

That will concatenate the first initial of the last name.

By doing it the way I suggested, you will get the records sorted in order of first name, last name
or by extension.

Have you thought that at some point in time it is possible that not only will the first name be duplicated, but also the first initial of the last name?

I don't actually understand why you aren't displaying their full name ... to do that just remove the [1] from my group name customization formula above.

regards,
ro




Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Crystalyzer]

Ro,

I think we're having a communication break down (because I am not being very clear). I only want the last initial IF the first names are the same otherwise I don't want it at all.



Thanks and best regards,
-Lloyd

 

[rosemaryl]

Dear Lloyd,

Ahhhh, now that is a horse of a different color and I am sorry I misunderstood. It would be helpful if I knew more of your formulas, but I just built a report using my formulas I did above ... and instead of customizing the groupname as I indicated before ... I did it like this:

If {?Sort} = 'EXT'
then {Clients.Ext.}
else
{Clients.First Name}

The Group tree will either display extension or first name.

On the report don't place the group name field in the group but instead this formula:

Try something like this (I am using my own tables, change table.field names and parameter names as required:

if {?SORT} = 'EXT'
then {Clients.Ext.}
else
if onfirstrecord
then
{Clients.First Name}
else if not onfirstrecord
then
if previous({Clients.First Name})
= {Clients.First Name}
then {Clients.First Name} & ' ' & {Clients.Last Name} [1]
else
{Clients.First Name}

Regards,
ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Crystalyzer]

Ro,

I REALLY appreciate all your efforts here. My data is VERY simple as stated in my first post and the output is pretty simple too. Your most recent solution will give me the last initial IF and ONLY if the list is sorted by first name but my users can sort it by extension in which case your example will not input the initial which is my dilema.

I thought of inserting a sub report with a shared array variable in the report header and supressing it to check each name in the array and if it appears more than once include the last initial. This is talk in the air right now since I am having some trouble thinking it through.



Thanks and best regards,
-Lloyd

 

[lbass]

Lloyd, I just worked out that solution...

Insert a subreport in which you insert a group on {table.firstname}. Then create two formulas in the subreport:

//{@dupes} to be placed in the subreport group (first name) footer:
whileprintingrecords;
shared stringvar x;

if count({table.firstname},{table.firstname}) > 1 then
x := x + {table.firstname} + ", ";

//{@displdupes} to be placed in the subreport report footer:
whileprintingrecords;
shared stringvar x;
left(x,len(x)-2)

Suppress all sections of the subreport and place it in the report header of the main report.

Then in the main report, create the following formula to be placed in the detail section instead of the {table.firstname field}:

whileprintingrecords;
shared stringvar x;

if instr(x,{table.firstname}) > 0 then
{table.firstname} +" "+ left({table.lastname},1) + "." else
{table.firstname}

You can use a parameter as suggested earlier in a formula to group on and get the correct sort.

-LB

 

[Crystalyzer]

lbass,

Your solution worked like a charm with one minor exception that I can live with if need be. The name "Chris" is deemed to be a duplicate because the name "Christine" exists. Is there any way around this?

Thanks to both of you lbass and ro for helping me out!





Thanks and best regards,
-Lloyd

 

[lbass]

I can't really test this, but try the following:

In the subreport, change {@displdupes} to;
whileprintingrecords;
shared stringvar x;

Then in the main report, change the test formula to:

whileprintingrecords;
shared stringvar x;

if instr(x,{table.firstname}+",") > 0 then
{table.firstname} +" "+ left({table.lastname},1) + "." else
{table.firstname}

-LB

 

[Crystalyzer]

I think the issue is with the accumulate formula. The Name "Chris" is satisfying the conditional count

if count({table.firstname},{table.firstname}) > 1

because it is present in "Chris" and "Christine" and being placed in the x variable.

It's a livable situation but I guess the real answer would be if the conditional count formula was something of and exact match ONLY without extra characters.

Thanks again for all your help.

Thanks and best regards,
-Lloyd

 

[lbass]

I don't think that Chris and Christine would appear in the same group. If you reveal the report footer of the subreport, is Chris or Christine in the array? If "Chris," is in the array, then "Christine," in the body of the report will not match "Chris,". If "Christine," is in the array, then "Chris," should not match either. Did you make the changes I suggested? I was pretty certain it would work if the comma was included in the test to delimit the name.

-LB

 

[Crystalyzer]

LB,

Both Chris and Christine are in the sub report. By including the comma in the main report we're just adding characters to both sides (sub report and main report).



Thanks and best regards,
-Lloyd

 

[Crystalyzer]

LB,

My apologies. I went back to the actual data and found that Chris IS REPEATED (and I thought I knew everyone here!).

Egg is squarely on my face! Thanks for all your help AGAIN!!

Thanks and best regards,
-Lloyd