Creating an estimate/quote tool with

[Max_Chopovsky]

I am building a tool to help us estimate projects and need help around the lookup functionality.

Logic
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.

Database
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.

Desired outcome
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.

For example:
Say we have a project that requires Ideation, Production, and Editing. I want to:

1. add a new record
2. select Ideation (ideally a dropdown but I’ll make do with link to record)
3. select Bob from the Staff table (ideally a dropdown but I’ll make do with link to record)
4. 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)
5. 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.

Questions
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!

[Jeremy_Oglesby]

There are several ways you could probably achieve this, but this is how I would approach it…

Have only 1 record per staff. Bob has only 1 record(row) in the Staff table, but there is a field(column) for each of his rates - hourly / daily / etc.

In the Tasks table, you will need the Staff selection to be a Link to a record from the Staff table.
You’ll need a Lookup field(column) for each of the “rates” defined on a staff member – hourly / daily / etc. – that looks up the rate of each for the Staff member selected. These Lookup fields can be hidden in your working view, if desired.

Also in the Tasks table, your “Task Type” (Ideation, Production, Editing, etc.) seems like it could safely be a Single Select(dropdown), as can your “Rate Type” (hourly, daily, etc.) field.

Now you just need a formula field with a conditional to create your rate.

SWITCH(
   {Rate Type},
   "Hourly", {Staff Hourly Rate Lookup},
   "Daily", {Staff Daily Rate Lookup}
)

In this way, the Staff member you choose will determine which Rates get looked up, and then the Rate Type you choose will determine which one of those looked up rates shows up in this formula field.

[Max_Chopovsky]

Jeremy thank you so much! That worked absolutely brilliantly. That switch formula is EXACTLY what I was looking for. You are a lifesaver.

How would we deal with rates that are TBD? In other words, we sometimes hire freelancers and the rate depends on the person. We could either:

1. Add a number of records to the Staff table, each for a typical rate, let’s say cameraperson $500, editor $700, etc. The issue here is that if someone has a different rate than what is typical, we either have to add a new record (which will eventually get cumbersome) or we have to make manual adjustments somewhere.
2. Have a separate table for freelancers, where we could list our typical freelancers and their rates, but that only move the problem to another table.

The ideal solution for me would be to somehow be able to enter manual values for rates as we are putting together estimates. Could this be something like a formula that looks at a field (e.g., freelancer, TRUE/FALSE), that, if false, pulls the SWITCH formula you outlined, and if true, pulls from another field, Freelancer Rate? Then the Freelancer Rate field would be blank for all Staff members but that way I suppose we would still be able to do rollups on the Rate field. In that case though, how would I handle the primary field, which is currently a formula that concatenates the task and Staff name. Would that be an if statement as well?

Thanks again - you really saved me a ton of time and energy. I really appreciate it!