One record per item

[Beaker]

This maybe easy or hard... I cant tell. In the database I am creating I must take care of tracking specific parts. Therefore I wanted to create a database of just items where each item would be its own record. With this item record would be fields: "Part Number" - (So I can ID the part) "Status" -(where the item is, ON ORDER, IN STOCK, SOLD, ECT) , "PO#" - (So I can look up where it was purchased from for RMA's), "Invoice#" - (So I know who I sold that part to), "Serial#" - (So each part can be tagged with this number" and "Price" ect... Now I can easily create these parts from a sepreate Purchase Order file. What I need to know how to do is; make it so when I add an item to an invoice in the Invoice file, It automaticly does a look up in the Item file and finds the lowest "Serial#" with the "Part Number" I input that has a "Status"=IN STOCK and associates/inputs the invoice number onto that item. I realize I may have to use a script to do this instead of a portal, but I was hoping to just use a portal instead if at all possible for easy of use. Eaither way I could use some help.

Thanks in advance

Gary

[Chuck]

Try sorting the relationship from the line items to the parts by serial number in ascending order. I think this will then pull the lowest serial number (first in the sort order).

Chuck