Hi All, I am working on a database which reads call records from a pabx and splits them with associated costs to separate people.

I have one table callrecords which holds all the records, the only thing I need from there is the duration and destination number. To determine the charge for the call I need to lookup the call rate from my rates table which defines the prefix for the number and the rate to charge for the prefix.

My problem is that I want to setup a relationship to do this lookup of the call rate, but, different countries have different prefixes, for example US is 1, but Finland is 358, Germany/Dusseldorf is 49211, how on earth can I match the call rate records in my rates table with prefixes from the callrecords table when the number of characters varies from prefix to prefix ?

Any suggestions would be greatly appreciated, I have been looking for some answers for this for some time. I have considered a calculated field for the relationship, but I will still have the problem of never really knowing the prefix which has been used for each call record.

Regards

Paul

how on earth can I match the call rate records in my rates table with prefixes from the callrecords table when the number of characters varies from prefix to prefix ?

Can YOU tell, by looking at the number, what the prefix is? If so, how?

Well the only way yo tell is by looking at the actual phone number, for example a number for the US might be 1875557849 so the prefix is 1, but another number might be 6489024573, but in this case the first 2 numbers might be for the generic country code (I think NZ), but there could also be another match which might be 6489 for a NZ mobile (all fictitious of course -) so you see I can't actually know whether the prefix is 1 digit or 6, but it can be anything in between I know that.

Regards

Paul

Edited September 18, 201015 yr by Guest

Well, then you should start by looking up the country/area by matching the first 6 digits of the destination number to the codes in the Rates table. If that fails, try the first 5 digits, and so on.

You can do all this at once, by "exploding" the destination number and sorting the related rates by their lengths. This will lookup the longest matching code.

This was the only way I could think of also, although I couldn't really get my head around how to do it, considering I will have thousands of call records per month to manage I was concerned about efficiency.

If I could be sure how many numbers always followed the area code it would certainly be easier, but I'm not.

Regards

Paul

If you use a custom function or a repeating calculation field (or even hard-coded calc with 6 Left() statements) to give you:

1

12

123

1234

12345

123456

out of "123456789...", I believe it won't be much slower than a regular lookup. And I don't see that you have too many choices anyway.

Thanks for your help, I'll have a go at getting that working and see how it goes.

Regards

Paul