8 Replies Latest reply on Oct 30, 2013 4:55 PM by DerekPopovich

    Charts in Filemaker Pro 12 - only the last 30 entries

    DerekPopovich

      Title

      Charts in Filemaker Pro 12 - only the last 30 entries

      Post

           Hello, 

           I have a list of records with 3 years worth of data. (have a date field and value field)

           The records are entered on the weekly basis, I have created a chart but it is charting 3 years.

           Does anyone knows how to make the chart to graph only the last 30 days?

           Sorry, I have been going around in circles and I can not find any samples for this, I have use some examples

           hat I have seen on the net but my chart does not show up properly.

           Sincerely, DPP

        • 1. Re: Charts in Filemaker Pro 12 - only the last 30 entries
          philmodjunk

               If you are basing your chart on the found set, you would need to limit your found set to only data from the last 30 days. You can perform a find for all records in that date range before viewing the chart and this can be scripted.

               There are also ways to limit delimitted data to the last 30 values by using the RightValues function if delimitted values are being used as the data source.

          • 2. Re: Charts in Filemaker Pro 12 - only the last 30 entries
            DerekPopovich

                 Hi, 

                 Thank you for the answer, I don't have much experience with Filemaker Pro. 

                 Can you post a link where I can see samples of the query/code to do this.

                 Thank you, DPP

            • 3. Re: Charts in Filemaker Pro 12 - only the last 30 entries
              philmodjunk

                   I first need to know which option you are using for your y-series: Delimited data or a found set?

              • 4. Re: Charts in Filemaker Pro 12 - only the last 30 entries
                DerekPopovich

                     Hi, 

                     I try using the "specified calculation" to reduce the number of records to only the last 30 entries but nothing i do makes any difference, at this point I want to learn how to do the above two. Is Using the Specified Calculation considered Delimited Data. Sorry I am very very new to this.

                     I have my main table called stats and a portal tool for my table statsvalue showing all the linked records to that main table, on the side I have my line chart.

                     The X (horizontal) is my sqldate field and the vertical are the values field, as you can see on the screen shoot I uploaded that I get all the records showing on the chart. Thank you for your help

                • 5. Re: Charts in Filemaker Pro 12 - only the last 30 entries
                  philmodjunk

                       The simplest option is to modify the portal's relationship to only match to the records you want to see in the chart. This might require adding a new table occurrence to your relationship graph if you need the existing relationship for other purposes.

                       Let's say that your existing relationship is:

                       ChartTable::MatchField = MainTable::MatchField

                       If you modify the relationship to be:

                       ChartTable::MatchField = MainTable::MatchField AND
                       ChartTable::DateStart < MainTable::Date AND
                       ChartTable::DateEnd > MainTable::Date

                       You can set up DateStart and DateEnd on your layout and control what data appears in the chart by selecting different start and end dates in those fields.

                  • 6. Re: Charts in Filemaker Pro 12 - only the last 30 entries
                    DerekPopovich

                         Hello and Thank you for the answer - I will work on as a separate database so I keep this a bit more organise.

                         Since my last post I was able to work out a solution, Here is what I have done, Please let me know if this is completely wrong (It seems to work).

                         I have created a new Layout.

                         I have switch the tables, The "main Table" is the one being open in the portal Tool, so all my search result from "StatsValue" are now part of Current Found Set (Before this was not possible as my main table seem to be the one determining the Current Found Set)

                         I have created a layout script Called "StatsTest"as Follows:

                         1. Enter Find Mode [Restore] (StatsValue::sqldate:[>= 01/07/2013]

                         2. Go to Layout ["StatsTest"(StatsValue)]

                         3. Sort Records [Restore, No Dialog] ) -->sort ascending.

                         On the Layout - I have set up a script Trigger for:

                         1.OnLayoutEnter - Script "StatsTest"

                         2.OnModeEnter - Script "StatsTest" -> This keeps my Layout constantly on search (only search and display the chart.)

                         -----I ONLY HAVE ONE PROBLEM-----

                         My chart shows the wrong order, I can not seem to be able to make the chart follow the Ascending order, with the oldest date on the left and the newest on the right as per the script.

                         I also enter the Order as ascending on the table relationship but does not change, all my searches show as descending. (Any Suggestion? or this entire approach to solve the problem is completely wrong?.

                         Sincerely, DPP

                          

                    • 7. Re: Charts in Filemaker Pro 12 - only the last 30 entries
                      philmodjunk

                           Specifying a sort on the relationship will not affect the sort order of your found set.

                           If you use table view to examine the records in your found set do you see them sorted in ascending order by the date field?

                           What I see in the chart suggests that your found set is actually sorted in Descending order.

                           And you may find these scripted find examples helpful as a way to avoid needing to redefine your script when you need to see data from a different date range on your chart: Scripted Find Examples

                      • 8. Re: Charts in Filemaker Pro 12 - only the last 30 entries
                        DerekPopovich

                             Thank you for the link.

                             Your Help is very much appreciated.

                             I adjusted my Script Triggers in the Layout and created a new script called "Sort" (Sorts my date ascending) and added to the

                             layout script trigger as OnModeExit - Script "Sort" and it is sorting my chart in the right order. 

                             Update: On the Layout - I have set up a script Trigger for:

                             1.OnLayoutEnter - Script "StatsTest"

                             2.OnModeEnter - Script "StatsTest" -> This keeps my Layout constantly on search (only search and display the chart.)

                             3.OnModeExit - Script "Sort" -> Sorts my charts in the right order.

                              

                             For those reading this post, I have very little to now experience with FileMaker Pro 12, and this project is for personal use. I do not know if having the above workout causes problems somewhere else on the project. Be aware of this data, if you plan to use the same setup that created.

                             Sincerely, DPP