7 Replies Latest reply on Jul 29, 2011 1:26 PM by deathrobot

    Trouble with charts

    deathrobot

      Title

      Trouble with charts

      Post

      I recently updated from FileMaker 7 to 11. One of the databases I upgraded is an invoice database. In it, there are seven fields which are for different types of charges. In the old version, I was able to make a graph that showed, visually, the percentage of each charge type for the found set of records, so that I would get a bar graph that showed:

      Type 1: 20% of total

      Type 2: 15% of total

      Type 3: 60% of total

      etc.

      Since they look so much nicer, I would like to use the new charting features of FM 11. I've gone through all the help files but cannot seem to get my head around how to do it. Am I missing something simple?

      Thanks,

      Michael

      Chart.jpg

        • 1. Re: Trouble with charts
          philmodjunk

          What kind of field are you using to compute the percentages? A "fraction of total" summary field?

          If so, then put your chart in the layout body, Specify your "type" field for the vertical (x) axis. Specify your summary field for the horizontal (y) axis and then be sure to do 3 more things:

          1. Select "Use data from current found set"
          2. Select "Show data points for groups of records when sorted"
          3. Then be sure to sort your records by the "type" field to group your records by type.

          .

          If you think about it, there's a lot of similarities between this type of bar chart and a summary report with sub totals in sub summary parts. Each "bar" is much like a sub summary part and has many of the same requirements--such as a summary field and the correct sort order.

          • 2. Re: Trouble with charts
            deathrobot

            Hi and thanks for the reply, PhilModJunk! I think my confusion is coming from the fact that there is not one "type" field; I have seven unique "type" fields each of which is a dollar amount. To get the graph in FM 7, I made seven summary fields, each of which was a total of one of the seven "type" fields for the found set of records, and then seven calculation fields that multiplied each of the summary amounts by 100 and divided by the total billings for the found set of records. Since I don't have one field I can sort by, I'm stumped.

            Michael

            • 3. Re: Trouble with charts
              philmodjunk

              You may want to restructure your database and for more reasons than to get this chart working.

              I'll need to no more about how you've structured your data and why before I can go further with this thread.

              • 4. Re: Trouble with charts
                deathrobot

                It is an old database (originally FileMaker 4) with many records. Most importantly, it's working fine! I'm sure some things could be reorganized more elegantly, but I hesitate to muck anything up. Here's how that portion is currently structured:

                1) On each invoice there are fields for describing the type work done, along with corresponding amounts (e.g.…

                | Type of work 1 | | Amount 1 |

                | Type of work 2 |  | Amount 2 |

                | Type of work 3 |  | Amount 3 |

                etc.

                The "Type of work" fields have drop down menus of nine types of work to choose from. What's tricky is that we need a bar graph that shows the dollar percentage of seven different combinations of those nine work types (e.g. one bar might be for "Work Type A + Work Type B as long as there is no Work Type C"). In order to accomplish this, I have seven calculation fields that figure out the dollar totals for each of those combinations using "if/than" statements. Then I have the fields I mention in my second post; seven summary fields which total the calculation fields for the found set of records, and seven new calculation fields that multiply each of the summary amounts by 100 and divide by the total billings for the found set of records.

                I know that is long winded, and thanks for even considering figuring this out. Is this something I can graph with the charting feature, or should I just stick with what I have?

                Michael

                • 5. Re: Trouble with charts
                  philmodjunk

                  A table of related records--which could have been done back in FileMaker 3, where you have one record for each work and amount entry would be much easier to work with here.

                  For the sake of getting this chart to work, It wouldn't be all that difficult to import the data from your existing data into such a table. You'd need to do one import for each field in order to get this done and could be scripted if you choose to only do this for charting purposes.

                  I do suggest, however, that such a structural change be one that you make even if it takes you a great deal of time to full implement as it will make your data much easier to work with in many areas. If you are unfamiliar with them, read up on Portals as this is a very useful way to implement a layout design where you can work with such a table of related records.

                  • 6. Re: Trouble with charts
                    rjlevesque

                    I must agree with Phil here, I understand you wanting to stick by the old addage "Don't fix it if it ain't broke" but here is a case where it is broke and you just don't realize it. Bringing things up to date is a very important part of any relational database system, syntax changes, methods change, etc.

                    You don't want to take a horse and buggy and just keep adding car stuff to it, you want to rebuild the buggy into a new modern car...still with all the old goodness, just looking and acting brand new and improved!  =)

                    • 7. Re: Trouble with charts
                      deathrobot

                      Thank you both for your input. I will give that a try!