Household Mailing List

[erichf]

I have a Voter list (table) that might have 2-3 or 4 voters in one house. I want to craft a filter that results in a table that has only ONE record per household for mailing purposes (First Name(s), Last Name, Address, etc.). I used key_unique_value() but that yields the FIRST person in that household. I thought of making a table using key_unique_value() to extract a master table of unique households based on the address field and then create a set with the individuals in the child of a parent / child. That would get one household and many first names. Any better ideas?

Erich

[Stan Mathews]

Re: Household Mailing List

Hard to explain fully without table and field names.

Summarize your current table group by household identifier. Include whatever other fields you like. Create an expression using

strtran(table.external_record_content_get(stuff here),crlf(),", "), choose first, last, whatever.

This expression will give you a comma separated list of voters per household.

Note: The suggested process doesn't work as intended. The expression may be too complex. You can still summarize the table as suggested, add a character field of appropriate length, then post the expression to that field.

[robert]

Re: Household Mailing List

You could duplicate the table and use the delete duplicate records utility on the table duplicate if you want to retain all the entries in the original table.

[Tom Henkel]

Re: Household Mailing List

I would suggest you build 2 new tables, household, and individual.

Each table will need a household identifyer.

The household table could get created using all records, then perform a delete duplicates operation, yielding one record per household.

The individual table would be built in the same manner, but with no deletions. Both tables would not need all the information from the original, only information pertinant to that table AND the household identifier. Once built, create a set linking on household identifier.

Tom

[Mike Wilson]

Re: Household Mailing List

I have a Voter list (table) that might have 2-3 or 4 voters in one house. I want to craft a filter that results in a table that has only ONE record per household for mailing purposes (First Name(s), Last Name, Address, etc.). I used key_unique_value() but that yields the FIRST person in that household. I thought of making a table using key_unique_value() to extract a master table of unique households based on the address field and then create a set with the individuals in the child of a parent / child. That would get one household and many first names. Any better ideas?

Erich,
Does this mean that you want the Mailing label to read:

Jim, Sara, and Tom Williamson
1234 Anywhere Rd
Downhome, TX 12345-2345

If not, what are you wishing?

Later:
What if divorced daughter Jane Smith comes back to live with her parents Bob and Betty Jones?

[erichf]

Re: Household Mailing List

Erich,
Does this mean that you want the Mailing label to read:

Jim, Sara, and Tom Williamson
1234 Anywhere Rd
Downhome, TX 12345-2345

If not, what are you wishing?

Later:
What if divorced daughter Jane Smith comes back to live with her parents Bob and Betty Jones?

Mike, yes that is exactly what I am after. I am NOT worried about future moves as this is a snapshot in time. With the 2 table solution, I can pretty easily get to

Williamson Family (or Household)
1234 Anywhere Rd
Downhome, TX 12345-2345

This list goes to a mailing service in an Excel spreadhseet format and is filtered for party affiliation, active/inactive voter etc. before it goes. It looks like Stan's idea above might work to draw the first names out of the 2 table solution.

There is an additional complicating factor: some households have differing last names (3 with same last name, 1 different), but I am not worried about that right now.

Erich

[DaveM]

Re: Household Mailing List

Hi,

I have been watching this for a couple of days now and it looks like you are getting it right now.

Let me see if this is how you plan it??

house.dbf
zip 'field could be the main id
address
city
state
Phone???

person.table ' child of one to many?
zip 'field could be the main id
first
last
etc ' other fields per person
affiliation ' to determine dem or rep/ or
diflast 'logical to state if diff last?? needed if a different letter needs to be sent?

is that kinda how you figured it out

.

[erichf]

Re: Household Mailing List

Hi,

I have been watching this for a couple of days now and it looks like you are getting it right now.

Let me see if this is how you plan it??

house.dbf
zip 'field could be the main id
address
city
state
Phone???

person.table ' child of one to many?
zip 'field could be the main id
first
last
etc ' other fields per person
affiliation ' to determine dem or rep/ or
diflast 'logical to state if diff last?? needed if a different letter needs to be sent?

is that kinda how you figured it out

.

Dave - that is close to the solution I am at now. I just had our County add what they call an Address_Library_ID which is their definition of a Household. This is a unique number for each separate address. This makes it easier to filter just single addresses per household although I could have achieved this with an index and key_unique_value() on the address field. I am still trying to figure out how to aggregate first names in a string using Stan's suggestion above.

Erich

[DaveM]

Re: Household Mailing List

Eric,

That kind of depends on if you want a letter like
John, mary and tom doe and bill wilson
or
1 letter to John, mary and tom doe and a seperate letter to bill wilson
or
john doe, mary doe, tom doe and bill wilson (this could be very long)

Either one is going to present some challenges for the letter reports, but are doable

One thought is another field like "NUMBERs as n 1" and place a number to designate change of last name.

as
all people named doe = 1
all people named wilson = 2
etc
in the same house.

I currently do that with auto trades. an may have up to 100 trades on a deal(never had more than 5 so far)



.

[erichf]

Re: Household Mailing List

So, here is the update. If you want to personalize the address other than sending to "Household" or "Dear Neighbor", it is not easy. I did create 2 tables into a set 1) unique addresses, 2) All name and address data. Then, I built a form that showed each unique address and allowed me to "pick" the one name to send to in that household. Time consuming but gets a better result. I can't think of a way to automate this due to the very many variables in each household. Some had as many of 5 registered voters all with different last names. I think that still takes a human in those more difficult cases -- and I am bleary-eyed. The cop out would have been to just give up and use the "Household" address.

Erich