I think you need to describe what you are trying to do with that "grabbed" record in more detail. Not sure why, from what you have posted, you would store the values in a global (field? Variable?) as that might not be necessary.
A script can find all records dated for a given month. A Sort can then make that last record of the month the last or the first record in that found set.
A sorted relationship can also make such a record the first related record. That then makes it possible to access the record directly via the relationship
ExecuteSQL could also be used to return either the values or the Primary Key of the desired record.
There are two situations:
1. I want to place a graph of three fields, (Int'l Growth, Cash, Fund) on a bar chart for the most recent December record (end of year), in this data set 2011, on a dashboard. There are actually additional fields not shown and other graphs I would generate from this record.
Date Month Int'l Growth Cash Fund Tot Yr 12/31/2011 2 $1,110 $610 $6,110 $7,830
2. I would also want to place a second graph of three fields, (Int'l Growth, Cash, Fund) on a bar chart for the most recent record, in this data set 3/11/2012, on a dashboard.
3/31/2012 3 $1,120 $620 $6,660 $8,400
1. To confirm, you want to chart end of the year totals for a series of years? Or are you trying to compare these subtotals to each other?
Either way, As I stated previously, both a sorted relationship and executeSQL can access this data. And this data can then be used for charting purposes. But your Tot Yr field may require linking to all records for that year if this is a summary field--it depends on how you've set this up in this table as this could be a number field, a summary field or a running total summary field.
I am just comparing the subtotals to each other for the year of interest.
Can you be a little more specific when you say, "That then makes it possible to access the record directly via the relationship"
The Tot Yr field is just a calculation field of the three subtotal fields.
Haven't used the executeSQL, seems like it might be the easiest once I understand it, will have to go through that concept.
So if your Parent table has a field named "Year" and you put a year into it such as 2014. And if you define a calculation field, cYear, in your data table as: Year ( DateField ). you can set up this relationship:
ChartTable::Year = DataTable::cYear
You can double click your relationship line and specify a sort order that sorts by Date Field in Descending order.
THen, in chart setup, you can use the delimited data option and your Y-series can be:
List ( DataTable::Growth ; DataTable::Cash ; DataTable::Fund ; DataTable::Total )
Your X-Series might be: List ( "Growth" ; "Cash" ; "Fund" ; "Total" )
I get it, except for the ChartTable, is this the Parent table you're referring to, where did that come from and what is in it.
Right now I have one table, what your calling DataTable that contains all of the monthly results.
You need another table occurrence (see: Tutorial: What are Table Occurrences? ) in order to set up a relationship to your datatable. Your chart layout would also be based on this table. This can be the same table as your data table if you set up a table occurrence or you can set up a new table to use in this relationship. As long you have that Year field, to use in that relationship, you have what you need.
Having a problem somewhere. Is the "Year" field a global field in the ChartTable? In your explanation is the "Year" field on the dashboard to enter a year into? This is what I have.
It can be global or local. Either can work.
Is your chart layout based on Results Graph?
Is year a number field?
Is cYear a calculation field with a number result type?
Based on what I think is equivalvent to your data table, ResultsEntry. Tried Results Graph TO, no difference.
List(ResultsEntry::Tax Int'l Growth;ResultsEntry::Tax Money Market;ResultsEntry::Tax Tot Stk Mkt)
"Year" and "cYear" are both numbers.
"Year" and "cYear" are both in the same table, correct, Results.
The chart should be on a layout based on ResultsGraph, not ResultsEntry.
Did you use the delimited data data source option in chart set up?
Did you specify a sort order for ResultsEntry in the relationship from ResultsGraph to ResultsEntry?
I redid the occurrences still no luck, to ChartTable and DataTable.
The chart is Current Record (delimited data), chart is based on ChartTable
Sort on DataTable is descending. I were to create a layout based on the ChartTable TO, what should I see?
What I am describing requires that the layout with the chart object be based on ChartTable TO. A portal to Data Table should list all records with a date from the specified year, but the first portal row should show the data you want to chart. I suggest temporarily setting up that portal to check and see if the relationship is working correctly.
If I place a DataTable portal on a layout based on ChartTable TO I get the years by selected year only, but still no chart.
If I remove the dashboard link I no get no records on the DataTable portal, why would that be? There is nothing in that dashboard table that is being referenced.