Problem with complex sort

[benwiggy]

Further to an earlier, somewhat lengthy thread:

http://fmforums.com/forum/topic/80142-create-auto-numbering-field/

Everything was working fantastically, until I foolishly re-sorted the database. Now I've got a problem.

To recap: I have a "serial" number that is generated from each record's position when the fields are ordered in a particular way. (In other words, the serial number is constant for a given record, regardless of how the data are actually sorted.)

This is done by first creating a field which joins together 3 fields, on which the order is based.

Substitute (List (Type; Title; Subtitle); ¶; " ")

The main table has a ≥ relationship to itself, and a field then counts every instance of the field whose value is "greater" (i.e. alphabetically higher) than itself. This produces consecutive numbers based on the order in the Calculation Field.

Anyway, the tricky thing is that one of the 3 fields listed above ("Type") needs to be ordered by a Value List. Now, it was working perfectly, until I recently re-sorted the database, Now, even though I have scripts to sort the database correctly, it seems to be producing the numbers based on an alphabetical order, rather than using the Value List.

In other words: now, when I sort the database according to the order I want, the serial number and the record number are now not the same, because the serial number is sorting the Value List alphabetically.

There doesn't seem to be anything in the definitions of the two fields involved in the sorting that mentions the Value List. Anyway, it's weird, because it used to work perfectly, and now it doesn't.

Does the Indexing of the field matter? The Serial number field has gone blank if I turn off indexing.

Thanks

[comment]

the tricky thing is that one of the 3 fields listed above ("Type") needs to be ordered by a Value List.

Yes, this is tricky - if you still insist on having the ordinal number available even when records are sorted in a different order.

Change the self-join relationship to:

Table::Type = Table 2::Type

and sort the related records from Table 2 in their desired order (i.e. by Type, Title and Subtitle - with Type following a custom order based on a value list).

Calculate the ordinal number as =

Let ( [

listIDs = List ( Table 2::SerialID )

] ;

ValueCount ( Left ( listIDs ; Position ( ¶ & listIDs & ¶ ; ¶ & SerialID & ¶ ; 1 ; 1 ) ) )

)

Further to an earlier, somewhat lengthy thread:

Please post follow-ups in the original thread.

[benwiggy]

Many thanks.