creating conditional lists

[staypuffinpc]

I have a db, ItemDescription, and a db Checkoutlist, which points to a portal in ItemCheckout. People can check out numerous items from this portal, using the value list ItemDescription::ItemName. I have created a script for each record (ItemName is unique) that creates a field with the appropriate number of items for checkout in the form of n+1 on each line. e.g. If there were 5 Hyperstudio cd's available for checkout, the generated list within the Hyperstudio record (in the ItemDescription file, where it is created) would look like:

1

2

3

4

5

What I want to be able to do back in ItemCheckout is to click on ItemName, choose an item from the pop-up list, such as Hyperstudio, and then have the appropriate number list appear. Therefore, if someone checked out a Hyperstudio CD a list of 5 appears in NumList, whereas a list of 3 appears if they choose Peerless Drive. How do I get this to work without creating a separate record for each item (i.e. Hyperstudio CD 1, Hyperstudio CD 2, Hyperstudio 3, etc.)?

Thanks ahead of time.

Peter

[CobaltSky]

Relationship based conditional menus produce values based on the index for the field on which the list is based.

If you don't want to create a separate record for each item, the alternative is to place the items within a field on one record, separated by carriage returns.

The carriage returns will ensure that the items are indexed separately, even though they reside within the same record.

[staypuffinpc]

I've done that, but what happens when I do that is that the number list created is not relative to the item. I've got 32 Hyperstudio CD's, but the list goes 1-100 (and it's sorting by the first digit, hence:

1

10

100

11

12

13

14

etc.

What is wrong?

[BobWeaver]

It's sorting as text (on the first character, then second etc.) rather than numerically. Alas, you can't make it into a number field and still have multiple entries in it. But, you can put leading zeros on the numbers to get them to sort properly. May not look to good though.

001

002

.

.

.

010

011

etc.

To add leading zeros, use this formula:

Right("000" & NumberField,3)