Sorting by custom value list - not working!

[exposbabe]

Hi folks,

Having trouble with this. It seems simple but it just doesn't work.

I have a customer mailing list that must be sorted by a specific (Canadian) postal code order, required by Canada Post. Specifically, the first three digits of the postal code, which are ANA (alpha, numeric, alpha: for example J5A) The field itself is a text field, necessarily.

I've got the required order in a value list.

In the script, when I try to sort it by that list, it seems to work for the first few, then it seems to start over and pretty much go alphabetically.

Can you explain why this is? And what the heck I can do about it?

The next mailing has to go out in less than 48 hours, and the postal code thing is throwing a major curve. It's the only thing about upgrading to Filemaker 8 (from an old Fox Pro relic) that hasn't worked out so far.

Serious, serious problems if I can't get it solved quickly. Help much appreciated.

[mr_vodka]

Do you have a copy of a watered down file with the value list and records in place that you can zip up and post?

[exposbabe]

I'm a bit of a newbie, I'm afraid. Not sure what you mean.

The custom value list in question has about 1,600 items, all of the Canadian postal code combinations (the Canadian postal code is like this: ANA NAN (A being alpha and N being numeric). The first part is how it needs to be sorted.

The customer records in my database (I'd be embarrassed to show it to you; I started it with no knowledge whatsoever and while it works, it's no doubt quite convoluted) have a specific field called Postalcode 1 (for the first part). And that is how they need to be sorted.

[mr_vodka]

No need to be embarassed here. Zip up the file and post a copy. To attach a file, hit the "Full Reply" button. There will be an option to attach a file under 'Manage Files'.

[exposbabe]

The whole file? It's like 3.6 mb

[mr_vodka]

Make a copy of the file. Then take the file and delete all the unnecessary fields in there. Basically just leave a few fields including the PostalCode field of course. Then Zip it and attach.

[exposbabe]

Just e-mailed it to you. Hope it's OK.

[mr_vodka]

Your issue is that there are spaces as an end character in some of the entries in your sorting value list. Once you get rid of those, it should work fine.

[exposbabe]

It does! Thank God you're a night owl.

Many, many thanks.

[mr_vodka]

BTW, I should have told you this before to save some tedious work. Instead of going through the whole list and finding all the extra spaces, in your case, you can do the following:

1. create a temporary field

2. Copy and paste your value list items into that field

3. Select the field and go to the Records Menu --> Replace field Contents --> Replace with calculated value

4. Use the formula TrimAll( TempField ;0 ; 3)

5. Copy the new updated contents of the field

6. Open up your value list and paste the new data into it.

7. After done, you can delete the temp field

Sorry if its too late. ;)

[mharrsch]

I have a conditional value list that will sort in ascending order by the first field but I want to sort it in descending order. I have tried to specify a descending order in the relationship sort but it does not seem to have an effect on the sort order of the conditional value list. (I have checked the values for trailing spaces and there aren't any). Anyone have any suggestions?

[comment]

This doesn't seem to be related to the original topic. In any case, see if this helps.

[mharrsch]

Thanks, veteran. I decided to tackle the problem a little differently. I have asked the user coordinator if the program selection can be by title instead of alpha-numeric program number and have set the conditional value list to sort by second field program title. If the coordinator still wants it sorted by alpha-numeric program number in descending order I can sort by second field populated with serial numbers assigned to records sorted in descending order by Program. New Programs added to the list will have an autoassigned serial number set to the next highest above the current maximum number so it should perpetuate the descending sort for future additions to the program table.

[Genx]

Does one of the skins for the forums put a lot of emphasis on the User Title or something...

Isn't the first time i've seen someone new refer to someone else by that instead of the display name... just curious.

[Lee Smith]

No, and No.

Lee

[comment]

I guess it's my screen name: people think the post is a comment from a veteran...

[mr_vodka]

Well "Veteran", you could have called Yourself, "Anomality" and then people would call you that instead.

:(

[comment]

Well, I couldn't have - it was stolen from me! And, unlike JT, I am not allowed to change my title. Maybe Stephen will give us another "bonus week" to do that. I don't like these classifications anyway.

[Genx]

Well, I couldn't have - it was stolen from me! And, unlike JT, I am not allowed to change my title

Lol, steal it back!

What do you mean you can't change your title though? Do you not have control panel > public profile > user title?

[Genx]

Hmmm, lol Carpal Tunnel if i wipe my user title.

Carpal tunnel syndrome (CTS) is a medical condition in which the median nerve is compressed at the wrist, leading to pain, paresthesias, and muscle weakness in the forearm and hand.[1] A form of peripheral neuropathy, CTS is more common in women than it is in men, and, though it can occur at any age, has a peak incidence around age 42.[2] The lifetime risk for CTS is around 10% of the adult population.[3]

Well apparently i sit at my keyboard to much.

[mr_vodka]

What do you mean you can't change your title though? Do you not have control panel > public profile > user title?

I think this is a feature for paying members? and you can probably see it because you are a moderator.

Michael was referring to a period where Stephen gave an open window so that members could change it briefly as a freebie.

[Genx]

Ah righto... lol weird, funny perk though.

[LaRetta]

LOL, Genx, I think I'd rather be a Veteran than a Carpal Tunnel. Veteran at least sounds steadfast and wise; Carpal Tunnel sounds like someone with NO life other than keyboard. Now mine, I chose and like. I will always be in study mode with FM; always. But it also sounds like I'm someone who just can't ever get the hang of graduating - a professional student who hangs on forever! Ha ha!

L

[Genx]

a professional student who hangs on forever

LOL love it!

[Genx]

Actually, not to pick at semantics but "carpal tunnel" by itself really implies that i am the disease carpal tunnel, rather than sick with it.

[comment]

Not to pick at semantics, but "carpal tunnel" by itself is not a disease.

[Genx]

I would say that 99 out of 100 in the first 10,000 results on google for a search of "carpal tunnel" would disagree with you in relation to it's common interpretation.

What exactly does "carpal tunnel" refer to without the syndrome tacked on in that case?

[comment]

It refers to the body part afflicted with the syndrome - just as "head" without the "ache" tacked on does.

[Genx]

Ah I see: "the space found in the wrist between the flexor retinaculum of the wrist and the carpal bones"

I assumed that Carpal referred to the body part - learn something new everyday

Well in that case I'm a body part lol.

[comment]

That's OK - some of my best friends are body parts.

[Genx]

Ahahahahaha. Okay, i have absolutely nothing to say to that -- you really are quite witty did you know that?