Repeating Field Dilema

[wildsicilian]

OK - I've heard/read all the "repeating field" criticisms and discovered many of their deficiencies - but, I don't have easy answers for replacing repeating fields.

I have developed an accounting system and it is loaded with repeating fields.

1. Could I have suggestions for changing the body of an invoice with 150 items (repeats) listed (40 fields per item)to nonreapeating functionality? The current file has 1700 records with all this repeating.

2. Purchase entries have up to 200 repeats on one invoice.

3. The entire system incorporates approximately 40 files with scores of relationships, portals, lookups, etc.

All recommendations would be appreciated.

------------------

Kathy Eldredge

the Wild Sicilian

[LiveOak]

The usual approach is to break an invoice file (or orders or purchase order, etc) into two files Invoice and Invoice Line Items. The Invoice file stores all the common information for an Invoice #. The Invoice Line Items file stores the line items on the invoice and replaces the repeating fields.

What should be done about your solution depends upon the context. If it is just for your use and it meets your requirements, there may no be a reason to rewrite it. If you are selling it as a shrink wrap solution, I'd probably eliminate the repeating fields for line items. The hard part would be converting the data from the exisitng structure to the new structure. Maybe you don't have to bring in the historical data. If you do, the scripting to do the conversion to a Line Item file structure will get ugly. -bd

[BobWeaver]

To convert existing data, I would import it from the main file into the line items file using the "Split repeating fields into separate records" option. That shouldn't be too bad.

[wildsicilian]

Thanks to all !!

I will use import "split" this is already set up for posting to General Ledger file.

Will I have any problem with complex calculation totals by splitting up the repeating fields?

------------------

Kathy Eldredge

the Wild Sicilian

[LiveOak]

Just splitting your repeating fields into single records won't solve your problem. You will have to rethink the file structure of your whole solutions to use a related file instead of repeating fields. "Complex calculations" may need to be revised. As to whether you'll have problems with them....?

-bd

[wildsicilian]

Thank you again for your guidance.

I am planning on setting up a third file that the user cannot access to store records containing the inventory items related to a specific invoice with a relationship to that invoice in the "Invoice" file.

The "Inventory" file would remain as is with all inventory data, the "Invoice" file would store the Invoice no., date, total, etc and have relationships to customer file and the new "Line Item" file.

I have no problem with changing complex calculations;however, I am confused on printing.

Should the print layout be set up in the invoice file with portal (doesn't seem right), a 4th file for printing formats, or the line item file in columner layout with Invoice/customer info in header?

[LiveOak]

Easy question, print from the line items file. Reference whatever invoice fields you need using a relationship built upon the index that connects the line items to the invoice file. This will allow you to have invoices of any length. Use a subsummary parts to present the invoice information (leading) and the totals (trailing). By using subsummaries (and a sort) you can print one or more invoices at one time. If you were to use a trailing grand summary part, you would have to deal with a found set of only one invoice at a time. -bd

-bd

[wildsicilian]

BD

Thanks for all your assistance on this issue. I designed a solution this weekend that the client is thrilled with.

8 Relational files, 5 self joining relationships, multiple layers of subsummary reporting and NO, yes, NO repeating fields - all thanks to guidance from this forum.

Kathy