Question
Historical Project Status
+9I would like to create a historical Project Status report. Realizing that data is live I was thinking that I could possibly create child records per month that would calculate the status at the time and run reports from that table. For example if a Project was created after 1/2016 and not cancelled or approved that month, the status would be pending for Jan-16. Say it was approved Feb-16 , the status would show as approved each month until complete. I was wondering if there is a way to automatically create these child records each month or if anyone has any other ideas. Thanks!
This topic has been closed for replies.
You might get similar functionality by turning on the "Track Changes" button for the status field. This functionality prepends the status field with the date and user every time the field is changed.
Now this makes the field look a little unsightly in display forms and reports because it shows the history within the field, BUT you can just create a formula field called "Current Status" that grabs the latest status. And use this field to display in the View form and any Reports. This is done with: Trim(Right([Status], "]"))
Now then, to get the similar functionality for your , you can create fields for every status, say you have New, In Progress, and Complete, you could have 3 fields that would be a formula date field to track the date the record was changed to that status.
Now this makes the field look a little unsightly in display forms and reports because it shows the history within the field, BUT you can just create a formula field called "Current Status" that grabs the latest status. And use this field to display in the View form and any Reports. This is done with: Trim(Right([Status], "]"))
Now then, to get the similar functionality for your , you can create fields for every status, say you have New, In Progress, and Complete, you could have 3 fields that would be a formula date field to track the date the record was changed to that status.
+9Thank you for the suggestion. My ultimate goal is to graph/summarize the number of Pending and Open projects from 2016-2017. The example below is for one project using a new "Monthly Status" child table. This sample job was approved in Feb. so shows as pending in jan. and "Open" starting in Feb.. I uploaded child entries with Dates 1/16-1/17. The table includes a calculated field that compares the date to the "Approval" and "Complete" date, calculating the status at that time. This seems to work. I would just like to automate adding the child record portion if possible.
I think you should go up, rather than down.
What I mean is you should make a "Months" table, and have that be the parent to the projects.
Then You'd just need to make the Month Records (Jan 2017, Feb 2017, etc.) and the relationship can be set up to be automatic based on the key dates you have.
No need to make a million child records.
Let me know if you'd like more guidance on setting this up.
What I mean is you should make a "Months" table, and have that be the parent to the projects.
Then You'd just need to make the Month Records (Jan 2017, Feb 2017, etc.) and the relationship can be set up to be automatic based on the key dates you have.
No need to make a million child records.
Let me know if you'd like more guidance on setting this up.
Phase 1: Creating your Months Records
Step 1:
Create a "Months" table.
Step 2:
You will have 3 fields (to start)
- Month ID (Text field)
- Month ID Helper (Formula-Text field)
- First Day of the Month (Date Field)
Step 3:
In [Month ID Helper] field you will need to make it into a unique text string based on the [First Day of the Month] date field.
I sometimes use something like this:
The result for Jan 2017 will be "2017-01", but you can change this option to display any "ID" you'd like.
Step 4:
You will then need to create or import the records for the time frames you are looking to report on. Could be a lot, Jan 2000 to Dec 2025.
Easiest way is to create an excel list of all the 1st days of the month, and then just import that column.
Step 5:
After you have imported the records and have a value in the [Month ID] field, you need to make that field the "key" field for the table.
Phase 2:
Step 1:
to be continued... need to save and return later
Step 1:
Create a "Months" table.
Step 2:
You will have 3 fields (to start)
- Month ID (Text field)
- Month ID Helper (Formula-Text field)
- First Day of the Month (Date Field)
Step 3:
In [Month ID Helper] field you will need to make it into a unique text string based on the [First Day of the Month] date field.
I sometimes use something like this:
ToText(Year([First Day of the Month]))&"-"&Right(ToText(Month([First Day of the Month])+100),2)
The result for Jan 2017 will be "2017-01", but you can change this option to display any "ID" you'd like.
Step 4:
You will then need to create or import the records for the time frames you are looking to report on. Could be a lot, Jan 2000 to Dec 2025.
Easiest way is to create an excel list of all the 1st days of the month, and then just import that column.
Step 5:
After you have imported the records and have a value in the [Month ID] field, you need to make that field the "key" field for the table.
Phase 2:
Step 1:
to be continued... need to save and return later
Continued...
Phase 2: Create Relationship & Create Related Month Helper
(this will assume you have a field called [Project Approval Date], and I will use that, if you have other "Date" fields that you are looking to track, you will need to duplicate this phase for those other dates.
Step 1:
Create the relationship where each month has many projects.
(if you do end up making more relationships you will want to name this relationship appropriately, [Related Project Approval Month], for example)
***
You'll notice that you have a dropdown to manually connect the project to a month (we will make that automatic below)
***
Step 2:
Create a "Helper" field on the Project table. It will be a formula-text field.
Call it something like [Related Approval Month Helper]
Step 3:
Use the same formula you used for the Month Helper, with one modification.
You will need to convert the date to the first day of the month, then use the syntax to match the other "Helper" fields.
Like so:
ToText(Year(FirstDayOfMonth([Project Approval Date])))&"-"&Right(ToText(Month(FirstDayOfMonth([Project Approval Date]))+100),2)
Step 4:
Change the Reference field to a formula-text field.
Change the field type [Related Project Approval Date] field to formula text.
In the formula entry box, simply reference the [Related Approval Month Helper].
***
Now your relationship is automatically connected
***
You can now make any summaries and lookups you need to.
Phase 2: Create Relationship & Create Related Month Helper
(this will assume you have a field called [Project Approval Date], and I will use that, if you have other "Date" fields that you are looking to track, you will need to duplicate this phase for those other dates.
Step 1:
Create the relationship where each month has many projects.
(if you do end up making more relationships you will want to name this relationship appropriately, [Related Project Approval Month], for example)
***
You'll notice that you have a dropdown to manually connect the project to a month (we will make that automatic below)
***
Step 2:
Create a "Helper" field on the Project table. It will be a formula-text field.
Call it something like [Related Approval Month Helper]
Step 3:
Use the same formula you used for the Month Helper, with one modification.
You will need to convert the date to the first day of the month, then use the syntax to match the other "Helper" fields.
Like so:
ToText(Year(FirstDayOfMonth([Project Approval Date])))&"-"&Right(ToText(Month(FirstDayOfMonth([Project Approval Date]))+100),2)
Step 4:
Change the Reference field to a formula-text field.
Change the field type [Related Project Approval Date] field to formula text.
In the formula entry box, simply reference the [Related Approval Month Helper].
***
Now your relationship is automatically connected
***
You can now make any summaries and lookups you need to.
+9Clever! My only issue is that this method will only count the 1 approval month: So for instance if I have a project that was created on 1/16 and approved on 3/16 and completed on 5/16 It should be counted as pending for 2 months and approved for 2 months.
1/16 Pending
2/16 Pending
3/16 Approved
4/16 Approved
5/16 Completed
1/16 Pending
2/16 Pending
3/16 Approved
4/16 Approved
5/16 Completed
I understand that dilemma, and you can do another option, which is basically taking a snap shot of the months table each month.
I've helped people do this with inventory, when they wanted to see the historical status of their inventory. This was even done on a daily basis so they can see trends in inventory fluctuations.
This is similar for you, but you want to know the 'inventory' of projects and the phase they are in.
I've helped people do this with inventory, when they wanted to see the historical status of their inventory. This was even done on a daily basis so they can see trends in inventory fluctuations.
This is similar for you, but you want to know the 'inventory' of projects and the phase they are in.
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.