Is there a value in being able to compare projected revenue against actual? That would seem the point of your Coworker's idea and that is as much a business decision as it is a database problem.
And you might not want to use a series of fields all in one record in the first place. This sounds like a case where a table of related records might be the best way to set up your initial projections from the start.
I believe they would like to keep projected and actual revenue separate but somehow be able combine them when they want to. How to keep them separate and yet combine them at will, I am not sure how to conceptualize that in terms of the database.
When we created the table, I also felt unsure if using a series of fields all in one record was a good idea and I believe you may have said the same thing in response to one of my previous posts. But given the time constraints and a general lack of knowledge, we plowed ahead with creating fields for individual months for three years ahead. Presently, the revenue table has 60 fields and counting.
Here are a couple of screenshots: http://imgur.com/1e51FWy,Ucpo8Fx http://imgur.com/1e51FWy,Ucpo8Fx#1
Here’s an example calculation for field "FY14 Apr”:
If ( Date ( 4 ; 15 ; 2014 ) ≥ Project Revenue Start Date and Date ( 4 ; 15 ; 2014 ) ≤ Revenue End Date ; PWNV/12 ; 0 )+ If ( Date ( 4 ; 15 ; 2014 ) ≥ Projects::Act_Feasibility_Started and Date ( 4 ; 15 ; 2014 ) ≤ Feasibility End Date ; Projects::Feasibility_Value/3 ; 0 )
This calculation is repeated in other fields, we simply change the dates accordingly.
If I am to redo the revenue table as a one-to-many relationship where a project is related to multiple records representing monthly income, how am I to replicate this calculation field? While I feel a lot more comfortable with FileMaker than I did a year ago, I still find this issue hard to grasp.
With or without changing to related tables, the explicit dates shown in your example will be an ongoing source of trouble. As time passes what will you do to keep these set up as dates for the future without changing what is calculated in past records? Will you just keep adding more fields?
Consider the following modification to the above calculation:
PWNV/12 + Projects::Feasibility_Value/3
That's it. You use a relationship based on __pkProjectID to match to only records specified for this project. A date field in that record would be used to identify the month that it represents. This assumes that you are using the same calculation to project revenue for each month. If there is some detail to this calculation that changes the result computed with each month, a slightly more sophisticated method would be used.
You are able to limit the applicable months by only creating those records that are applicable to a given project. IN fact, a script can take the project start and end dates and generate one such record for each month starting with the start date and finishing with the end date.
You'd then add a number field to these same records for recording the actual revenue and you now have a way to record the actual revenue to compare against that which was projected for that same month.
I went ahead and created a new related table with projected revenue. I modified the calculation as follows:
If ( Date ≥ Revenue::Project Revenue Start Date and Date ≤ Revenue::Revenue End Date ; Revenue::PWNV/12 ; 0 )
If ( Date ≥ Projects::Act_Feasibility_Started and Date ≤ Revenue::Feasibility End Date ; Projects::Feasibility_Value/3 ; 0 )
I simply substituted the hard coded date with the Date field in the new table. My new data seems to be identical to the stuff in the existing tables, so that's great. My next step would be to automate record creation with a script.
But now I am faced with a different challenge. The financial officer likes to export the data to his excel sheets the way the fields are currently designed: FY 14 Sep, FY 14 Oct, etc.
| ProjName | FY14_Sep | FY14_Oct | FY14_Nov ... | ProjA | $x | $y | $z | ProjB | $x | $y | $z
My new related table is now like this:
| ProjName | Date | $$$ | | ProjA | FY14Sep | $x | | ProjA | FY14Oct | $y | | ProjA | FY14Nov | $z | | ProjB | FY14Sep | $x | | ProjB | FY14Oct | $y | | ProjB | FY14Nov | $z |
It looks great in a portal but leaves me with an unhappy financial officer who wants to see and chart the data the old way when he exports to excel. Perhaps this is now a question for an excel forum? Thanks for all the advice thus far.
Sorry, but I don't see the need for the calculation fields. I can't imagine revenue records related to a given project that do not fall within the start and end dates specified for that project.
Have you considered using FileMaker to produce your FO's chart?
It's still possible to set up your data for export to excel with the columns he wants, but if you can chart the data in FileMaker, life get's simpler all around.
To set up fields for export, you can set up a series of calculation fields for your project records that extract this data from the related data for exporting purposes. GetNthRecord, for example, can be used to pull up the nth related record from a set of related records.