List sorting with custom sort order

[brian rich]

I have a list which is generated from two separate tables, and consists of letters and numbers - for example 

 

Table A gives the list: 2, 3, 4, 4A

Table B gives the list: A, B, 1

 

I would like to concatenate the lists and then sort them into order thus:

 

Concatenated and sorted list;  A, B, 1, 2, 3, 4, 4A

 

Filemaker's sort mechanism allows you to sort records based on a value list.  I can get them into this order with a sort using a value list like this  (I've missed a few values out!)

 

A, AA, B, BA, C, CA, D ......Y, YA, Z,1,1A, 2, 2A, 3, 3A ........24, 24A, 25

 

 

Has anyone got a custom sort function which will sort a list in the same way, or point me at the algorithm used to sort based on a value list?

 

Thanks

 

Brian

[LVie]

Brian,

 

Its unclear what you are doing with the 2 tables. Have you combined the records from them? If you haven't combined them do you plan to? If you have combined them why can't you simply create a value list from the resulting field of interest? How will the value list be used?

 

I'd love to help if I understand what you're trying to accomplish.

 

Levi

--

Laura (Levi) Vie | [email protected]  | 573-874-5176 | iChat:  leevie

[brian rich]

Levi

 

The two tables hold information on manufactured parts; one table holds current parts, the other holds archive parts.  The alpha and numeric values in the list represent incremental issues of the parts, the issue characters changing as the part is revised for engineering or production use. In general, the parts have an alpha character when in engineering and a numeric character when in production.  A typical sequence of issues might be:

 

10000.A, 10000.1, 10000.2, 10000.3, 10000.4, 10000.5   (first issue was in engineering, all the others are production revisions)

 

OR

 

20000.A, 20000.B, 20000,C, 20000.1   (three engineering revisions, with one production issue)

 

To add to the fun, you can also have transition parts (a part undergoing change but not yet approved) which has an A after the issue character e.g. 2A or CA)  There is only ever one specific issue of a part; in general, old issues will be in the archive, newer issues in the current file. 

 

I need to be able to temporarily build a list of all the available issues for any part, whether they are in the archive or current list - hence the need to concatenate data from each table. The problem is that I need to sort the resulting list to be able to see the progression from start in engineering to latest production issue -  and neither a numeric or an alpha sort gives the correct result.  

 

I don't want to resort to pulling the information from both tables into a temporary table just so I can use Filemaker's custom list sort.  There are several custom function which sort lists, but none use a custom list. I was hoping that someone might have an idea how to modify one of the existing sort custom functions to use a custom list, as I'm not sure what the algorithm is for such a sort.

 

TIA

 

Brian