Sorting List of Related Pages

[Emma in England]

I am writing a database to create a complex and sophisticated index to a large catalogue.

I have a table of Categories - these are the actual text that appears in the printed index.

These are related to a table Page Entry which has a page number and a ProductName. The ProductName is related to one or more Categories.

So, the Category 'Barbecue' appears on pages 25, 32, 40, 41, 42 and 50.

I have to manipulate these numbers and eventually output 'Barbecue [tab] 25, 32, 40-42, 50'

I have got the numbers into my 'Category' field by 'List ( Page Instances::Page No )'. However, if the entry on page 42 is added later than the one on page 50, I end up with: 25, 32, 40, 41, 50, 42

How can I force that list to sort numerically? Must I script a bubble sort?

Sorry if I've got terms wrong, or if my whole approach is wrong - I'm self-taught and pretty much alone, so be kind and I'll try to learn!

[comment]

Related records are sorted by the relationship (or by the portal, which is not relevant here). Open the relationship graph and define the relationship to Page Instances to sort the records by Page No.

[Emma in England]

Yes, I've done that. But the List function uses the unsorted values. I tried defining the values as a Value List, which sorts perfectly, but I can't see how to get that into a manipulatable field! I'm happy to try another approach. I've got my head buried in a text book and hoping for inspiration...

[comment]

Yes, I've done that. But the List function uses the unsorted values.

No, it does not. There's probably some confusion regarding which TO you are using (I sure can't follow your original description). Why don't you post your file or - preferably - a simplified version showing only the problem.

[Emma in England]

Yes! You were right. Thanks so much. I'm wading about in stuff I barely understand, and missed that. I know it's a common mistake....

I wish I could describe this more clearly - I'm sure I'll have more questions! It's actually an existing system written in a very old version of Access....

[brian rich]

Perhaps you could use a custom function to sort the list into order.

There are a couple on Brian Dunnings Custom Function list. This one from Agnes Barouh looks as though it would do the job

SortList(ValueList ; Sort ; Type ) see - http://www.briandunning.com/cf/1181

So your calculation would become:

SortList (List ( Page Instances::Page No ); "Asc";"Number")

Hope this helps

Brian

[Emma in England]

Thanks Brian - I was just researching this sort of thing... but 'comment' has solved the problem for me!

I have now got as far as changing:

25

32

40

41

42

50

61

62

into

25 32 40-42 50 61/62

Now somehow I need to work on that until it's:

25, 32, 40-2, 50, 61/2

Not to mention pulling the most heavily populated page to the front of the list and making it bold (in InDesign)!

This is going to be a long struggle but I WILL get there!

[brian rich]

Try

Substitute( "25 32 40-42 50 61/62"; " "; ", ")

Brian

[comment]

I have now got as far as changing:

25

32

40

41

42

50

61

62

into

25 32 40-42 50 61/62

Now somehow I need to work on that until it's:

25, 32, 40-2, 50, 61/2

You didn't say how you managed the first part - and I suspect the second part would be easier to handle at the same time.

BTW, I am not sure that technically '40, 41, 42' (3 or more occurrences of the subject) is the same as '40-42' (a single discussion of the subject spanning 3 pages).