Look Up Cost From Table
I am working on a layout to compute service income based on hours X service rate. I have a table (TABLE A) that contains hours and service_id and another table (TABLE B) that contains service description, rate, etc. I created a relationship between the two tables based on service_id. In TABLE A, I created a field (service_cost_lookup; TYPE: number; OPTION: Lookup). I input several records to test lookup and it appeared to work fine, however; if I changed the rate in TABLE B, the service_cost_lookup in TABLE A changes. I then created a field in TABLE A called service_cost_rate as a calculated number field (service_cost_lookup X 1) and the lookup appears to work without problems. I can change the rate in TABLE B and it does not change the service cost_rate, however; the service_cost_lookup will change whenever I change the rate in TABLE B. If I change the service_id in TABLE A, the service_cost_rate will change to the latest value in TABLE B.
Should the lookup value for service_cost_lookup remain static after the original input of service_id unless I change the service_id?
I appreciate any comments or suggestions.