Author Topic: Beginner's Query Question (Read 2855 times)

MaryD · September 25, 2008, 05:49:26 am

Hi,

I have imported records which include many profile files on different supporters. Some have addresses, some do not (just names & email addresses). I want to come up with a query that defines only those:

with an address and
sorts the labels into zip code order.

I found that if I use the string "Individuals City Primary Like %" I cut out the ones that didn't have a city in the address field (it worked to cut out the ones with emails and no addresses. However, I am still trying to figure out how to sort them into zip code order. Any ideas?

Thanks ahead for any suggestions!

ctarascio · September 25, 2008, 07:07:07 am

Hi MaryD,
If you do a search for postal code > "00000" and then click on the column header for "postal code", the records will sort in zip code order. Unfortuately, this order is not retained when you go to do the mailing labels. Short of exporting the data out of CiviCRM and into another piece of software, I don't see how to get this to work for you but, as my knowledge of CiviCRM is limited, I'll leave it to one of the pros to jump in.

Cynthia

petednz · September 25, 2008, 12:15:38 pm

Agree with Cynthia - was going to ask what the purpose was of sorting while in civiCRM. If for labels then expect it reverts. So probably just need to export and do it from eg excel/neooffice

If for eyeballing purposes then cynthia is right - alternatively, if you want to view the PostCode with other non-address data, you could either Tag or Group your contacts, set up a Profile (as Search Results) and add the fields you want to view, setting them to In Selector, and then you can choose this option under Search Views in Adv Search.

This will result in the fields you are interested in being displayed in the Search Results.

That said, postcode is one of those fields that shows in Search Results as a default I believe, and can be clicked on at top to sort results by that field.

(THink above is right)

MaryD · September 25, 2008, 03:14:28 pm

That's a pain in the patoosie. The post office wants numeric sorted mail for bulk mailings. Oh, well. I think my best bet is do the sort, then export...

petednz · September 25, 2008, 03:24:08 pm

The export will ignore the sort - they export by Internal ID. We also have to do this to get our discounted postal rates. We simply export, then sort by postcode, then either send it to the mailhouse or print to labels via Mail Merge or whatever the program offers to make labels.

Dave Greenberg · September 25, 2008, 04:47:23 pm

Actually, at least in 2.1, the export does respect the requested sort. I just did a test where I sorted the search results by Postal Code and the export was properly sorted.

petednz · September 25, 2008, 08:59:15 pm

IN which case, how hard to have that 'sort' used in printing labels from the Search Result?

Dave Greenberg · September 26, 2008, 12:21:02 pm

Just checked in my 2.1 install and it looks like the labels are sorted by postal code if I sort the search results that way. Have u seen different behavior in 2.1?

petednz · September 26, 2008, 03:21:16 pm

I see that on the Demo too. All good. Print, Labels and Export. Other than it puts postcode 10 before postcode 2 - but that might be how it works in some countries (or may have been due to my import set up?)

Dave Greenberg · September 26, 2008, 03:59:43 pm

10 before 2 ...

The issue is that some countries use numeric postal codes and others use alphanumeric codes. Because of this, we store the postal code in a "character" type database column, not a numeric column. AND that means they are sorted as "strings" rather than numbers. This works decently for a lot of cases - in the US all postal codes are 5 digits - so 10000 will sort before 20000. And alpha-numeric codes should mostly / always sort reasonably.

petednz · September 26, 2008, 05:35:14 pm

All makes sense - I just know that some of our 0670 type postcodes come in to our system as 670 - but that is an issue we can sort out.

MaryD · September 29, 2008, 01:54:56 pm

So that will work out in version 2.1? Oh, that's going to be really cool!

I went ahead and cranked out an excel spreadsheet and used it for labels. Still, it will be cool when this works internally--I like the pdf, which will be useful with my volunteers.

CiviCRM Community Forums (archive)

News:

Author Topic: Beginner's Query Question (Read 2855 times)

MaryD

Beginner's Query Question

ctarascio

Re: Beginner's Query Question

petednz

Re: Beginner's Query Question

MaryD

Re: Beginner's Query Question

petednz

Re: Beginner's Query Question

Dave Greenberg

Re: Beginner's Query Question

petednz

Re: Beginner's Query Question

Dave Greenberg

Re: Beginner's Query Question

petednz

Re: Beginner's Query Question

Dave Greenberg

Re: Beginner's Query Question

petednz

Re: Beginner's Query Question

MaryD

Re: Beginner's Query Question