AnsweredAssumed Answered

How can I get the value of a cell based on the value of 2 other cells

Question asked by mmainville on Aug 17, 2018
Latest reply on Aug 20, 2018 by mmainville



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


Thank you