Sorting on extremely long records

[cabsandy]

See the 3 records below

Hardware version: 0; OS: VxWorks 5.3.1; Software version: 3.2.17p

Hardware version: 0; OS: VxWorks 5.3.1; Software version: 3.2.12p

Hardware version: 0; OS: VxWorks 5.3.1; Software version: 3.2.17p

No matter what I try, I have multiple records of the type above and I would expect FP to sort the 3.2.12p records first but FP seems to be having difficulty in looking this "deep". All the field are text. Anybody got any ideas on how to improve the sort?

sandy

[Lee Smith]

If your field is a text field, FM ignores non alpha characters, unless you change it's option to ASCII (an option of the field in "Option - Storage - checkbox at the bottom)

HTH

Lee

[ernst]

Hi there,

This has (also) got to do with the maximum amount of words and the maximum amount of characters that Filemaker sorts on.

I could not find an exact specification for this, but found that sorting:

Software Software Software Software blah version: 3.2.17p

Software Software Software Software blah version: 3.2.12p

gives the expected result, whilst sorting:

Software Software Software Software blah BLAH version: 3.2.17p

Software Software Software Software blah BLAH version: 3.2.12p

gives a result that looks like the last word or characters are ignored, no matter if they are numerical or alphanumerical.

You could always split your inputfield to two or more calculation fields and then perform the sort on those.

Regards,

Ernst.

[Lee Smith]

Ernst is right, you have exceeded the index for text of 60 characters. I almost said that in my first post, but it didn't look like 60 characters when I looked at it. However, after Ernst post, I thought I'd count them..

Software Software Software Software blah BLAH version: 3.2.17p = 63

Software Software Software Software blah BLAH version: 3.2.12p = 62

Software Software Software Software blah version: 3.2.12p = 58

Software Software Software Software blah version: 3.2.17p = 57

HTH

Lee

[cabsandy]

Cheers chaps-the records are actually way longer than the ones I posted-I omitted the first parts for brevity-looks like I'll have to find some way of splitting it up! :-(

sandy

[ernst]

It's not only the amount of characters that matters. In my silly example I replaced a few of the spaces with letters, so the total wordcount became less, but the amount of characters did NOT change, and hey, the sorting worked again...

Keeps me wondering about the exact specification...

And Lee, you surely need a G5 if I see your counting results...

Beste regards,

Ernst.

[Lee Smith]

[color:"blue"] > And Lee, you surely need a G5 if I see your counting results

Yes, that would be nice Slobber, slobber, drool, drool,

[cjaeger]

There's one more important rule for indexing: FileMaker Pro indexes only the first 20 characters from each word, and only up to the total of 60 characters from the whole field. So, if you want to use longer text strings, such as some German words, you may have to divide them by inserting a word separator (space) after the 20th character.

from ADVISOR TIPS, Use Text-Based Relationship Keys, by Honza Koudelka

http://advisor.com/Articles.nsf/nl/12235

[cabsandy]

Not sure if I've stumbled onto something here-I decided to change the field which has the extremely long text (80+) from Text to Number. I let FP then change the all the records (about 280,000 approx) to Number.

Once done, I noticed that upon clicking an individual record the original text appeared-but the record was still in number form (power of 10 was the format-must be because it was so large). So I then done a Sort by Ascending and Indexed all records.

The result seemed to be in order so I then changed all records BACK from Number to Text-ola! my records were now exactly in order-all 280,000! Put all this into a script to double check and works great.

I'd be interested if somebody else could try this to see if it works for them

sandy

[LaRetta]

Hi Sandy,

I hope you backup (to a zip or StuffIt) before making these changes. You're trying something dangerous. It's fine that it works; however, there are script-steps such as Set Field and (?) that, if the field is still number, will REMOVE all text!

Using (and manuipulating data) by mixing data types can produce some interesting results - and some that can be used to 'strip text' from numeric fields (I've used it with imported mixed data) or to *fool* FM (a number field recognizes a 'y' as 1), etc. Anyway, be careful!

I have planned to test every data type each direction, with every script command to watch their various behaviours but I haven't gotten around to it yet.

And since you can't change a field type via script (at least I don't think so), you might forget to change it back!! At least test during all your scripts except your sort script that - If[FieldType contains "number" flash a message to User and abort script. But personally, I just wouldn't do it. I'd find another solution to this problem. Back up, okay?

LaRetta

[Vaughan]

Your experiment indicates that you need your fields to store just the version number (or string) itself, not the descriptive text. That can easily be addes later, or stored in another field.

[cabsandy]

Mmmm... thanks for the tips-I'll bear them in mind. The data itself is not mission critical so if it all ends in tears, no great loss. I only need a few results so I wont be doing any more than about 3 steps.

On the subject of the scripting, the fact that I cant automate the changing of the field, means I have to "dive in" at that point.

Thanks for the feedback-i'm finding these forums a great help, along with my FP bible, and I'm learning all the time :-)