Does anyone have a sample of how an effective date is calculated?
Could you please elaborate on your requirements here please! To which effective date are you reffering?
I have created a table called “Employee Rate Components” which supports the existing Navision table called “Employee Rate”. “Employee Rate” holds the pay rate by “Earnings Code” for an employee. My new table will hold the component rate (e.g., shift differential, base rate, hazard duty pay rate, etc). This table will also hold the “Effective Date” for the component entry. If the user wants to globally adjust the Hazard Duty Pay Rate entry for an employee, they can run a report that will calculate the latest effective date for a given component entry and change the pay rate. But on the Navision table “Employee Rates”, there is not an effective date. I want the Amount field on this table to look at the new supporting table and sum the rates for any Earnings Code based on the supporting table’s “Effective Date” field for that Earnings Code. I only want to look at the latest dates in the table, even though one record’s “Effective date” value may be different than another record’s “Effective Date” value. The way it works now is that every component entry for a specific “Earnings Code” must have the same “Effective Date” value in order for the parent table to calculate the right Amount for each Earnings Code in the Employee Rate table. With the correct code, the net effect would be to look at the existing Navision table and see an “Amount” field that is a summary by for that Earnings Code. That is to say, the “Amount” field would be a sum of the supporting table’s records filtered to include only the latest “Effective Date” from the supporting table. When the user clicks the drill down arrow, they would see only the latest supporting records based on the “Effective Date” of each record. With the change I would like to implement, the “Employee Rate Components” table would not have to have consistent “Effective Date” values in order for the “Amount” field to sum by the latest “Effective Date” in the table.
David I wish I had never asked the question! I now understand the concept but I think I will leave this in the hands of the developers who, hopefully, will be able to help you here. However I am still a little confused as my version of Navision Manufacturing 2.60 does not have an “Employee Rate” table. I believe the handling of costs is done by machine centre (which can be a person), so this would be split between the base rate and hazardous rate, although there is no way to catch shift differentials. It is unusual not to assign employees to tasks but they seem not to, but handle it via machine centres. Still I am new to all this and will try to overcome this problem when I am asked to! Apologies if I have wasted your time but thank you for the information and education. Steve