Alphabetize a list

[ChasCH]

Hi

Can someone clue me in as to alphabetizing a list in FM. The list is seperated by returns and may contain multiple words per line.

This would be the contents of an example field:

Fred

Abby & Joe

2Happy ToCare

Ancient Aztec Ruins are amazing

Lettuce Pray

[Lee Smith]

Is this a value list, or a field that can be sorted by a "Sort"?

Lee

[ChasCH]

So if I have 100 records, each with a field named "totalItems"

and

every record may have a different number of "items" in its field "totalitems":

so field "totalitems" of record 1 has:

Fred

Abby & Joe

and field "totalitems" of record 2 has:

Fred

Abby & Joe

Lettuce Pray

and field "totalitems" of record 3 has:

Fred

Abby & Joe

2Happy ToCare

Ancient Aztec Ruins are amazing

Lettuce Pray

Then I want to sort each field "totalitems" within each record alphabetically by 1st char in each word

so field "totalitems" of record 1 is sorted to this:

Abby & Joe

Fred

field "totalitems" of record 2 is sorted to this:

Abby & Joe

Fred

Lettuce Pray

etc...

Make Sense?

All the Best

Chas

[ChasCH]

And yes I can creat a value list that corresponds to the correct order.

I did not know I might be able to coerce the contents of an individual field to sort based on a value list.....

[transpower]

Make the field a checkbox field (with the values presorted).

[-Queue-]

Create a self-relationship based on serial number. Create a value list using values from field relationship::textfield. Create an unstored text calculation of ValueListItems( Status(CurrentFileName), "yourvaluelist" ) and put it on your layout. It will have to be refreshed, either manually or by script, when the data in textfield is changed.

[ChasCH]

Thanks Guys! Cool, but I'm a little fuzzy:

1. So I have created a field called "AlphaField"

2. Created a value List called "Alpha" that gets its values from "AlphaField"

"Create an unstored text calculation of ValueListItems( Status(CurrentFileName), "yourvaluelist" ) and put it on your layout"

Imposed this on the "ToBeFixed" field

ValueListItems( Status(CurrentFileName), "AlphaField")

Am I close??

Thanks Chas

[-Queue-]

You sound as if you're close, but I read no mention of the self-relationship. The record needs to be related to itself and only to itself using its unique serial number as the key. Then you base your value list on the related AlphaField, not just AlphaField.

[ChasCH]

OK....--Queue- would you look at this

I start with the stack:

alphaBetizeField.fp5

begins with three fields:

ToBeFixed - text

AlphaField - text

serial - number

1. "Create a self-relationship based on serial number"

the number fld called "serial" has an AutoEnter "next value increment" checked.

I've created a relationship between alphaBetizeField.fp5 and alphaBetizeField.fp5 based on the "serial" fld - serial = ::serial

2. "Create a value list using values from field relationship::textfield."

Value List Alpha uses values from field "alphaBetize::AlphaField"

3. Create an unstored text calculation of ValueListItems( Status(CurrentFileName), "yourvaluelist" ) and put it on your layout.

defined a "TextCalc" as a calculation field with Unstored, =ValueListItems(Status(CurrentFileName), alphaBetizeField::AlphaField)

Changed the fields - have gone to different records - nada.

I'm missing something....that's for sure...

[-Queue-]

Your value list is called Alpha, so your calculation should read ValueListItems(Status(CurrentFileName), "Alpha" ). I see no purpose in your AlphaField field. The value list should be based on your ToBeFixed field (since it is the one you want to sort), which shouldn't be an auto-enter of the value list calculation. It should just be whatever text is manually entered in the field.

You can also remove the sort from your relationship definition since there is only one related record and sorting one record does nothing.

See attached for changes.

alphaBetizeField.zip

[ChasCH]

That's fantastic.... All this time I was stuck thinking that the "Alpha" was going to re-sort the "toBeFixed" hence... well you know the rest.....

Many Thanks!

[ChasCH]

So -Queue-,

..... This is great, but WHY does it work?

I don't see why the TextCalc field (result) sorts alphabetically.

1. the value list is defined as the ToBeFixed field (source) thru a relationship

2. The TextCalc field (result)is a calc with "ValueListItems(Status(CurrentFileName), "Alpha" )"

Where is the thing that causes alphabetical sorting....

All the Best and with deep gratitude and suplication

Chas

[-Queue-]

This works because FileMaker treats unique words or phrases separated by carriage returns as unique items in a value list. This is similar to the way a single repeating field can be used as a value list. And value lists naturally sort alphabetically unless you define them to do otherwise. So by using a value list based on a field in the current record, you force it to resort the items in the field alphabetically. If you format a field to use the value list as checkboxes, radio buttons, pop-up menu or list, you can see that each line is indeed treated as its own item.

One caveat is that FM 6 only indexes up to 20 characters in a word and 60 characters in a line. So you'll need to make sure none of the items match beyond 60 characters and there are no words longer than 20 characters. Otherwise, your value list will be missing items and only showing the first of two (or more) 'duplicate' items according to the index.