Jump to content

Alphabetize a list


ChasCH

This topic is 7096 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7096 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.