Hi,

I have a portal that lists items for an order. I would like to make the items a numbered list, with the number stored as a field in the item record, i.e.

1. Apples

2. Oranges

3. Pears

How can I autogenerate serial numbers that restart at 1 for each related portal?

Thanks!

Andrew

I think we need a little more detail of what the outcome is to look like.

Right now, it would appear you have

Apple

Oranges

Pears

in a list

Do you want the result to be

1.

2.

3.

Lee

@@

I have a parent table, Orders, and a child table, Order Items. Order Items is a portal in Orders. In addition to a normal Order Items Primary Key field, I want a unique numbered key that is specific to each order. So, for example, the fields in Order Items might look like this:

OrderID# OrderItemID# OrderItemLineItem# OrderItemName

123 _ _ _ _ 567 _ _ _ _ _ _ 1 _ _ _ _ _ _ _ _ _ _ _ _ Apple

123 _ _ _ _ 568 _ _ _ _ _ _ 2 _ _ _ _ _ _ _ _ _ _ _ _ Orange

123 _ _ _ _ 569 _ _ _ _ _ _ 3 _ _ _ _ _ _ _ _ _ _ _ _ Pear

124 _ _ _ _ 570 _ _ _ _ _ _ 1 _ _ _ _ _ _ _ _ _ _ _ _ Shirt

124 _ _ _ _ 571 _ _ _ _ _ _ 2 _ _ _ _ _ _ _ _ _ _ _ _ Jacket

124 _ _ _ _ 572 _ _ _ _ _ _ 3 _ _ _ _ _ _ _ _ _ _ _ _ Hat

So the question is, how do I generate the OrderItemLineItem# field? Does that make sense?

Edited January 31, 200620 yr by Guest

Vaughan gave you the answer. Add the 2 @'s to your portal row. Not the line items table.

Unfortunately, that won't work -- I need the numbers stored permanently in a field, so I can reference them in other places as Item 123-3, 124-2, etc.

This is not a very good idea. What's supposed to happen if an item is deleted? If the subseqent items get re-numbered, you will lose your reference to the "other places". If they don't, you will not have a sequential list anyway, so why not simply put a serialID in Items and use that as reference?

IOW, stick to the "one fact per field" rule: the item's individual identity is one fact - that goes into the SerialID field; the item's parent record is another fact - that goes into the ParentID (foreign key) field.

I agree with you... it isn't a good idea. Unfortunately, it's the way the original database designers did it and would be impossible to change at this point.

I could just mimic the techique they are using (cumbersome scripting), but I was wondering if there's a better way -- this system was designed back in FM5. Sounds like there isn't...

You could define a self-join in Items:

Items::ParentID = Items 2::ParentID

AND

Items::SerialID > Items 2::SerialID

and sort the related records on Items 2 side by SerialID, descending. This makes the previous record for the same parent the first related record. Then you can auto-enter:

SerialIncrement ( Items 2::Number ; 1 )

But such schemes are always vulnerable, especially when 2 users are creating records simultaneously.