I am building a tool to help us estimate projects and need help around the lookup functionality.
We have various team members, each of whom has multiple rates (hourly, daily, etc). Each project consists of multiple tasks. Each task can have multiple people working on it, e.g., an hour on ideation from Joe, an hour on ideation from Bob, etc.
I have a table called Staff and a table called Tasks. The Tasks table just has the tasks which comprise any project. The Staff table has each employee’s name, rate, labor cost, etc. There are multiple records for each person: Name + daily rate, Name + hourly rate, etc.
For the estimate, I have a junction table, Estimate, wherein I select a task from the Tasks table (link to record) and a Person from our Staff table (link to record). However, currently, the Person I’m selecting is really the Name + Rate combination (e.g., Bob hourly rate), which then populates the Rate field via lookup.
In estimating a project, I want to be able to create a line item for each task, select a person from the Staff table, select that person’s rate (with the choices being only the available rates for that person), and have a rate field populate automatically given the person and rate combination.
Say we have a project that requires Ideation, Production, and Editing. I want to:
- add a new record
- select Ideation (ideally a dropdown but I’ll make do with link to record)
- select Bob from the Staff table (ideally a dropdown but I’ll make do with link to record)
- select hourly or daily (with the only two choices being Bob’s hourly or daily rate - again, ideally this is a dropdown but I’ll make do with link to record)
- have the rate populate automatically based on the selections from steps 3 and 4
Basically, I’d like for the data in the Person and Rate type fields to act as input variables for the query which is Rate.
How do I accomplish this?
Should I have a separate Rate types table (hourly, daily, etc) and then join that table with the Staff table or can I have multiple Bob and Joe records in the Staff table, e.g., Bob daily, Bob hourly, Joe daily, Joe hourly, etc. If so, what is the way to go about it?
Thanks in advance!