In article <1122244502.793197.108880 (AT) f14g2000cwb (DOT) googlegroups.com>,
leon.obers (AT) iae (DOT) nl wrote:

This really depends on what you want to do with that list once you've
obtained it, and whether the names come from the whole database or just
the current Found Set of records.

For example, you could use a script to sort the records and then
generate the list, but this would be no good if you want the list
on-screen constantly updating.

Anyway, here's one method:

The easiest way would be to use two separate fields - one for the city
names and another one for the numbers.

To obtain the list of names (without the numbers) is to define a new
Value List that takes it's values from the City field - this will
automatically generate the list of unique names in alphabetical order
and update whenver new names are added to the database.

The names in this Value List can then be grabbed by a Calculation field
using the ValueListItems function.
eg.
CityNamesIndex Calculation, Text Result, Unstored
= ValueListItems (DatabaseName, ValueListName)

where DatabaseName is the name of the file you're using and
ValueListName is the name of the Value List you've defined to obtain
the unique City names.

This gives you a field containing each unique City Name from the entire
database, with each City Name on a separate line (ie. separated by a
Return / ¶ character).


For the numbers you can use another new Calculation field that counts
the number of values in the CityNamesIndex field above.
eg.
CityNameNumbers Calculation, Text Result
= Left("Nr 1 ---
Nr 2 ---
Nr 3 ---
Nr 4 ---
Nr 5 ---
Nr X ---", (PatternCount(CityList, "¶") + 1) * 9)


Obviously you'll need to add "Nr X ---" lines to cover as many cities
you'll possibly ever get.

This counts the number of Return / ¶ characters, adds 1 (to include the
last name in the list) and then uses that to add the correct number of
characters using the Left function (the * 9 is used because you've got
"Nr X ---" plus the Return character).


Then simply put the two fields on a Layout next to each other
ie.
[CityNameNumbers][CityNamesIndex]

stretched vertically as long as necessary.



Note: Despite the CityNamesIndex field being Unstored, it will not
update automatically on-screen when you add a new record with a new
City Name - you have to go to another record before the Value List and
therefore this field updates. \




Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Hello Helpful Harry,

Studying the given solutions plus reading the inside of FM (and
translating to my mother tongue language trying to understand), for my
purpose I did find two options that did the trick in an easy way.

- First is sorting the data, exporting it in a separate table, using
checkbox to group the contents (can only be checked when records are
sorted). In this way only unique records are exported. Loading this
table it is easy to give numbers that are following.

- The other way is by using:
"Identifying duplicate values using a self-join relationship".

Using the last method typically I run into trouble that I got 4 records
more with the message "Duplicate", as with the previous sorting and
export method of above. Difficult to find out what was wrong. Finally I
found it. Within the fill in window to define calculations, I had
forgotten to change the calculation result to "text" instead of
"number".

I will mention this to other users, as it is easily overlooked, and do
give false calculations without notice.

I will store your written solutions for later use.
Thanks for time spending to write and very much for help.


Regards, Léon Obers

Hello Helpful Harry,

Studying the given solutions plus reading the inside of FM (and
translating to my mother tongue language trying to understand), for my
purpose I did find two options that did the trick in an easy way.

- First is sorting the data, exporting it in a separate table, using
checkbox to group the contents (can only be checked when records are
sorted). In this way only unique records are exported. Loading this
table it is easy to give numbers that are following.

- The other way is by using:
"Identifying duplicate values using a self-join relationship".

Using the last method typically I run into trouble that I got 4 records
more with the message "Duplicate", as with the previous sorting and
export method of above. Difficult to find out what was wrong. Finally I
found it. Within the fill in window to define calculations, I had
forgotten to change the calculation result to "text" instead of
"number" (default is "number").

I will mention this to other users, as it is easily overlooked, and do
give false calculations without notice.

I will store your written solutions for later use.
Thanks for time spending to write and very much for help.

--
Vr.groet - regards, Léon Obers

Reacties per mail, vervang "invalid" door "cc" in het adres.
Reactions by mail, exchange "invalid" by "cc" within address.