I have a table of contacts, here are the essential fields:

id, acct, first, last, mobile, phone

 

a few other fields are flag fields that I would use in the WHERE clause, 

isActive = 1, isDeceased ≠ 1, phone ≠ null, mobile ≠ null,

 

the desired result is:

id, acct, first, last, phone

 

I would like to also only include anything that has a phone number as some records have a phone some have a mobile number others have both, in addition two people (same account) may also have a phone number that is duplicated between records.

 

My end result is a unique list of phone numbers.

 

If two people have the same phone number then the first person with that phone number is sufficient. 

 

I was thinking it was either a UNION or SUB QUERY or perhaps a GROUP BY but the proper syntax escapes me.

 

Thanks.

 

 

Stephen

Of course the real answer is to have a related numbers table where you can easily accomplish your request (attached).  Even if you can't split the numbers to a related table, I want to post this here so others who may have this same need night take advantage of it since it is much simpler, allows more flexibility, and uses less resources. 

 

By using a Numbers table, you can require the number be unique.  Two Contacts can share the same number with a simple multiline of the ContactID in the Numbers table (or by using a join table).  You will have to script adding/removing ContactID from a number, which is fairly simple as well.  This has the added benefit of allowing additional numbers such as 'summer residence', 'warehouse' ... without limits.  And your criteria to restrict to Active and not Deceased (would there ever be a situation where one is deceased but still active?), would become more flexible because all that is required is modifying a find request.  Omitting empty phone or mobile wouldn't be necessary because all that would exist in the Numbers table are true numbers.

 

How you would script the new number would depend upon your version (whether taking advantage of popovers) and your existing workflow.  If someone wants to use this method and needs help with the Add/Remove Number script, let us know.

 

Added sentence in blue.

contacts.fp7.zip

Edited January 1, 201511 yr by LaRetta

Thanks LaRetta,

 

I was going to use the SQL to get results to make a virtual list but after much needed coffee - determined that i needed something that isn't virtual but a bit more static - (required for other relationships)

 

Came up with a routine to loop thru the list of people to generate a clean list of records.

 

the script first finds records that meet the criteria, sorts it and then sets a Global to create records in the other table.

 

the Active status is a bit as the account ( parent record ) but the isDeceased bit is individual (in child table) when they have passed. And so there may be others associate in the account still living. -

 

here is the script i worked out.

 

unique.pdf

 

(just updated the pdf, had to make a few corrections)

Thanks LaRetta,

I was going to use the SQL to get results to make a virtual list but after much needed coffee

LOL, and Happy New Year to you.