Sorry that table distorted! It is supposed to run from left to right like in filemaker table view!
I think you should change two things to get the solution you are seeking.
1) Change your data structure. You do not need to have two separate tables for 2011 usage and 2012 usage. All usage should be in the same table with a field for both the month and the year of the usage, or a date field that can be broken down into the month & year.
2) I'd switch to Excel for the final report itself. You could export data for both 2011 & 2012 usage to a 'Raw_Data" worksheet, create a second worksheet for a Pivot Table that can re-organize and group your data appropriately, then set up a third worksheet with a series of VLOOKUP() functions to that can neatly display the data you are looking for.
My guess is that the people who are going to receive the report are expecting Excel anyway. For some data analysis, Excel is the better/simpler tool.
In FM12, there would be some argument for using the ExecuteSQL() function to grab the data you are looking for, but I know Excel better. I'm also guessing that you don't need to see, on a dynamic record-by-record basis, what the comparison of a particular farm's usage for this month to the same month last year is. That would require moment-to-moment access to the data for immediate updates, and that doesn't sound like your current need.
-- Drew Tenenholz
1. I agree with Drew, change your structure. The same type of data with only a year value differing them should be in the same table, with a "year" field. Much easier to push and pull for reporting.
2. I just wrote a blog about cross-tab reports with related summary fields. See it it helps:
"Aggregates in Filtered Portals"
- there are also links to related blogs about cross-tab reports at the end of the article, for more ideas, especially for exporting this to Excel.
OK, I'll trust that you have good reason to keep Table 2 separate from Table 3, if for no other reason than Table 3 is month-and-year-wise and Table 2 is only year-wise data. In any case, assuming you need to stick with 3 tables, then if we can assume that there is a unique record for each "OEM Part No." in Table 1, then you could create the appropriate relationship between Table 1 (Part No.) and the two Usage tables. This assume that there is a unique record in Table 2 for each OEM Part No. & Year combination, and also a unique record in Table 3 for each OEM Part No. & Year & Month combination. The idea is to create a 1-to-1 relationship so that you do not end up with ambiguous related data, as follows:
To link Table 1 to Table 3, use "OEM Part No." and "Month" as the match fields in a complex relationship, e.g., called "Current Yr Usage" (assuming Table 3 ONLY contains data for the Current Year and also assuming your report is only for a single month, otherwise if it contains data from more than one year, then simply add the Year as a third field in your complex relationship; it's possible to make the report cover multiple months, it's just another dimension that needs to be added to the report). You would either manually select the month (and year, if necessary) from a value list in Table 1, or you could simply calculate it via a script that generates the report.
To link Table 1 to Table 2, just use "OEM Part No." as the match field in a simple relationship, e.g., called "Prev Yr Usage" (assuming Table 2 only has annual, not monthly, data from the previous year for each part number).
Once you uniquely identify each related record, you then can represent your Usage data within the approrpiate single-row that matches the relationship for each part number and year (for Table 2) and also month (for Table 3). And just as you can specify which related data displays in each Usage field from both Tables, you also can perform math between those portals in order to calculate the resulting Variance as a function of the OEM Part No. (you might need to widen the window to get all the following to spread out properly):
OEM Pt • Manuf pt • Descript • Current Yr • Current Mo • Qty Usg • Year • Qty Usg Prev Yr • Avg Mo'y usg Prev Yr • Variance
1234 abs1002 generator Tab1::yr Tab1::mo Tab1::usg Tab2::yr Tab2::prevYr Tab2::usg (Tab1::usg)-(Tab2::prevYr)
As the other posters here have suggested, you can combine your usage data into a single table, but that still would require you to create two separate relationships, one with the month and current year and one with just the previous year excluding the month. These parameters can be selected in Table 1 via value lists which set fields that are incorporated into the relationship.
Would that work for you?
Phil (@ micinfo.com)
Long term, it would be wise to bring all that data into a single table. But in the short term, Phil's suggestion should get you the report you need.
In addition to the descriptions above, you could also use the virtual list technique to gather all the values into a global variable. Then you can display it as you see fit. It might be a little bit of an expansion of your current skill set, but in the long run can help you make more flexible reports.
- The idea, basically go through the process ( Looping script or such ), and gather the values for each OEM Part.
- Calculating the needed values as you go, each separated by a tab or some other delimiter.
- Then in a VirtualList table, each field is a calculation that equals the respective segment of the delimited row. Each record will pull the next value ( FM Value = row in a list ) from the global variable.
That may be more long term of an approach than getting this report to the necessary parties, but adding it to your toolbelt will serve you well as you continue work in FM.
There's a misleading label in my post. What I refer to as the relationship "Tab1::" should be labeled "Tab3::" to more clearly identify that it's referring to a relationship bewteen Table 1 and Table 3. Therefore, the red fields in my example should be "Tab3::yr" and "Tab3::mo". LIkewise, the final field to the right should read "(Tab3::usg)-(Tab2::usg)".
To make it even more clear (and more consistent with my suggestions for constructing the relationships), what I just said should be the red label of "Tab3::" in the colored example refers to the relationship I describe in the text description as "Current Yr Usg::" while my blue label "Tab2::" in the color example refers to the relaionship "Prev Yr Usg::" in the description.
Sorry for any confusion. I was in a hurry to reply to your urgent request!