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 the following:
It lists the days between submission and deadline dates of new projects and retrieves value in a field [capacity] corresponding to those days from a child table and sums them.
For instance, if I add a record with the following data:
Submission date: Feb 23rd
Deadline: Feb 26th
it returns:
sum(Feb 23 [capacity] + Feb 24[capacity] + Feb 25[capacity] + Feb 26 [capacity])
If you calculate it manually according to data in screenshot it should return 36.
Thank you
Question
List days capacity between two dates
This topic has been closed for replies.
+15Karim,
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
------------------------------
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.