1 2 Previous Next 29 Replies Latest reply on Aug 4, 2015 8:42 AM by laguna92651

    Grab single record

    laguna92651

      Title

      Grab single record

      Post

      I want to graph three fields, (Int'l Growth, Cash and Fund), from the latest record that is entered for December 31, end of year. I assume I would need to store the data in a global, how would I grab the end of year record. Also do same thing for the every last record, regardless of date.

       

      Last_Record.png

        • 1. Re: Grab single record
          philmodjunk

          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.

          • 2. Re: Grab single record
            laguna92651

            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.

                                                                                                                                                                                                                    
            DateMonthInt'l GrowthCashFundTot Yr
                                                                                                                                                                                                                    
            12/31/20112$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/20123$1,120$620$6,660$8,400
            • 3. Re: Grab single record
              philmodjunk

              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.

              • 4. Re: Grab single record
                laguna92651

                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.

                • 5. Re: Grab single record
                  philmodjunk

                  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" )

                  • 6. Re: Grab single record
                    laguna92651

                    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.

                    • 7. Re: Grab single record
                      philmodjunk

                      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.

                      • 8. Re: Grab single record
                        laguna92651

                        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.

                         

                        • 9. Re: Grab single record
                          philmodjunk

                          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?

                          • 10. Re: Grab single record
                            laguna92651

                            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.

                            • 11. Re: Grab single record
                              philmodjunk

                              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?

                              • 12. Re: Grab single record
                                laguna92651

                                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?

                                • 13. Re: Grab single record
                                  philmodjunk

                                  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.

                                  • 14. Re: Grab single record
                                    laguna92651

                                    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.

                                    1 2 Previous Next