parsing and lookup

[rogermax]

I will give much background to hopefully help in understanding my problem.

I have two db's ESTIMATES and PRICEDATA. PriceData contains many types of steel rule (products) and prices per in. We allow 3 tiers per rule type based on linear inches used. one record contains five fields, PerInch, PerInch Over, PerInchOver2, Overlimit, Overlimit2.

RuleType1 1.20 per in

1.15 per in over 80 in.

1.10 per in over 150 in.

In the Estimates db the fields are looked up based on RuleType chosen. We allow up to 5 types of rule on each estimate...RuleTypeA, B, C, D, E. That means I have to allow 25 fields for potential looked up data.

I would like to just use relations for the data, but can't because once the estimate is given, I don't want the numbers to update if we increase prices in the PriceData db.

Open thought and question:

Thinking about using a calc field in the PriceData db to concatenate all five fields with commas or spaces (don't know which might be better) ie: RuleTypeCalc = (1.20,1.15,1.10,80,150), then simply lookup that one field and parse it in the Estimates db. This would reduce it to five fields instead of 25.

My Question is how to parse that field. Each value won't necssarily have the same number of digits each time. Or should I just go with the current way. I am hoping to save some overhead and speed.

If there is a better way I am all eyes

[ August 18, 2001: Message edited by: Roger Maxwell ]

[spragueg]

Your best bet is to use lookups to "capture" the estimate at the time it is given. Then if you change the price the lookedup value wont change unless you do a re-lookup.

Respond if you don't know how to use lookups and I'll try to explain in greater detail.

Thanks,

G

[rogermax]

thanks. That is what I am doing now. I was hoping for a more streamlined approach but this seems to be the best answer for this situation. I even tried (successfully) using the GetField() command for a semi-static approch, but decided that it too would be unacceptable. Though it would reduce the number of fields in the db by 50 or so, and shrink the size of stored data considerably, I found that I must have the static looked up values for this solution.

[ September 17, 2001: Message edited by: Roger Maxwell ]