I have 4 repeating fields in the database I that I currently use, and I am dying to get rid of them, but I am not sure how, any help does just that.

Some background:

The database is used at a manufacturing company which produces plastic in roll form. A typical order that we receive would be 10000 lbs of product, that might come on 50 200lb rolls.

When an order comes in, I create a record, which contains all the production data, customer info, along with specs. on each of the 50 rolls, i.e. weigh, length and roll number etc., these are the repeating fields. Most orders start with roll# 1001, however some customers require us to use a serialized number for their rolls. What I have right now is 4 repeating fields (50 repetitions each), one for roll #, weight, length, and pallet#.

Getting roll numbers to serialize in repeating fields is beyond me, never mind getting them look back at the last number used in a particular sequence.

Any suggestions?

May I ask if all the rolls on an order are identical? If so, is it strictly necessary to separate them? I mean could you just say, "50 rolls @ 200lb. Serial numbers 1001-1051."

If that isn't workable, then the way to do what you asked is to set up a "line items" file. It needs only the bare minimum of fields:

Order ID, Roll Number, Weight, etc. (Customer ID is not strictly necessary but can be handy for reporting -- make it an auto-lookup based on Order ID.)

You can import your existing orders, and when it asks what to do with repeating fields, choose split into separate records. At that point you can delete the original repeating fields in the Orders file. Then make a relationship based on Order ID, Orders::Line Items. Now you can create a portal and see all the line items for each order.

So. To create 50 line items for an order, you would want a global number field to use as a counter, a global to temporarily hold the current order id, and a global text field for the starting serial number. Also make a calculated field in each file called Constant, that = 1. Then make a relationship from the Line Items file on Constant. This is so we can access the global fields I just mentioned on every record of Line Items.

So, you would enter 50 in gCount;

and let's say 1001 in gStartSerial

Now for the script:

(in Orders)

Set Field [gOrder, Order ID]

Perform script [External, Line Items (see below)]

Go to Layout [Orders]

(in Line Items)

Enter Browse mode

New Record

Set Field [Order ID, Orders::gOrder]

#based on the Constant relationship, remember?

Set Field [serial Number, Orders::gStartSerial]

Loop

. Duplicate Record

. Set Field [serial Number, Serial Number + 1]

. Set Field [Orders::gCount, Orders::gCount - 1]

. Exit Loop If [Orders::gCount = 1]

End Loop