Comparison between two Data Sources Advice/Help

[chrisppppp]

Crystal Reports 2008

Hi

I've received an Excel spreadsheet of names (first and last names in different cells) and i have to compare this spreadsheet to the current list of clients on our database where there is a match.

The best hope is by last name, i know this isn't a direct match cause multiple people can have the same last name, but that is really all i've got to go off. The name field in our data base is just one long string (ie Mr Joe Bloggs Esq). I'm totally at a loss of how to compare these two sperate datasources cause there is absolutely nothing that uniquely links them.

Does anybody have an advice or have any help with how i could compare these two data sources using crystal?

I've thought about creating a table on a seperate SQL server and populating it with the data from the spreadsheet but as i said i've got nothing to link off of. I've thought about sub reports by still don't see how this could work.

Any help or advice would be greatly appreciated.

Cheers

 

[IanWaterman]

Crystal is not really the right tool for this.

Is the last name always element 3 in your name field?

How big is your excel spreadsheet. (No. of rows).

Ian

 

[chrisppppp]

No not necessarily, examples could be:

Mr J Smith
Mr John Smith
Mr John Smith-Cooper
Mr John Smith Esq
Mr John Smith MD

Its all random so difficult to really put any logic into it.

The excel spreadsheet is about 7,000 rows so nothing huge.

I was thinking about just exporting all of our clients into excel, and then comparing them using that. I looked in the help and found this formula:

=IF(ISNUMBER(FIND("v",A2)),"OK", "Not OK")

This would be to find any "v" in cell A2. How ever this is case sensitive where i would need it to be case insensitive. What would you think to that?

Do you have any other ideas of how i could go about this?

(Sorry just realised that i may have posted this in the incorrect forum really)

 

[IanWaterman]

To help with your excel export you could use the crystal function split

split({table.namefield}," ")[3]

This creates an array and the above will output third element, which in your list above is generaly the surname.

Ian

 

[chrisppppp]

See that would be useful but unfortunatly as i said the third element isn't always the last name, my fault really i should have given a definative list of possible names.

Are there any other ideas of how i could do this? This is a real mess but it doesn't have to be perfect, just have to come up with a suitable comparison so i can then send off a list of suspects.

 

[IanWaterman]

Break down name into all elements and export

create as many formula as there are max no. of elements.

@El1
split({table.namefield}," ")[1]

@El2
split({table.namefield}," ")[2]

@El3
split({table.namefield}," ")[3]

etc

Export to excel and then do your comparison there.

Ian

 

[lbass]

You could try adding the excel sheet and the table to the same report without linking, and then in the record selection formula try:

{xls.lastname} in {database.fullname}

You haven't really said what the comparison is intended to be, i.e., what is the report intended to show? How will it be used?

Also, you haven't said clarified whether one of the datasources contains all of the names, with names missing in the other, or whether each datasource could have names missing from the other.

-LB

 

[chrisppppp]

Cheers for the response lbass. The report is intended to show which clients names (from the database) match the names on the excel spreadsheet. These will be highlighted as suspects and then sent away. The report doesn't have to be perfect, same surname will do for a start.

The database is the list of all our current clients, and the excel spreadsheet is a full list of suspects which we need to highlight to show if they appear in our database. So either datasource may have names missing from one another.

When you say place this in the record selection, do you mean just directly into the formula editor as above? (obviously with my tables and fields substituting above)

 

[chrisppppp]

I've tried your way lbass and it seems to work ok, only problem is, say i've got a surname of "Ali" on the excel list, in the crystal report it will come up with matches on names like "alison" or "aliee" because the string "Ali" is in there. Would it just be best to change the formula to:

{xls.lastname} = {database.fullname}

Or can you think of any better methods?

 

[lbass]

Try:

" "+{xls.lastname}+" " in {database.fullname}+" "

-LB

 

[chrisppppp]

lbass that seems to have worked perfectly, definately got something i can manipulate and play with now. If you don't mind could you explain to me how that line of code works for future reference?

Thanks again for your help

 

[lbass]

It is just requiring a space before and after the last name in the spreadsheet to be present in the database name field. If the database name field is the last element of the fullname, there is no space, so the formula adds one on. This has the effect of requiring that the entire last name (as set off by spaces) be present as an element in the full name. This is not great code in the sense that I don't think it will pass to the SQL.

-LB