Trouble with repeating fields and portals

[BobWeaver]

DO NOT USE REPEATING FIELDS! You will regret it if you do.

It sounds to me that file B is organized just fine. I'm not so sure about file A. If file A contains the general quote information like customer name and address etc. then you should probably have third file (file C) to hold the line items for the quote. Then, you put a portal to file C on your file A layout so that you can add multiple items to the quote.

To get the proper prices, you just have to create the proper relationship from file C to file B. Typically, you create a ProductQty field that concatenates a product ID to the break quantity like so:

ProductID&""&Right("00000"&NumToText(BreakQty),5)

So if you have a product ID "ABC123" and a break qty of 100, this combined field will give you "ABC12300100." The quantity part has leading zeroes as shown so that the prices will lookup properly. In file C, you set up a calculated field with the Product ID and the actual quantity in the same format:

ProductID&""&Right("00000"&NumToText(Qty),5)

Create a relationship based on these fields. Then set up your price field in file C to auto-enter looked up value, and check the "use next lower value" if no exact match. This will automatically put the correct price into your price field when you enter the Product ID and quantity.

So, you see, nothing much needs to happen in file A other than holding the basic customer info.

Sorry, this is pretty brief. There are other topics somewhere on this site that go into this in a bit more detail. HTH

[mpearl]

I'm having great difficulty reformatting imported data.

I'm working with 2 files: File A (masterfile) and File B (related file). File A contains header records of sales quotations (quote#, Item#, Item Description, etc). File B contains the detailed item price break data.

Unfortunately, File B is formatted such that quotations (qty, unit price, unit cost, etc) of different quantities of the same item for the same quote# are listed in SEPARATE records rather than, say, in repeating fields.

I cannot seem to write an effective script which will move the separate quantity price-break data into repeating fields which could then be accessed through a portal in a layout of File A.

In the alternative, I have also been unable to format an effective layout of File A with a portal to the File B records; e.g., say File A contains 1 header record of Quote# 101 for one item. File B contains XX price break records for the same Quote# 101 for the same one item with one record each covering price quotes for 10pcs, another for 25pcs, 50pcs and 100pcs, totalling four(4) detail records. Whenever I run a search from my layout from File A (containing a portal of qty, unit price, & unit cost in 5 rows of repeated fields), the search returns four(4) records each containing the same data. Each record shows the 4 different price-break data in the portal. I want the search to obviously return only one(1) record with all of the price-break data.

HELP!

[mpearl]

Thanks so much for your reply but I didn't explain either correctly or fully the circumstances.

The qty pricing does not reside in a price-list as the items quoted are all custom, made-to-order parts. As such, the qty pricebreak data is only associated with the particular quote# of its time. However, the prior price data is used as a reference for current/future quotations and therein lies the rub.

On the one hand, I need to (a) look-up old quotes price data for ref purposes when working on new RFQ's and, (: be able to enter qty/price-break data for new quotations on-the-fly.

The trouble previously described was regarding (a): how to retrieve previous quotes showing quote#/seqItem#/part# for its various quantities/price-breaks all on one screen/report BUT showing the line item data (seqitem#, part#) only once for all of its associated qty/price-breaks. Since my LineItem file doesn't store the data this way, but rather repeats the lineitem data in each record for each qty/price quoted, I've found it (so far) impossible to accomplish.