update file1.fieldA when file2.fiieldB changes

[TeofenGL]

Hi,

Would like some advice on the following problem we are having, which I'm sure is just a matter of overlooking something.

We have a file1, which lists items, their size and a price for this item calculated from size times the price/unit at that size range

once a week we receive a file which provides us with the different size ranges and the price per unit at each of the size ranges.

we have set up a relationship whereby the price/unit field of a product in the first table can be retrieved from a matching unit-price-at-size-range record in the reference table, HOWEVER

(this is where the problem lies)

when the reference table is flushed each week, we need the actual prices in the product table to change - the value is calculated OK for a new item added, but we need some way that all 10000 or so items would be updated with new information, essentially live, or at least on command...

any thoughts?

thanks in advance

[QuinTech]

Is your calc field set to unstored? (Under Storage Options in the calc definition.)

Jerry

[TeofenGL]

hmmm the field type is "number"...which does not seem to have the option for unstored... if i try to make it a "calculation" type field, it won't let me on grounds that another field is using it...

[TeofenGL]

perhaps this bears clarification...

i am linking these 2 tables on a hybrid calculated field which links the type of items and the size range into one entity...

then using that index, the price-per-unit-at-range is a "looked up" number - thus, a number, not a calculation as such.

[QuinTech]

Why is your Price-per-unit-at-range NOT a calc field? It's always equal to size times price, right? So your calc field should just be the product of two related fields, ReferenceTable::Size * ReferenceTable::Price. Right?

J

[TeofenGL]

the price-per-unit-at-range is a set field and it varies depending on the range (diamonds).

the ref table entries look like this: (simplified and unreal data)

MIN MAX PRICE/C

0.01 0.03 100

0.04 0.07 105

0.08 0.14 130

0.15 0.17 135

etc

there is no calculation that will get me these values, and this is the information we are getting from an outside source.

the range goal-posts are, thankfully, set, and never overlap.

the combined index (from the side of the product table) is determined like this:

Case(

Weight>=.01 and Weight <= .03, ".01-.03",

Weight>=.04 and Weight <= .07, ".04-.07",

Weight>=.08 and Weight <= .14, ".08-.14",

(etc)

)

in the incoming reference table, i just combine the goalpost values to form the same information.

i have a relationship between the tables defined as indexed on this combined field.

then, when a new product weight is entered, that case statement is triggered and an appropriat value put into the "range field" for that product - then the price/u@r can be determined (lookup) from the incoming ref table...

i hope that makes sense...

[TeofenGL]

bump. anything?

[Ender]

Could this be done with a lookup? If no exact match, use next higher (or lower). A Relookup would then update the prices.

[TeofenGL]

how would i do a relookup in a few thousand records?

[Ender]

A relookup can be done by putting the cursor in the key field (of the lookup's relationship) and selecting Relookup from the Records menu (this can also be scripted.)

[TeofenGL]

ahhhh yess thankyouthankyouthankyou.

i didn't even know "relookup" existed, but i looked around, found some instructions, and this is indeed the way the cookie crumbles.

just for the record, when the range prices table changes, i select the combined index column (the key we're using to join the 2 tables) in the main pricelist, and do records>relookup. (this despite the fact that the actual looked up column is price/unit...)