Hi everyone,

Well, I just about have this set up. I have one additional problem. I have Orders db with OrderID and LineItems db with OrderID. I have a LineItem# which is set to auto-enter serial number (01 etc).

My problem is that the lineitem numbers keep getting bigger and I want them to start over for each new order. Lineitems are created when adding in a portal in Orders. Is there a way to make the serial number start over for each new order? If so, I can use a calculated field of OrderID & LineItem# for the Unique lineitem ID.

I tried using a self relationship in lineitems but got myself totally lost.

Pete

I I understand, you're trying to rank each item belonging to the same order.

What you could do is create a SelfJoinOnOrders.

Then, add a new calc c_nextLineItem = Max(SJonOrder::LineItemNumber)+1

Then, define your 'LineItemNumber' field to be a lookup from the c_nextLineItem using the SelfJoin. Define the lookup to use 1 if not found.

Finally, you could have your lineID be a calculation of type :

c_currentLinePerOrder = "Order"&" "& Order_ID&"-"& lineItemNumber.

Note though that this set up could break when deleting items, and is not recommanded if many items could be "ranked" that way.

You'd better use a script to do the job, and still use another Unique Id for any line.

Ok, well I think I'm making this more complex than it needs to be. As long as each lineitem has a unique id, I guess the number of the lineitem doesn't matter at all.

I will be printing from a portal which currently displays record # and so they are never holes in the numbers anyway. I guess it isn't even necessary to show the number sequence of lineitems as set data within lineitems (since I sort the relationship by ItemName) so I don't need to worry if an item is deleted. I guess my question is ... why have one? The order total shows the total # of items ordered anyway. Anyone else do anything with item numbers?

BTW I will be using fixed portals to print so I won't have to worry about overrunning each portal.

Pete

RE: BTW I will be using fixed portals to print so I won't have to worry about overrunning each portal.

That is not the best way. Print from LineItems file. Then your invoice can have 1 or 20 pages.