Mutiple Rate x period (issue date and expiry date)

[fperezl]

Hi, i a m new to filemaker and i am having a hardtime with the logic behind it. I have created a database for tracking my freight business. I have rates depending on the following criteria : origin and destination of the trip, client, and subclient, rate, etc. ( this is the rate table)

Depending on these fields (which are in a table) i do the programming of the trips on a second table which i called "TripProgram". Now, if my customer changes its prices i would need to enter a second entry in the first table to include the updated rate. However, i dont know how to have filemaker lookup in the RATE TABLE based upon ISSUE DATE AND EXPIRY DATE OF THE RATE how to select the desired rate.

I was told by a friend that i needed a trigger event or something like that but i am not sure.

The purpose of my program is to keep the history of the adjustments in rates but at the same time have the program lookup the rate for the period.

I hope this is clear. Please help me. Any help is appreciated.

thanks,

[Wim Decorte]

I don't think we have enough information about the structure of your solution to really help you out.

Typically one would keep a history of pricing in a separate table (related to the quote by quoteID).

i don't think you need a trigger event (which FM doesn't support natively, only through plugins) but you'd need a scripted process.

[comment]

I am also not too sure I understand your question, so this may not be the answer you're looking for:

In the Rates table, have a date field EffectiveDate. This signifies the start of the period for which this rate is in effect (there's no need for an expiry date).

Make the relationship to the Rate table include the criterion:

Trips::Date = Rates::EffectiveDate

(this is in addition to other relevant criteria which pick the correct product from the Rates table).

Define the lookup to "lookup next lower" if no match.

[fperezl]

Ok. I will try to explain it again. I have more than 5 tables but the following are the most important : TripProgramming, TripExpense, TripInvoice, EmployeeDues, TripRates (includes rates per client, subclient, origin and destination). The issue is that my client and/or subclient (which vary) may change the rates per destination or origin and therefore, i would need to go to my TripRates and modify them. If, I modify the rates then the history of that particular origin,destination,client, and subclient may be erased or updated. My intention is to be able to define a range or lifetime of the particular rate and keep the history. Thats why i thought of an issue and expiry date.

By reading both of the posts, i am not sure if is should :

1) Update the rates as the clients and routes are added.

2) Have the system lookup in the TripRates the particular rate assigned to the period.

I don't know how to define in TripInvoice ( how to pick or choose the right ID (which includes origin, destination, client and subclient, rate ) if i have two entries with almost the same information.

For example,

ISSUE DATE = 1/1/2005

EXPIRY DATE = 7/1/2005

ORIGIN ---------> FLORIDA

DESTINATION ---> NEW YORK

CLIENT ----> XYZ COMPANY

SUBCLIENT --- TSHIRT KING

RATE ---> $1,000

ISSUE DATE = 7/1/2005

EXPIRY DATE = 9/1/2005

ORIGIN ---------> FLORIDA

DESTINATION ---> NEW YORK

CLIENT ----> XYZ COMPANY

SUBCLIENT --- TSHIRT KING

RATE ---> $1,500

If i have the same entry twice in my rate table but one with a rate and a different date how can i make the database lookup the correct rate table for my trip is lets say that my trip is on the 8/1/2005.

How can i have the system invoice and charge the trip with the correct rate.???

It sounds confusing to me.. Trust me, i am breaking my head..

please help me !!

[Søren Dyhr]

Filemaker does not provide triggers in SQL sence, but does instead have calc'fields and a feature on Lookups to make a keying lookup to pick the next higher value...

I made you a template of how I would approach th matter. I'm a little dissapointed with myself, that I can't get my way around compoundkeying wich belonged to pre. 7.0 versions!!!

--sd

conjunxion.zip

[comment]

how can i make the database lookup the correct rate table for my trip

About the same way a person without a computer would do it. That is you look at the date of the trip, then find the LAST price that was "published" BEFORE the trip date.

S

PriceByDate.fp7.zip

[Søren Dyhr]

S

[comment]

where do you take the precaution that prevents a combination not existing to lookup

That is a good point. I used the classic (pre-7) model of range lookup. Even before 7, if you used lookup next lower/higher, you've lost the ability to provide for a failed lookup.

I guess the classic answer would be to use product codes to identify the product with certainty. With a multi-criteria relationship, as in the present example, the classic answer may not be good enough. So it's time to think of a new model - see attached.

PriceByDate.fp7.zip

[fperezl]

Why discard the option of

FROMDATE 1/1/2005 TODATE 1/5/2005 in the examples (using date ranges). Using the new higher or next lower seems interesting. But, why not use a range to avoid future issues.:

Do you guys see it viable to have a price list for all the modifications or an addtional table to keep the history ? If history ? then how can one keep a separate table for all the changes.

I guess my confusion arises because i used to track all the information using Excel. Then, the registry was too long and the typos when invoicing were too many. That's why i decided to buy filemaker to track my business. But, i have had many difficulties understanding the logic behind it. I guess all my questions surged when i first tried to migrate all the data. I had to create new fields and new rates.

Anyway, i will give it a try with the low and high lookup option and see how it goes.

Thanks to all, any suggestions are appreciated..

[comment]

You could use a range as well, but then every time a price changes, you'd have to "close" the current price in effect, and "open" a new one (i.e. enter the same date twice). Why bother? This way, every time you declare a new price, the previous price is automatically terminated.

I can see no problem in keeping all prices, past and current, as individual records in the PriceList table. Once in a couple of years, you can export historical data and purge the file, but it shouldn't be necessary unless it grows really big.

[fperezl]

Veteran,

I will give it a shot and try it. I think i am missing something here. I will let you know

thanks,