3 Replies Latest reply on Sep 13, 2016 9:45 AM by realgrouchy

    Creating a chart of number of items due in coming days

    realgrouchy

      Never having used charts before in FMP I think I need some advice on this problem instead of wasting a bunch of time spinning my wheels trying to find a solution.

       

      I want to create a chart showing how many items are due in each of the upcoming days, i.e. a bar graph showing values sort of like this:

       

       

       

       

      #

      #

      #

      #

      _

      #

      #

      _

      #

      #

      #

      #__

      #

      #

      _
      Due in -3 daysDue in -2 daysDue in -1 daysDue todayDue in 1 dayDue in 2 daysDue in 3 daysDue in 4 daysDue in 5 daysDue in 6 daysDue in 7 days

       

      I want this to appear in my layout where I browse the items themselves. So each entry on the current table has a due date, and a field that calculates from this how many days away (or past) it is due, however for any given due date, there would be any number (zero, one, or more) of records due on that date.

       

      I'm not sure how to configure my chart to pull the right data, and I suspect calculated fields may need to be involved (I'm hoping not more tables... I have enough tables already!).

       

      Can anyone offer advice on if there is a relatively easy way to accomplish this?

       

      Thanks,

       

      - RG>

        • 1. Re: Creating a chart of number of items due in coming days
          jbrown

          Good morning. Sorry for the delay in responding. I wanted to put together a file for you.

           

          Here are some basics: you can set up a chart to show data from "Related Records" (Look at the "Data Source" tab of the char tool). I created a zSystem table and related from this to the Items table using a Cartesian Join.

          I used two fields in my example: A "daysDue_c" field and "DaysDue_Text_c" field. The first field here finds the number of days between today and the DueDate of the item. I also created a summary field that does a count of the records. When I then go to sort the chart data (Data Source) by the DaysDue, this summary field does the counting for me.

           

          The 2nd chart is a chart based off ExecuteSQL. As I say in my notes, I prefer this kind of chart. Instead of using a data source of "Related Records", I use "Current Record (delimited Data). This allows me to ignore the layout context and put the chart pretty much anywhere.

           

          I hope this is helpful to get you started. Feel free to ask questions...

           

          (I purposefully didn't explain EVERYTHING. I hope you can pick this apart and learn something more)

          • 2. Re: Creating a chart of number of items due in coming days
            realgrouchy

            Thanks Jeremy, that's awesome!

             

            I don't understand it yet, but I can see that it works and therefore I should be able to pick it apart when I get a chance to revisit this. I look forward to learning something new out of it.

             

            Just one question - in the home layout, your note says that the SQL layout "...uses one layout and you can change what is shown on the chart by the dropdown."  What dropdown are you referring to?

             

            Thanks,

             

            - RG>

            • 3. Re: Creating a chart of number of items due in coming days
              realgrouchy

              Thanks again. I've finally gotten around to working on this and getting it to work.

               

              I think they key part was making sure to sort the chart in the "Data Source" panel of the Chart Setup box.

               

              - RG>