Compare Part of Phone Number in one DB to another DB 1

[dhennekens]

I need to find out how to get a location based on phone number called.

The first database has all the phone call info like calling party, called party, time, date, length, etc. The second database will have area code, prefix, city, state, etc.

Based on the CALLEDPARTY which will look like this "910005551234" I need to compare the 000 and 555 to the area code and prefix (respectively) in the other database and return the ({City}+", "+{State}) to one field. So Area code 000 is in Iowa and prefix 555 is in Des Moines. So the formula would return "Des Moines, IA".

I am very new to Crystal Reports and any help would be much appreciated. Thanks

 

[synapsevampire]

This would be better served on the databse side rather than crystal.

And not knowing your software version makes it difficult to advise you well, please remember to post the basics.

If done in crystal, you'll need to use advanced functionality, such as a custom SQL in an Add Command, or by using subreports and creating formulas to join to the otehr table with.

-k

 

[lbass]

You could try the subreport approach, where you place a formula using the second database in the subreport detail section:

{table.city}+", "+{table.state}

In the main report, create two formulas:

//{@areacode}:
mid({table.calledparty},3,3)

//{@prefix}:
mid({table.calledparty},6,3)

You could insert a group based on the area code and prefix by using:

mid({table.calledparty},3,6)

Then you could place the subreport in the group header for this group, and link the subreport to the main report by choosing the main report formula {@areacode} to link to the subreport {table.areacode} and then choosing {@prefix} to link to {table.prefix}.

This assumes that the calledparty field is never null and that it is always in the same format, with two digits preceding the ones of interest.

-LB

 

[dhennekens]

Sorry. I neglected to mention I am using version 9.

 

[dhennekens]

lbass,

Thanks for your reply. So far I can display the area code and prefix portions perfectly. However, I am not seeing how to link the area code and prefix to the subreport.

Also I don't have the second database yet. We haven't decided which one to purchase. I downloaded a demo portion of one of them. I see it going one of two ways. The database could reference the State in a different table or each record could have a field with the state in it.

Would that change how the formulas are linked?

Thanks

 

[lbass]

Does the demo database have a field for prefix and a field for area code? If they are from different tables, is there a field that links the two? Once you have done that, you should be able to follow the steps above. If you have duplicate data in the subreport, group on the formula and display only the group footer where you have dragged the formula.

To do the linking, go to edit->subreport links and for each formula, move the formula from the main report to the right and then in the bottom right of the screen, use the dropdown choose the field you want to link to.

-LB

 

[dhennekens]

I am sure I'm missing something.

Lets Say I dial the number 917897770000

Here is an example of what's in the second database

987 222 Dallas TX
654 333 Norwalk OH
321 444 Chicago IL
123 555 Charlston VA
456 666 Denver CO
789 777 Miami FL

I should get back

917897770000 Miami, FL .05
(group sum) Total .05

Instead I get
(record should only be one of each call)
917897770000 , .05
917897770000 , .05
917897770000 , .05
917897770000 , .05
917897770000 , .05
917897770000 Miami, FL .05
(group sum) Total .30


there is other misc info I get like time, date and duration but this is what pertains to my problem.

Some of the formulas i am using. Call Data is the number called.

@areacodeprefix
(mid({CallData.Call Data},2,3))+(mid({CallData.Call Data},5,3))

@location
{@Prefix-City}+", "+{@AreaCode-State}

@areacode
(mid({CallData.Call Data},2,3))

@areacode-state
If ({@AreaCode}={Areacodes.AreaCode}) Then {Areacodes.State}

@prefix
(mid({CallData.Call Data},5,3))

@prefix-city
If ({@Prefix}={Areacodes.Prefix}) Then {Areacodes.City}

Thanks

 

[lbass]

The second data base is only for retrieving the city and state and should not create duplicate values in the main report. You should only be using the second database in the subreport, and then linking the subreport to the main report as suggested earlier.

Your mid formulas are off by one value in each case, assuming numbers are always preceded by "91".

If you group on {@areacodeprefix} in the main report, you can place the subreport in the group header. Or you could place it in the detail section, I think, if you had to.

You should not need any formulas like {@areacodestate} and {@prefixcity} if you are linking the subreport correctly. They wouldn't work anyway unless you have added the second database into the main report and linked to it. If you are able to do this, you could. Please let me know what you have actually done, and where you are placing the subreport and formulas.

-LB

 

[dhennekens]

Right now, I do have both databases in the main report but there are no links.

The only group I have created is for sorting by the extension that made the call. In that group there are all the calls that were made by that extension during the time frame that I prompt for. It also calculates rounded time and cost per call for that record. In the group footer is the sum total for that group of records. The report header actually prints off a whole page of paper with Customer name and grand total of all group sums. Page header A has the page number and my logo. Page header B has the title of the columns of data in the details area. Group header has the name of the extension in it. The report footer is hidden. The page footer has my contact info.

I basically only select records that meet a certain criteria. Then use different formulas to calculate different rates for inter and intra state calls.

The last thing I need in the report is what you are graciously helping me with, the location of the called party.

The mid formulas are correct in this case. I have two databases I am working with. One that does have the 9 and one that doesn't. The one that doesn't has cleaner data.

Thanks

 

[lbass]

I would remove the second database from the main report, add it to a subreport, and do the linking I suggested earlier, and adding the city and state concatenation formula to the detail section of the subreport. Is there a reason you haven't tried this?

-LB

 

[dhennekens]

The main reason is I don't 100% understand what needs to be done to get the end result.

The links are causing me problems. I'm not understanding what to link to what. Am I linking formulas in the main report to actual fields in the subreport? I go to the subreport links and pick the mid formulas that I created for areacode and prefix and link them to the second database and field. It looks like it creates a prompt for each one. Do I check the box that says 'select data in subreport based on field? Once this is done, what formula am I inserting into the details on the main report to show the location with the selected record?

Again thanks for your help. I am much closer than I was on my own.

 

[lbass]

Your approach to the linking is correct. Yes, check the "select data based on field" box (although I think this is the default). There will be no prompt. The {?pm-xxx} is just an internal parameter reflecting the links and it will appear in the record selection formula of the subreport. You need to do the linking twice, once connecting {@areacode} to {table.areacode} and once connecting {@prefix} to {table.prefix}.

Within the subreport, you need to create the formula that concatenates the city and state and place this in the detail section. Then suppress all other sections within the subreport. Then place the subreport wherever you want the city and state to appear in the main report. It will appear with a border around it. You can resize it to reflect the width of the formula field and then right click on the subreport->format subreport->borders->change the four border settings to "none".

-LB

 

[dhennekens]

I think I may have it. I ended up using the mid formula to get the 6 digits that make up the area code and prefix and linked them to a formula in the subreport that concatenates the area code and prefix from the second database. Then it pulled the right info. Thank you very much for helping me understand the linking. I am going to populate my second database with more info to test more numbers against.

 

[dhennekens]

Well, I ended up purchasing a database. The same links and subreport works great with just a few changes. The problem is that it the subreport is handing back multiple answers even though they are the same answer. Sometimes 3 or 4 or 8 answers or more. Another weird thing is there are two adjecent records with the same "callto" number. One gets a location (3 times) and the other did not return a match. It is possible that the database has more than one record in it for each match, but I only want to return one or at least just the first match. Thanks

 

[lbass]

If you have the links correct, you should be able to drag the concatenation formula for city and state into a group header (group on the formula) in the subreport in order to get a display of only one record.

-LB

 

[dhennekens]

You are a genius! Thanks again. It takes while to run the report (60k records in 1st DB and 400k+ records in 2nd DB). I am going to put it on a dual processor server with more memory and give it a shot. Thanks again