So I'm working on a database where I need to record tasks that staff members have manually inputed in relation to a project. In order to invoice these tasks I need to assign billing rates to each staff member. However, a staff member's billing rate is dependant on two things: First the client that we bill to, and then to what class they are in relation to that client. For example Staff Member A could bill $100.00 to Client A while he bills $150.00 to Client B, and Staff Member B could bill $120.00 to Client A while he bills $175.00 to Client B, depending on who the client is. I've already set up a table that looks like the image posted below, which differentiates between client values, staff values and individual rates.
I've associated the "Client Value" between the Bill Rates Table and the Clients Table (which is related to the Projects table, because we have one client per project), and I've associated the "Staff Value" between the Billing Rates table and the Staff Table ( which is related to the Tasks Table, because one staff member can complete many tasks). So far I've only been able to either populate a Rates field with one Bill Rate value in relation to the Client, or one Bill Rate value in relation to the Staff Member.
So how can I fix this in order to automatically populate a field with a bill rate that is determined by both the client billed to and the staff member that completed the task to be billed?
Note: One of my associates has told me that I should set each staff value as a different field, ie Field 1 is SA, Field 2 is SB. My problem with that is that I then need to associate the Rates table with the Staff table, so which field, SA or SB in the Rates table, do I associate with the StaffValue field in the Staff Table, and how do I tell FM to either associate with SA or SB. If this is possible, then how do I accomplish this?