Jump to content

update file1.fieldA when file2.fiieldB changes

This topic is 7144 days old. Please don't post here. Open a new topic instead.

Recommended Posts


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

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?


Link to comment
Share on other sites

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)


0.01 0.03 100

0.04 0.07 105

0.08 0.14 130

0.15 0.17 135


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:


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

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

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



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...

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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...)

Link to comment
Share on other sites

This topic is 7144 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.