jbowley Posted September 20, 2007 Share Posted September 20, 2007 I have 2 databases that have a relationship on phone numbers On database 1 I want a field that will look at database 2 to see if that phone number is there. But I am not looking for exact match. I am looking for area code - prefix match. Example database one has 222-333-4000 I want to do partial match on just first 7 characters. So if database 2 has 222-333-4125 & 222-333-4287 then it would show there is a match I tried a calculation field If(Left(Phone;7) = Left(TBN_ADM_IC_231::Phone;7);"XX";"") also tried PatternCount (Left(TBN_ADM_IC_231::Phone;7) ; Left(Phone;7)) But they only returned exact matches. Link to comment Share on other sites More sharing options...
Keith LaMarre Posted September 21, 2007 Share Posted September 21, 2007 there's an obvious solution that should work - and that is to have a calculation (in both) that says: Phone_first7:(left(phone;7)) - or whatever you want to do to strip out any non numberic characters - and then either use this as the match field or at least compare these two. Link to comment Share on other sites More sharing options...
aldipalo Posted September 21, 2007 Share Posted September 21, 2007 (edited) Try this. Assuming Contacts2 is the number you want to check against contacts. I read this wrong, sorry. Edited September 21, 2007 by Guest Link to comment Share on other sites More sharing options...
jbowley Posted September 21, 2007 Author Share Posted September 21, 2007 I must be missing something. Database 1 calculation field Phone7=LEFT(Phone;7) Database 2 calculation field Phone7=LEFT(Phone;7) They both display 222-333 Yet when I create a comapre field ComparePhone=If(Database2::Phone7=Phone7;"DJM";"") still not working right. Link to comment Share on other sites More sharing options...
mr_vodka Posted September 21, 2007 Share Posted September 21, 2007 I think that you are trying to use a relationship correct? If you are matching on DB1::Phone7 to DB2::Phone7, then all you have to do is Case ( IsValid ( DB2::Phone7); "DJM") or you can also use Case ( not IsEmpty ( DB2::Phone7); "DJM"). Link to comment Share on other sites More sharing options...
jbowley Posted September 26, 2007 Author Share Posted September 26, 2007 Tried this & I can still only get it to work if exact match? Any suggestions. If you were going to do this please walk me through each step you would take. Thanks Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6191 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now