 | |
Searching via Relationship
comp.databases.filemaker comp.databases.filemaker
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Searching via Relationship -
05-29-2011
, 09:12 AM
#2
| |||
| |||
|
|
FMA 11.0v3 - Mac I need some help with a relationship problem : When the user creates a new contact record, I'd like to see if that contact already exists. In addition to the normal fields : Name, Address, etc., they must fill in either a cell & or a home phone & or a work phone. The problem is that one user may put the contact's home phone in the cell field, etc. How can I check if the number exists in any of the fields ? Thanks for your help. |
fields and hit a "Submit" button to transfer the data to the regular
fields.
The script that runs to do the transfer can check previously entered
data for duplicates, including all possible combinations of the phone
number(s) entered.
Be aware that users tend to wander about in their name & address entry,
though, with varying nicknames, abbreviations, spellings. This makes
direct comparison difficult. Better to compare on phone and/or first
few characters of the street address, which are generally numbers.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer
#3
| |||
| |||
|
|
On 2011-05-29 07:12:49 -0700, Keith <francois7... (AT) gmail (DOT) com> said: FMA 11.0v3 - Mac I need some help with a relationship problem : When the user creates a new contact record, I'd like to see if that contact already exists. In addition to the normal fields : Name, Address, etc., they must fill in either a cell & or a home phone & or a work phone. The problem is that one user may put the contact's home phone in the cell field, etc. How can I check if the number exists in any of the fields ? Thanks for your help. One way to gently guide the users is to ask them to fill in global fields and hit a "Submit" button to transfer the data to the regular fields. The script that runs to do the transfer can check previously entered data for duplicates, including all possible combinations of the phone number(s) entered. Be aware that users tend to wander about in their name & address entry, though, with varying nicknames, abbreviations, spellings. This makes direct comparison difficult. *Better to compare on phone and/or first few characters of the street address, which are generally numbers. -- Lynn Allen --www.semiotics.com Member FBA FM 10 Certified Developer |
phone number already exists. I have no difficulty with the globals,
name or address.
I began checking past entries with a pattern count but got lost ...
Any further adice would bne more than appreciated.
#4
| |||
| |||
|
|
Hi Everybody, FMA 11.0v3 - Mac I need some help with a relationship problem : When the user creates a new contact record, I'd like to see if that contact already exists. In addition to the normal fields : Name, Address, etc., they must fill in either a cell & or a home phone & or a work phone. The problem is that one user may put the contact's home phone in the cell field, etc. How can I check if the number exists in any of the fields ? Thanks for your help. |
already checking the other data for "duplicates". (e.g. Having multiple
phone numbers in the same Field could cause more difficulty than only ever
having single phone numbers.)
I haven't tested this, but you might be able to create a new Calculation
Field that combines the two phone numbers using a carriage return
character to separate them.
i.e.
c_CombinedPhone Calcuation, Text Result, Unstored
= PhoneNumber & "P" & CellNumber
where P is the carriage return symbol.
Then you can create a Relationship that matches this Field with the other
records,
i.e.
rel_MatchingPhoneNumbers
Match Records in Table A with Records in Table A
when c_CombinedPhone = Table A::c_CombinedPhone
Then if there is a duplicate the count of the matching records will not be 0.
i.e.
Duplicate when Count(rel_MatchingPhoneNumbers::c_CombinedPhone) > 0
The similar way would be to create multiple Relationships that match all
the possible combinations of the existing phone number Fields.
i.e.
rel_MatchingPhoneNumbers
Match Records in Table A with Records in Table A
when PhoneNumber = Table A::PhoneNumber
rel_MatchingCellNumbers
Match Records in Table A with Records in Table A
when CellNumber = Table A::CellNumber
rel_MatchingPhoneNumber_To_CellNumber
Match Records in Table A with Records in Table A
when PhoneNumber = Table A::CellNumber
You shouldn't need to have a CellNumber_To_PhoneNumber since that simply
matches the last one above in the opposite direction.
Then use the same Count method to check for duplicates.
i.e.
Duplicate when
(Count(rel_MatchingPhoneNumbers::PhoneNumber) > 0)
or (Count(rel_MatchingCellNumbers::CellNumber) > 0)
or (Count(rel_MatchingPhoneNumber_To_CellNumber::Cell Number) > 0)
Of course, whatever method you use will almost certainly turn up false
positives (e.g. people at the same address / phone number) and miss real
duplicates (e.g. people who have moved to a new address / phone number).
The only real way to find duplicates is manually trawling through the
data, and even that isn't 100% foolproof.
Helpful Harry
)
#5
| |||
| |||
|
|
On May 29, 12:04 pm, Lynn Allen<l... (AT) NOT-semiotics (DOT) com> wrote: On 2011-05-29 07:12:49 -0700, Keith<francois7... (AT) gmail (DOT) com> said: FMA 11.0v3 - Mac I need some help with a relationship problem : When the user creates a new contact record, I'd like to see if that contact already exists. In addition to the normal fields : Name, Address, etc., they must fill in either a cell& or a home phone& or a work phone. The problem is that one user may put the contact's home phone in the cell field, etc. How can I check if the number exists in any of the fields ? Thanks for your help. One way to gently guide the users is to ask them to fill in global fields and hit a "Submit" button to transfer the data to the regular fields. The script that runs to do the transfer can check previously entered data for duplicates, including all possible combinations of the phone number(s) entered. Be aware that users tend to wander about in their name& address entry, though, with varying nicknames, abbreviations, spellings. This makes direct comparison difficult. Better to compare on phone and/or first few characters of the street address, which are generally numbers. -- Lynn Allen --www.semiotics.com Member FBA FM 10 Certified Developer Thank you very much, Lynn, my problem is how do I determine if the phone number already exists. I have no difficulty with the globals, name or address. I began checking past entries with a pattern count but got lost ... Any further adice would bne more than appreciated. |
Here is an example where storing all phone numbers in a dedicated,
normalized, Phones table makes life much easier. Use a type_code field
(wk, hm, mb..) in teh Phones table and place the required number of
phone fields on the layout as single iteration portals filtered by the
type. ( or as a normal portal...)
Place a labelled button ( MB, BUS...) adjacent to each single portal row
iteration, and attach the script, with the script parameters respective
to the label. Enter the number in a global as part of a custom dialogue,
have the script gp to a bts_phones layout, search the single
phone_number field via a find, and check against the parent contact_id
as required... if it does not exist, create new record, hard code the
phone number and contact_id, return to contact layout.
The rel filtered rel for the specific number will now be valid, and the
number displayed. The other portal rels will not have related numbers,
and one can use conditional formatting to grey these out. So there is an
obvious visual feedback...
The avoid mutant replicates, scrub (trim for spaces, number of digits
etc via the script prior to hard coding the number. If you have
historical data, scrub/massage this into conformance.
The same approach works for individual dedicated tables for all
addresses, emails, and URL.
Rather than have one big pop up layout with the globals for address,
email, phones, break it down into individual routines.
Some may think the extra overhead of TOC and RELS is not worth it; I
disagree.
#6
| |||
| |||
|
|
In article 0b5c5786-3e53-45b6-970e-07dad31b5... (AT) s14g2000vbi (DOT) googlegroups.com>, Keith francois7... (AT) gmail (DOT) com> wrote: Hi Everybody, FMA 11.0v3 - Mac I need some help with a relationship problem : When the user creates a new contact record, I'd like to see if that contact already exists. In addition to the normal fields : Name, Address, etc., they must fill in either a cell & or a home phone & or a work phone. The problem is that one user may put the contact's home phone in the cell field, etc. How can I check if the number exists in any of the fields ? Thanks for your help. That depends on how the data is being entered / stored and how you are already checking the other data for "duplicates". (e.g. Having multiple phone numbers in the same Field could cause more difficulty than only ever having single phone numbers.) I haven't tested this, but you might be able to create a new Calculation Field that combines the two phone numbers using a carriage return character to separate them. i.e. * * * c_CombinedPhone * * *Calcuation, Text Result, Unstored * * * * * *= PhoneNumber & "P" & CellNumber where P is the carriage return symbol. Then you can create a Relationship that matches this Field with the other records, i.e. * * * rel_MatchingPhoneNumbers * * * * *Match Records in Table A with Records in Table A * * * * *when c_CombinedPhone = Table A::c_CombinedPhone Then if there is a duplicate the count of the matching records will not be 0. i.e. * * * Duplicate when Count(rel_MatchingPhoneNumbers::c_CombinedPhone) > 0 The similar way would be to create multiple Relationships that match all the possible combinations of the existing phone number Fields. i.e. * * * rel_MatchingPhoneNumbers * * * * *Match Records in Table A with Records in Table A * * * * *when PhoneNumber = Table A::PhoneNumber * * * rel_MatchingCellNumbers * * * * *Match Records in Table A with Records in Table A * * * * *when CellNumber = Table A::CellNumber * * * rel_MatchingPhoneNumber_To_CellNumber * * * * *Match Records in Table A with Records in Table A * * * * *when PhoneNumber = Table A::CellNumber You shouldn't need to have a CellNumber_To_PhoneNumber since that simply matches the last one above in the opposite direction. Then use the same Count method to check for duplicates. i.e. * * * Duplicate when * * * * * * (Count(rel_MatchingPhoneNumbers::PhoneNumber) > 0) * * * * *or (Count(rel_MatchingCellNumbers::CellNumber) > 0) * * * * *or (Count(rel_MatchingPhoneNumber_To_CellNumber::Cell Number) > 0) Of course, whatever method you use will almost certainly turn up false positives (e.g. people at the same address / phone number) and miss real duplicates (e.g. people who have moved to a new address / phone number). The only real way to find duplicates is manually trawling through the data, and even that isn't 100% foolproof. Helpful Harry * ) |
Thanks for your explanation.
The only problem I see is that when creating the record, the means of
contact field (phone.home, phone.work or cell) becomes included in the
relationship so he alerts to doubles (the count will always be more
than 0) :
Relationships :
phone.home = phone.home
phone.home = phone.work
phone.home = cell
I had tried with globals but that didn't work either. He found only
when the global was = to the related field i.e. only when gphone.home
was = to phone.home, not to when it was = to cell ! :
Relationships :
gphone.home = phone.home
gphone.home = phone.work
gphone.home = cell
The only immediate solution I see is to script the procedure but that
is a step I wanted to avoid.
Suggestions ?
Thank you
#7
| |||
| |||
|
|
On May 29, 4:22=A0pm, your.n... (AT) isp (DOT) com (Your Name) wrote: In article 0b5c5786-3e53-45b6-970e-07dad31b5... (AT) s14g2000vbi (DOT) googlegroups.com>, Keith <francois7... (AT) gmail (DOT) com> wrote: Hi Everybody, FMA 11.0v3 - Mac I need some help with a relationship problem : When the user creates a new contact record, I'd like to see if that contact already exists. In addition to the normal fields : Name, Address, etc., they must fill in either a cell & or a home phone & or a work phone. The problem is that one user may put the contact's home phone in the cell field, etc. How can I check if the number exists in any of the fields ? Thanks for your help. That depends on how the data is being entered / stored and how you are already checking the other data for "duplicates". (e.g. Having multiple phone numbers in the same Field could cause more difficulty than only ever having single phone numbers.) I haven't tested this, but you might be able to create a new Calculation Field that combines the two phone numbers using a carriage return character to separate them. i.e. c_CombinedPhone Calcuation, Text Result, Unstored = PhoneNumber & "P" & CellNumber where P is the carriage return symbol. Then you can create a Relationship that matches this Field with the other records, i.e. rel_MatchingPhoneNumbers Match Records in Table A with Records in Table A when c_CombinedPhone = Table A::c_CombinedPhone Then if there is a duplicate the count of the matching records will not be 0. i.e. Duplicate when Count(rel_MatchingPhoneNumbers::c_Combined Phone) > 0 The similar way would be to create multiple Relationships that match all the possible combinations of the existing phone number Fields. i.e. rel_MatchingPhoneNumbers Match Records in Table A with Records in Table A when PhoneNumber = Table A::PhoneNumber rel_MatchingCellNumbers Match Records in Table A with Records in Table A when CellNumber = Table A::CellNumber rel_MatchingPhoneNumber_To_CellNumber Match Records in Table A with Records in Table A when PhoneNumber = Table A::CellNumber You shouldn't need to have a CellNumber_To_PhoneNumber since that simply matches the last one above in the opposite direction. Then use the same Count method to check for duplicates. i.e. Duplicate when (Count(rel_MatchingPhoneNumbers::PhoneNumber) > 0) or (Count(rel_MatchingCellNumbers::CellNumber) > 0) or (Count(rel_MatchingPhoneNumber_To_CellNumber::Cell Number) > 0) Of course, whatever method you use will almost certainly turn up false positives (e.g. people at the same address / phone number) and miss real duplicates (e.g. people who have moved to a new address / phone number). The only real way to find duplicates is manually trawling through the data, and even that isn't 100% foolproof. Helpful Harry )Harry, Thanks for your explanation. The only problem I see is that when creating the record, the means of contact field (phone.home, phone.work or cell) becomes included in the relationship so he alerts to doubles (the count will always be more than 0) : Relationships : phone.home =3D phone.home phone.home =3D phone.work phone.home =3D cell I had tried with globals but that didn't work either. He found only when the global was =3D to the related field i.e. only when gphone.home was =3D to phone.home, not to when it was =3D to cell ! : Relationships : gphone.home =3D phone.home gphone.home =3D phone.work gphone.home =3D cell The only immediate solution I see is to script the procedure but that is a step I wanted to avoid. Suggestions ? Thank you |
happens when you type something up when you're in a hurry to get out to an
appointment.
(Instead of testing the Relationship Counts with > 0, change it to > 1. If
there is more than 1 record with this number, then there are duplicates.
A Global Field on the left side of the Relationships should work fine.
i.e.
global_Phone.Home = TableA::Phone.Home
global_Phone.Home = TableA::Phone.Work
global_Phone.Home = TableA::Phone.Cell
global_Phone.Work = TableA::Phone.Home
global_Phone.Work = TableA::Phone.Work
global_Phone.Work = TableA::Phone.Cell
Helpful Harry
)
#8
| |||
| |||
|
|
In article eb253243-39ea-4101-ad05-b17146038... (AT) f15g2000pro (DOT) googlegroups.com>, Keith francois7... (AT) gmail (DOT) com> wrote: On May 29, 4:22=A0pm, your.n... (AT) isp (DOT) com (Your Name) wrote: In article 0b5c5786-3e53-45b6-970e-07dad31b5... (AT) s14g2000vbi (DOT) googlegroups.com>, Keith <francois7... (AT) gmail (DOT) com> wrote: Hi Everybody, FMA 11.0v3 - Mac I need some help with a relationship problem : When the user creates a new contact record, I'd like to see if that contact already exists. In addition to the normal fields : Name, Address, etc., they must fill in either a cell & or a home phone & or a work phone. The problem is that one user may put the contact's home phone in the cell field, etc. How can I check if the number exists in any of the fields ? Thanks for your help. That depends on how the data is being entered / stored and how you are already checking the other data for "duplicates". (e.g. Having multiple phone numbers in the same Field could cause more difficulty than only ever having single phone numbers.) I haven't tested this, but you might be able to create a new Calculation Field that combines the two phone numbers using a carriage return character to separate them. i.e. * * c_CombinedPhone * *Calcuation, Text Result, Unstored * * * * = PhoneNumber & "P" & CellNumber where P is the carriage return symbol. Then you can create a Relationship that matches this Field with the other records, i.e. * * rel_MatchingPhoneNumbers * * * * Match Records in Table A with Records in Table A * * * * when c_CombinedPhone = Table A::c_CombinedPhone Then if there is a duplicate the count of the matching records will not be 0. i.e. * * Duplicate when Count(rel_MatchingPhoneNumbers::c_Combined * * Phone) > 0 The similar way would be to create multiple Relationships that match all the possible combinations of the existing phone number Fields. i.e. * * rel_MatchingPhoneNumbers * * * * Match Records in Table A with Records in Table A * * * * when PhoneNumber = Table A::PhoneNumber * * rel_MatchingCellNumbers * * * * Match Records in Table A with Records in Table A * * * * when CellNumber = Table A::CellNumber * * rel_MatchingPhoneNumber_To_CellNumber * * * * Match Records in Table A with Records in Table A * * * * when PhoneNumber = Table A::CellNumber You shouldn't need to have a CellNumber_To_PhoneNumber since that simply matches the last one above in the opposite direction. Then use the same Count method to check for duplicates. i.e. * * Duplicate when * * * *(Count(rel_MatchingPhoneNumbers::PhoneNumber) > 0) * * or (Count(rel_MatchingCellNumbers::CellNumber) > 0) * * or (Count(rel_MatchingPhoneNumber_To_CellNumber::Cell * * * * * *Number) > 0) Of course, whatever method you use will almost certainly turn up false positives (e.g. people at the same address / phone number) and miss real duplicates (e.g. people who have moved to a new address / phone number). The only real way to find duplicates is manually trawling through the data, and even that isn't 100% foolproof. Helpful Harry * )Harry, Thanks for your explanation. The only problem I see is that when creating the record, the means of contact field (phone.home, phone.work or cell) becomes included in the relationship so he alerts to doubles (the count will always be more than 0) : Relationships : phone.home =3D phone.home phone.home =3D phone.work phone.home =3D cell I had tried with globals but that didn't work either. He found only when the global was =3D to the related field i.e. only when gphone.home was =3D to phone.home, not to when it was =3D to cell ! : Relationships : gphone.home =3D phone.home gphone.home =3D phone.work gphone.home =3D cell The only immediate solution I see is to script the procedure but that is a step I wanted to avoid. Suggestions ? Thank you D'oh! Of course it counts the current record ... sorry, that's what happens when you type something up when you're in a hurry to get out to an appointment. * (Instead of testing the Relationship Counts with > 0, change it to > 1. If there is more than 1 record with this number, then there are duplicates. A Global Field on the left side of the Relationships should work fine. i.e. * * * *global_Phone.Home = TableA::Phone.Home * * * *global_Phone.Home = TableA::Phone.Work * * * *global_Phone.Home = TableA::Phone.Cell * * * *global_Phone.Work = TableA::Phone.Home * * * *global_Phone.Work = TableA::Phone.Work * * * *global_Phone.Work = TableA::Phone.Cell Helpful Harry * ) |
are certainly not necessary. In addition I should have known to change
the 0 to 1 !!
Stay well & happy.
#9
| |||
| |||
|
|
On May 29, 12:04*pm, Lynn Allen <l... (AT) NOT-semiotics (DOT) com> wrote: On 2011-05-29 07:12:49 -0700, Keith <francois7... (AT) gmail (DOT) com> said: FMA 11.0v3 - Mac I need some help with a relationship problem : When the user creates a new contact record, I'd like to see if that contact already exists. In addition to the normal fields : Name, Address, etc., they must fill in either a cell & or a home phone & or a work phone. The problem is that one user may put the contact's home phone in the cell field, etc. How can I check if the number exists in any of the fields ? Thanks for your help. One way to gently guide the users is to ask them to fill in global fields and hit a "Submit" button to transfer the data to the regular fields. The script that runs to do the transfer can check previously entered data for duplicates, including all possible combinations of the phone number(s) entered. Be aware that users tend to wander about in their name & address entry, though, with varying nicknames, abbreviations, spellings. This makes direct comparison difficult. *Better to compare on phone and/or first few characters of the street address, which are generally numbers. -- Lynn Allen --www.semiotics.com Member FBA FM 10 Certified Developer Thank you very much, Lynn, my problem is how do I determine if the phone number already exists. I have no difficulty with the globals, name or address. I began checking past entries with a pattern count but got lost ... Any further adice would bne more than appreciated. |
table and displaying them in a portal. You can check for dupes with a
field options validation (Unique).
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.