I want to automate the process of calculating the cost of development, based on an entry field (development hours)  so when a developer enters their project hours, it automatically calculates the cost based on our team average.
Any thoughts/assistance would be greatly appreciated.
Any thoughts/assistance would be greatly appreciated.
 300 Points
Posted 1 year ago
QuickBaseCoach App Dev./Training, Champion
 65,310 Points
Can you tell us more about your tables and relationships and also give a verbal description with example numbers of the calculation?
 300 Points
I have my main table (Project), which is the app that contains the general project information (Name, type, creation date, owner, etc). Within this is a relationship to another table, that allows the developers to 'code' their time to the project (Dev_Hours). The sum of the entries are then displayed in the Project table as Total Design Hours.
So next, I would like to take the Dev_Hours sum and, in a new field, output a cost.
For example let's say it costs an average of $65 dollars per hour to develop and there are 12 Dev_Hours input, I would like to create an additional field (next to Total Design Hours) in the Project that displays the cost.
Hope that helps  and this may be a very simple formula to do, but I am struggling with this one...
Thanks in advance for your help and please let me know if you need any additional information.
So next, I would like to take the Dev_Hours sum and, in a new field, output a cost.
For example let's say it costs an average of $65 dollars per hour to develop and there are 12 Dev_Hours input, I would like to create an additional field (next to Total Design Hours) in the Project that displays the cost.
Hope that helps  and this may be a very simple formula to do, but I am struggling with this one...
Thanks in advance for your help and please let me know if you need any additional information.
QuickBaseCoach App Dev./Training, Champion
 65,310 Points
 300 Points
That's just it  it doesn't show a total. I set the field up as a Numeric (formula) and nothing happens. If I switch it over to Duration, it will calculate it out as Days just fine. Is there a specific type of field I need to set it up as, other than Formula  Numeric?
QuickBaseCoach App Dev./Training, Champion
 65,310 Points
OK, so if this is a duration field type for
[Total Design Hours]
Then you will need to convert that to be a numeric field type before multiplying
ToHours([Total Design Hours]) * 65
That is because when you have a duration field, even though it happens to be named "hours", it is a a duration which could also be expressed in seconds, minutes, hours, days, weeks ... so you need to get it converted ot the number of hours if you are going to multiply by an hourly rate.
[Total Design Hours]
Then you will need to convert that to be a numeric field type before multiplying
ToHours([Total Design Hours]) * 65
That is because when you have a duration field, even though it happens to be named "hours", it is a a duration which could also be expressed in seconds, minutes, hours, days, weeks ... so you need to get it converted ot the number of hours if you are going to multiply by an hourly rate.
 300 Points
Apologies for the late followup  travel/schedules bogged me down for a bit. So here's what I wound up with:
If([Designer]= "Name",
[Hours Worked] * 40.9,
I adjust the hoursworked formula based on cost relative to designer. Where I was getting hung up is the closing parenthesis (I needed 14 and only had 1)  once I figured that out, the formula worked. So now, when I select a designer from my pulldown, it automatically calculates based on the hours worked and autopopulates the Cost field.
If([Designer]= "Name",
[Hours Worked] * 40.9,
I adjust the hoursworked formula based on cost relative to designer. Where I was getting hung up is the closing parenthesis (I needed 14 and only had 1)  once I figured that out, the formula worked. So now, when I select a designer from my pulldown, it automatically calculates based on the hours worked and autopopulates the Cost field.
 300 Points
So for anyone that would like to use this (or try it out) here's the steps. To start, create the following three fields:
Designer (or any other name that you need) Type: Text Multiple Choice
Hours Worked (or any other name that you need) Type: Numeric Field
Cost (or any other name you need) Type: Numeric/Currency)
Input the names in your Designer list; in the Cost field properties, locate the formula field and enter the following information:
If([Designer]= "Name",
[Hours Worked] * 40.9,
If([Designer]= "Name",
[Hours Worked] * 38.6,
If([Designer]= "Name",
[Hours Worked] * 34)))
Enter as many formulas as you need. To close out your formula, use the same number of parenthesis as your If statements.
Designer (or any other name that you need) Type: Text Multiple Choice
Hours Worked (or any other name that you need) Type: Numeric Field
Cost (or any other name you need) Type: Numeric/Currency)
Input the names in your Designer list; in the Cost field properties, locate the formula field and enter the following information:
If([Designer]= "Name",
[Hours Worked] * 40.9,
If([Designer]= "Name",
[Hours Worked] * 38.6,
If([Designer]= "Name",
[Hours Worked] * 34)))
Enter as many formulas as you need. To close out your formula, use the same number of parenthesis as your If statements.
QuickBaseCoach App Dev./Training, Champion
 65,310 Points
Ron,
Good to see that you made it work.
Here are two suggestions for the future
You only need 1 IF in QuickBase, so you need to undo your excel background.
If(
[Designer]= "Name", [Hours Worked] * 40.9,
[Designer]= "Name", [Hours Worked] * 38.6,
[Designer]= "Name", [Hours Worked] * 34)
But the right way to do this is not to hard code it.
There should be a table of designers and their rates. Then setup a relationship where 1 designer has many rates and pull that down as a lookup field for the Rate.
Then I would set up a snapshot field on that rate lookup https://help.quickbase.com/userassistance/setting_up_snapshot_fields.html so that you do not change the historical records, when the rates are changed over time.
Good to see that you made it work.
Here are two suggestions for the future
You only need 1 IF in QuickBase, so you need to undo your excel background.
If(
[Designer]= "Name", [Hours Worked] * 40.9,
[Designer]= "Name", [Hours Worked] * 38.6,
[Designer]= "Name", [Hours Worked] * 34)
But the right way to do this is not to hard code it.
There should be a table of designers and their rates. Then setup a relationship where 1 designer has many rates and pull that down as a lookup field for the Rate.
Then I would set up a snapshot field on that rate lookup https://help.quickbase.com/userassistance/setting_up_snapshot_fields.html so that you do not change the historical records, when the rates are changed over time.
 300 Points
Thanks for the additional info (for guys like me, this is the stuff that helps tremendously!). The single If statement does make it so much easier!
The additional ideas on the relationship table is brilliant, as is the snapshot. Going to give those a try so you may be seeing me back here asking questions when it doesn't work :)
The additional ideas on the relationship table is brilliant, as is the snapshot. Going to give those a try so you may be seeing me back here asking questions when it doesn't work :)
QuickBaseCoach App Dev./Training, Champion
 65,310 Points
Correction to my post above.
There should be a table of Designer with their Rates.
Then set up a relationship where 1 Designer has Many Orders (or whatever you call your table that now has the formula)
There should be a table of Designer with their Rates.
Then set up a relationship where 1 Designer has Many Orders (or whatever you call your table that now has the formula)
Related Categories

App builders
 900 Conversations
 45 Followers

Formulas & functions
 2927 Conversations
 75 Followers