I have a client database where work orders are entered and pricing is calculated depending on what kind of visit it is (scheduled, nscheduled, after-hours, etc).

Each client record has a field for price since prices can be different for each client. each visit type is Linked to a rate. There is a rate field so if i select "scheduled" as the visit type, the rate field becomes the amount on the "rate_scheduled" field via case statement.

Problem:

If a client is @ 100/hr rate for scheduled visits and they are invoiced, further in the future if that rate changed, invoiced records reflect the new rate, i would like to have it in a way where once a rate is on a work order, it does not changed if the rate amount changes (only new work orders will display the new rate)

Use a lookup instead of a related field.

that doesnt seem to work for the way i have it set up. each rate is a field in the client table so im not sure how the one "visit type" field can lookup to multiple fields in CLIENTS.

what i did was that on the WORKORDERS table there is a field called WORKORDERS::VISITTYPE, the VISITTYPE has 5 manually entered items [1. scheduled, unscheduled, project, after-hours and emergency response)

there is a field "WORKORDERS::WORATE" which is a calculation defined as follows:

Case ( WOVisitType = "Scheduled"; WOClient::Rate_Scheduled ;

WOVisitType = "unscheduled"; WOClient::Rate_Unscheduled;

WOVisitType = "After-Hours"; WOClient::Rate_AfterHours;

WOVisitType= "Emergency Response"; WOClient::Rate_EmergencyResponse;

WOVisitType = "Project"; WOClient::Rate_Projects

)

so here is a scenario

Client "ABC" has $75 on CLIENTS::SCHEDULED, I go create a work order and select "Scheduled" under WORKORDERS:VISITTYPE which according to the calculated WORKORDERS::RATE field, the case event gives it the value listed under WOClient::Rate_Scheduled. then that rate is calculatedwith the WORKORDER::TOTALTIME

im getting myself familiar with FM im working with the FM BIBLE book for v.10 but im just a little stuck on how to correct this, maybe i ned to restructure the way the rates are entered, but as i see it, every client must keep their own rates fields because they each have custom rates

Thanks in advance

What might confuse you is that what you have should be an autoenter calc and not a direct lookup as shown in say this template:

http://fmforums.com/forum/showpost.php?post/309136/

Where the PriceLU is a straight forward lookup, but what you have at present is an unstored calc, but what you need is to get the entire Case( statement working is an autoenter. If you turn to:

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

Would you see the other method, as highligted in the image above.

--sd

Thank you so much Søren, changed WORKORDERS::RATE to number type instead of calc, then in the auto enter of that field, added the field as a calculated value, once a work order is posted the it permanently keeps the rate that it was created under and does not change if the client rate changes

Edited September 10, 200916 yr by Guest