List days capacity between two dates
I'm trying to calculate the workload of my department while adding new projects. So I have a table for Projects submitted and a table of employees' availability dates. I need a formula that does th...
Forum Discussion
Karim,
To retrieve the values of [Capacity] you will need to establish a relationship between your Project Record and the records for the inclusive dates of the project.
You did not show the Record ID#s but lets assume you data looks like this and your new project is RID =2
RID, Date, Day Capacity, Related Project
54, 23 Feb, 9, 2
55, 24 Feb, 9, 2
56, 25 Feb, 9, 2
57, 26 Feb, 9, 2
You now can easily write a Summary field between Projects and Capacity that will total [Day Capacity]
The obvious problem is that you have to relate the days in Capacity to Projects for this to work. @Mark Shnier (YQC) is an expert on changing Key Fields so that you can use Dates to pull together data. I suspect this one will challenge even him because you have a dynamic number of dates that have to be related to the Capacity table. He needs to weigh in here for a potential native solution.
However, if I am correct and this is not going to work natively, you are going to need to write some scripts to do this one for you. The math for this sort of look up is not complicated. I a few paragraphs of PHP by someone that knows the QB API's will give you the answer. There is a real advantage to a scripted solution which is that you will get a static value that you can store when the Project is created or evaluated. A pure Quick Base solution where things are dynamically calculated will change as the Days Capacity change.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
To retrieve the values of [Capacity] you will need to establish a relationship between your Project Record and the records for the inclusive dates of the project.
You did not show the Record ID#s but lets assume you data looks like this and your new project is RID =2
RID, Date, Day Capacity, Related Project
54, 23 Feb, 9, 2
55, 24 Feb, 9, 2
56, 25 Feb, 9, 2
57, 26 Feb, 9, 2
You now can easily write a Summary field between Projects and Capacity that will total [Day Capacity]
The obvious problem is that you have to relate the days in Capacity to Projects for this to work. @Mark Shnier (YQC) is an expert on changing Key Fields so that you can use Dates to pull together data. I suspect this one will challenge even him because you have a dynamic number of dates that have to be related to the Capacity table. He needs to weigh in here for a potential native solution.
However, if I am correct and this is not going to work natively, you are going to need to write some scripts to do this one for you. The math for this sort of look up is not complicated. I a few paragraphs of PHP by someone that knows the QB API's will give you the answer. There is a real advantage to a scripted solution which is that you will get a static value that you can store when the Project is created or evaluated. A pure Quick Base solution where things are dynamically calculated will change as the Days Capacity change.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
MarkShnier__You
Qrew Legend
Qrew LegendKarim, as a followup to Don's post, what are you hoping to happen with the available capacity after the project is entered? Are you expecting it to decrease?
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
- I think I should have another column with the decreased capacity.
I think I didn't describe the problem properly, I'll try to describe it in a better way.
Let's say we manufacture chairs, we receive projects daily, each project has a set number of chairs.
Each employee can make 3 chairs per day, everyday they're at the office.
When I receive a project request to make 25 chairs from Feb 23 to Feb 26, I want my total capacity for those days calculated and the number of chairs required deducted sequentially from the daily capacity and stored in the sheet.
In the screenshot I wrote the numbers I expect the formula to provide.
- 23rd we'll do 9 chairs, so ending capacity is 0
- 24th we'll do 9 chairs, ending capacity is 0
- 25th we'll do the remaining 7 chairs, ending capacity is 2
- 26th will remain unchanged since we finished the project the day earlier.
The following assumptions stand:
- Projects are First come first serve basis - hence sequential deductions
- "Chairs" are identical to each other, and all employees have the same capacity to make 3 chairs every single day at the office. Projects differ in the amount of chairs only.
I appreciate your help since I've been stuck here for some time.
------------------------------
Karim Gawish
------------------------------
