using lookups in repeating fields

[Betsey Nord]

I am really new to databases, so please use small words and short sentences.

I would like to have lookup fields in a repeating field. Is this possible, and if not what is a good alternative?

I have steered away from portals because only some of the fields are related.

It is a simple purchase order form with a repeating field of products. These are products I order from at least 30 different companies. I am an end user in a chemistry lab. I want to be able to enter the product id and have the name come up automatically, but not the price, or quantity. I self joined the table but product id and used the look up option for the product name.

Thanks for your time.

[Vaughan]

Don't use repeating fields for this, go for related records in a portal.

The big "problem" with repeating fields comes when you go to get a list of all the stuff you've ordered: if the data is in repeating fields you cannot list it or count, because each record can have one or more items.

If each "item" is an a separate related record, however, counting and listing them is very, very easy.

I've seen simple invoicing solutions using repeating fields that have worked well for years. Until somebody wants to get a report out of the system, that is; then the limitations of the data model using repeating fields becomes obvious. So too does the fact that several years worth of data entry is going to need to have major surgery to get any meaningful information out of it.

[Betsey Nord]

How can I use a portal if the only fields that relate souly to eachother are product id and description. In the purchase order form I have a line of repeating fields that include "date of arrival", "product id", "product desription", "quantity ordered", and "price of product". Product id and description are related, but the rest will change with each new purchase order.

[TerenceM]

For your purpose you probably want to create a Join table that stores all the info for each purchased item, and view that table in a portal on your PO table. The structure would be like this:

Table: POs

POnumber as number

Table: POJoin

POnumber as number

ArrivalDate

ProductID

ProductDescription (auto lookup from Catalog)

QuantityOrdered

Price (auto lookup from Catalog)

Table: Catalog

ProductID

Relationships:

POs:POnumber to POJoin:POnumber, set to allow creation of records in POJoin table

POJoin:ProductID to Catalog:ProductID

Your PO layout should show the current PO, with a portal showing all the related records in the POJoin table. To enter a new item on the PO, simply type in the ProductID on the last line of the portal. This will create a new record in the POJoin table, with the POnumber set to the current PO, and the ProductID as you just entered, and will trigger the auto lookups. This way you can deal with the price changing on future orders without affecting your old POs.

-Terence