I have a script step which sorts by a custom order, involving three fields. The first of those three fields is set to sort using a custom order based on a value list, which is as follows:

Red

Yellow

NI

Green

Note

In the sorted results, items appear in this order (there are no entries for NI at this time):

Green

Note

Red

Yellow

This is in the wrong order. I tried selecting "Sort Records..." from the Records menu, and removing the other two fields (to sort just on this field), but the results are the same. I suspect this is a bug, but the same, unchanged setup was working perfectly before.

The only changes I have made were to split the solution into two files: one with the layouts, etc. and one with the tables. I have removed the tables from the file with the layouts, and set up the relationships, file references, etc... all of the actual data seems to be coming over quite perfectly, but the sort doesn't work!

Can someone please help me with this problem?

FileMaker Pro 7.0v2, MacOS X.2.8, B&W 400MHz G3, 640MB RAM

Are you *sure* the sort is set to "custom" (with the correct value list selected) and not "ascending" cause it sure looks like it's sorted ascending to me.

Quite sure. As I said, it was working before, but it doesn't work now.

I can't figure out what might have changed here, other than the fact that I am now bringing in the table as a reference to an external file.

Is the field you are sorting a text field? or did you accidentally make it a number field?

It is a Text field, marked as Indexed.

It seems to work fine for me.

Here is an example setup with two files - how does yours differ?

(I changed the example to have no table in the interface file - I forgot that part.)

I'm not using a portal. The 'Report Data::flagColor' field (your 'colorData::color' field) is part of a table from the data file. That table is referenced from the interface file, which uses that table as the main table of the layout. Other data is included in header/footer sections, along with a title header -- that continues to work properly. The data in the wrong order is in the body section, and the sort is handled via "Sort Records..." from the Records menu (or from the script equivalent, rather -- but neither one is working).

Only the sort order seems to be incorrect at this point, and I can't figure out how to fix it.

The value list is defined similarly to yours (w/o 'Blue'), but in the Interface file, and then referenced as an external list from the 'Data' file. (I tried switching this, but doing so had no apparent effect, so I switched them back).

I also tried modifying the value list, thinking that maybe splitting the files would have required some kind of 'handshake' to revalidate the list, but this had no effect either, so I switched that back too.

Taking a hint from your example, I made a quick temporary layout with a portal, using a similarly related table (which already existed), and made the list in the portal. Same incorrect sort order.

Strange problem.

Oh, and I do have one table in the interface table, which is related. That table is pulling a container field onto the report based on the color value -- for a value of 'Green', it shows up as a circle filled with green, for 'Yellow', a circle filled with yellow, etc.

These are matching up correctly, but the records themselves are in the wrong order.

That's a bug. Take a look at this sample, both portals are sorted using identically defined value lists.

The first one works, the second does not. This is cause its sorting by the first value list in the data file, its not reaching over to get the interface file's value list.

Yes, and that's the *exact* incorrect order I'm getting from my database, too.

I will see what I can do to report this to FileMaker's Tech support.

Thank you, that is a big help.

Do you mind if I send them your example file to help demonstrate the bug?

Feel free to use my file.

I also already entered a bug on their website, and included a link to this thread.

Fixed by FMP 7.0v3.