Hi:

I have a cost field on an Invoice Table which is a look-up from a Pricing Table.

How should i handle the cost increase of 4% that is effective on September 1

while still maintaining the present price structure.

If it's a genuine lookup wouldn't raises affect older invoices, your solution "should" have a structure in the vicinity of this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

...and it would be smooth sailing!

--sd

My mistake.

Yes the look-up is formatted correctly and historical prices are intact.

What I meant to say is for orders dated from today until Aug. 30 the present price is in effect.

And for orders dated from Sept. 1 the new price would be in effect.

Is there a correct way to make lookups, beyond being a choise of autoenter.

However do I think the correctest way is to dupe the entire found set of items after having added two new fields showing start and end of the timespan where the particular price works.

The two new fields are then linked to an unstored field which is a second criteria for the relation making the lookup work.

--sd

The simplest way would be to wait until August 31, then change the prices. Otherwise you'll have to do as Søren says and keep two sets of prices (two records for each Product in the Pricing table). It's not necessary to have 2 date fields for the range, though - one PriceEffectiveFrom field should be quite enough.

Thanks Soren and Comment