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

Hello,

 

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

Outcomes