I have a complex database that is getting above my reach. I have a pay rate field, that puts every employee into a rate list, which determines what they are paid for each job. I have a drop down list to choose what room they are working in (rooms) and this is filtered by who is logged in, therefore only shows the list of rates for that employee. But I also need to further filter the list by only showing the rate for the room they select in their time sheet.

So a rate is determined by pay rate, room, then of course number of hours. My list now shows a pop up of all the possible rates for all possible rooms, and I would ideally just like to filter the list by the room they choose in their time sheet drop down list.

I have tried everything. Filemaker is kicking my butt, and have not slept in days trying to get this up and running for this company.

Thanks!!

Are you sure the rate should be set by the employee's room number? I would imagine that the rate ought to be associated with each employee identity, not with the room they're in. What if they change rooms?

Well, that is the point. Its a recording studio, and there are separate rates for each room. The employees get paid more if they work in a more expensive room, and anyone can log into any computer via their account. The more experienced engineers need less assistance, therefore their rate for the room is higher.

I don't see why you need "to further filter the list"; once you have selected the room and the engineer, the correct rate can be looked up from the Rates table via a relationship matching on RoomID and EmployeeID.

OK, that seems like a great suggestion, but it won't work. I tried creating a key/link to the tables, but it keeps showing me the full list of rates. Should the connection remain as is with the employee selection, since its working? or is that why the new link will not relate the records correctly?

It seems to work for me...

RatesByRoomLU.fp7.zip

thank you so much! That was exactly what I needed.

You are welcome. Note that I have made a shortcut with the rooms, linking by name instead of RoomID. This is not the best practice, since renaming a room will break the relationship.