10 Replies Latest reply on Mar 3, 2011 4:39 PM by philmodjunk

    Anything more sophisticated to generate graphs by month based on dates in FM 11?

    PecCars

      Title

      Anything more sophisticated to generate graphs by month based on dates in FM 11?

      Post

      Currently migrating an app from MS Access 2003 to FM Pro 11. I have several graphs that are generated based on date fields, as in Inquiry::InquiryDate or Inquiry::BookingDate. Basically I need to generate counts of these fields and put in a graph. The reports need to show monthly data although the dates in those fields will obviously be exact dates including the day.

      While browsing through the forum I found a suggestion that goes as follows: create a calculated field for each record so that it calculates the first day of the month.

      Easy, yes but ... in the case of my apps it means generating another 20 fields in the table considering the several date fields that it includes in the first place and considering that I will need to do something similar for the yearly comparisons.

      In MS Access I can create on-the-fly fields in the report as opposed to adding clutter to the table with calculated fields.

      Any suggestions if FM Pro 11 offers something similar ?

        • 1. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
          philmodjunk

           in the case of my apps it means generating another 20 fields in the table considering the several date fields that it includes in the first place and considering that I will need to do something similar for the yearly comparisons.

          Can you expand on that a bit? I'm afraid I can't see from here why you would have "several date fields" instead of just one such date field per record?

          Yes, when you switch from Access to FileMaker the need to define so many calculation fields directly in the table seems strange and inefficient. FileMaker doesn't give you many alternatives, however, though sometimes you can set up a global variable that is loaded via a Let function or you can define a calculation inside a portal's filter expression or a chart table's data series setup... and avoid the extra field in a few cases.

          On the other hand, there are, at times advantages to defining such fields at the table level. When working with Access, I've had to spend quite a bit of time, on several occaisons, debugging the calculations defined as the data source for different text boxes or in different SQL expressions to figure out why the value computed in one form/layout did not match the value computed on another. Had I been able to define a single calculation at the data level and use it in all such places, keeping things consistent and applying updates quickly would have been much easier. I'm not trying to convince you that FileMaker is better than Access in this area, just pointing out that there can be plusses to go along with the minuses here.

          • 2. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
            PecCars

            Thanks Phil !

            Several date fields because of Inquiry Date, Booking Date, Arrival Date, Departure Date and a couple more. Each of them has a "business" implication, which is why a separate graph per field is used. Creating this calculated fields is just more manual work. I will test how far do I get with calculations in the chart setup.

            In the meantime I have been shopping around and I have found some plug-ins that seem to do the trick. I will be testing in the next few days and hope to find some that does not require heavy scripting, although it seems that I won't get around creating calculated fields.

            • 3. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
              philmodjunk

              Feel free to report back on your experiences with plug ins. That may help others dealing with the same issues.

              Shouldn't be that much extra work by the way, as you can copy and paste one expression, double click the one field that's different and select the new date field to update the expression. I sometimes use the Let function as a way to refer to such a field only once in the calculation so that there's only one field to change for a parallel calculation.

              • 4. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
                PecCars

                I think I am getting the hang on counting and totaling in FM now. There is a lot of opportunities to massage data in Filemaker, albeit I would call it non completely straight SQLish. A tip for anyone: visit directly the tutorials on filemaker in youtube. There are several tutorials (including the ones from filemaker) that help you put the puzzle together and get started.

                As for the FM developers:

                It is just confusing for a user to create calculated fields such as Total Of Sales at the record level, as the value of the Total field will change based on the context of reports in the first place, depending if it is placed in the Body or a Sub Summary part. I strongly believe that it would be a lot cleaner to have a total field type that can be created directly in a layout / report instead of having to define it at the database level. Would be certainly less confusing for the user.

                On charting with external solutions:

                After evaluating FusionCharts for Filemaker I have come to the conclusion that it is simply not worth the effort in my case:

                - Pros: fantastic charts including funnels, data deviation and others including animation

                - Cons: CODING !, as in FusionCharts parameter coding (this is not really an FM issue but rather a FusionCharts one, actually if you want to embed them in your filemaker app you need to do some heavy work generated extended parameters.

                Coming from the MS Access world I got used to not code anything for my app. With its many wizards you can get a lot done without writing a single line of code or a script or anything similar.

                Notes for FM developers on charts:

                - Wizards please

                - Data series labels

                - Automatic folding of data by years or months where that is a break criteria.

                - Other types of charts: funnels, hi-lo, radar

                - A bit of animation on the charts

                • 5. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
                  philmodjunk

                  It is just confusing for a user to create calculated fields such as Total Of Sales at the record level, as the value of the Total field will change based on the context of reports in the first place, depending if it is placed in the Body or a Sub Summary part. I strongly believe that it would be a lot cleaner to have a total field type that can be created directly in a layout / report instead of having to define it at the database level. Would be certainly less confusing for the user.

                  Having developed solutions in Access and VB, I get where you're coming from here, but wouldn't this issue be invisible to the end user? Whether the calculation takes place at the presentation level like you can in a form or RecordSource query in Access or at the Table level like you usually have to do in FileMaker, the end result is that the same calcualation is performed and the same value is presented to the user. I don't see how that can possibly confuse the user.

                  It can confuse a developer when first getting used to the difference in approaches. Ironically, I often missed being able to define a calculation at the table level in access when I found I had to copy and paste the same exact expression into more than one location (more than one SQL query, more than one text box...) and realized that any future updates to this expression would require me to track down all such instances and to then make identical updates to each...

                  Frankly, I'd prefer to be able to choose either method, depending on the needs of a particular project.

                  • 6. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
                    PecCars

                    Right you are ... and so I am as well. I am thinking along the lines of I am the user and the developer. Choosing the method is fine, but  wizards can help save time and also appeal to non coders to use FM. For my MS Access app I did not have one single line of code. Just out of curiosity I just took a look at my MS Access app, and here my stats:

                    -  MS Access: 11 tables and 11 relationships ( my FM has now 11 tables + 8 self relationships brings it to 19 views/tables together, makes also 20 relationships so far )

                    - MS Access charts: stacked up bar charts, switch on / off for stacked data (several ys allows me to show several layers of data in one single chart, not to mention such cosmetics as label names, show data for each data point in a graph.and does not have meaning not really interested in coding because my driver is how the app supports my business.

                    - Scripts: MS Access: None necessary; FM: 3 so far (mainly "only" for finding and sorting records)

                    - Calculation fields: MS Access: none necessary as the charts take care of the calculations, FM: I didn't bother to count all summary and calculation fields in my app, but we are talking about something in the range of 20 to 30.

                    ...

                    all speaks for MS Access except that it doesn't run on Mac OS, which is why I will stick to FM anyway now.

                    I do think FM has a great potential. It just needs to get better in a few areas and indeed my view may be limited to the requirements that I put for my use.

                    • 7. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
                      PecCars

                      BTW, by making notes to FM developers I meant the developers of Filemaker, and not FM developers that create FM applications. !

                      • 8. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
                        philmodjunk

                        You might be interested in using this link to submit requests for new features:  http://www.filemaker.com/company/feature_request.html

                        • 9. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
                          PecCars

                          Done ! Thanks for the info. Let's see what the response is.

                          • 10. Re: Anything more sophisticated to generate graphs by month based on dates in FM 11?
                            philmodjunk

                            Don't hold your breath. Out of all my suggestions, I've gotten exactly one response back and that's one more response than anyone else I've communicated with reports. They get a lot of suggestions. You can also post suggestions to the FeedBack section. I usually post suggestions in both locations figuring the feedback section makes it public where others can respond--often producing even better ideas to suggest and also to drum up "me too" posts by others to the suggestion form.

                            The suggestion form, on the other hand, is the official repository for customer suggestions which, I'm told, is looked at by FileMaker Personnel some of whom may not see a suggestion made to the feedback section.