I'm far from an expert, but I think you may need to provide a little more info to get someone to help/respond
I think u should create a lookup field for Tracking history of price changes.
wow...sorry about that. clearly, filemaker doesn't make you clairvoyant.
The situation is this: we track the rate that we charge clients in Clients::BillingRate It occurs that instead of creating a new occurance of a client when we increase their billing rate, we would need to figure a way to track the history of the rate, OR, be able to apply special rates as events warranted.
What I was thinking was to create three billing fields, which would have a dollar figure applied to it, then a radio button set to turn a rate on or off. a third calculation field would then hold the field and then paste it into the event record as a global value at the time the event was booked. Whenever the rate was changed in the client record, you could then see the adjustment in the individual event records. I will probably add date fields to the billing fields to track when the rates were bumped one way or the other...
other thoughts on a good way to do this?
Why not define a related table (linked to clients) of billing rates with an effective date that records the date the rate change went into effect? A relationship that sorts by the effective date in descending order will enable you to set up a lookup that copies the current rate into a number field for each new billing record that you generate for a given client.
So, a new table called Billing_Clients, related to clients, would have different rates and dates of adjustment of those rates. The client billing function would then paste the most recent figure out of that table into the event record to manage the billing function down the line...
Sounds good. i will give that a crack.
The related record won't return the most recent rate unless you also specify the correct sort order as part of the relationship. There's a looked up value field option that can be used to copy this rate into a rate field in the parent record.