I am a newbie in Filemaker and am doing a database for timesheet and expense management along with project controls.
In order to properly calculate the project costs, I need to be able to get the payrate the the resource was paid when the work was done. We do have projects spanning over 2 to 3 years.
I have a table that links to the resource table which has a rate start date, rate end date and the hourly rate.
In my timesheet details I have a date worked.
I would like to do a calculation to populate the hourly cost on the timesheet detail which would lookup the hourly rate in the payrate table that was active as per the date worked.
I have Case( Dateworked >= RateStartDate and Dateworked <=RateEndDate; Hourlyrate; 0) and also
Case(DateWorked<=RateEndDate and Dateworked>=RateStartDate; HourlyRate;0)
But neither work.
Can someone advise what I am doing wrong