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
------------------------------
-------------
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.
--------------
Having it dynamically calculated and updated is not gonna be a problem at all, I can find a workaround to store the "state" of the project at the time of submission.
(Checkbox to be ticked while adding a new project confirming our knowledge that we might not meet due date for example)
So a pure Quick base solution can still be an option.
Thank you for your help.
------------------------------
Karim Gawish
------------------------------
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.
--------------
Having it dynamically calculated and updated is not gonna be a problem at all, I can find a workaround to store the "state" of the project at the time of submission.
(Checkbox to be ticked while adding a new project confirming our knowledge that we might not meet due date for example)
So a pure Quick base solution can still be an option.
Thank you for your help.
------------------------------
Karim Gawish
------------------------------
- MarkShnier__You
Qrew Legend
My native solution is to create a middle table called Project Day Assignments
1 Project has many Project Day Assignments
1 Capacity has many project day Assignments.
That will allow you to summarize up to capacity to see where you have unused or overused capacity.
I would first get this working manually, and then we can discuss ways to make the process of adding the assignment records more easily. A stating point is that for the drop down record to choose a capacity day to assign a qty to, to filer the drop down report for only where there is some capacity remaining.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
