9 Replies Latest reply on Oct 25, 2011 10:09 AM by philmodjunk

    Usage Chart Based on Various Records

    mgxdigital

      Title

      Usage Chart Based on Various Records

      Post

      Hello,

      I have a Customers Database and an Orders Database.

      The Customer DB holds our customer info and the Orders DB hold our order  tickets and packing lists.

      I'm wanting to create a layout and put a chart on the layout which will show the total orders shipped each month per whichever customer record you are looking at.

      I'm hoping that i can just have one chart and the data could change for each customer.

       

      Currenly, the Customers DB and the Orders DB are connected via a Customer ID field relationship.

      Any ideas?

       

      Thanks in advance for any help!

        • 1. Re: Usage Chart Based on Various Records
          philmodjunk

          Are you using Filemaker 11? (Required for built in charting).

          Total orders as in the total number of orders records, the total dollar value, the total number of individual line items???

          You could set up such a chart in the orders table and use a button from a layout based on Customers that performs a find or uses Go To Related records to pull up a specified set of Orders Records.

          If you have a field defined like this: cMonthShipped : DateShipped - Day ( DateShipped ) + 1

          Then you can sort these records by month and use the "Use data from current found set" and "Show data points for groups of records when sorted" options.

          Your Y-Axis value would be a summary field that totals the value you want to chart.

          • 2. Re: Usage Chart Based on Various Records
            mgxdigital

            Hi Phil,

            Yes, i am using Filemaker 11.

            I'm wanting a chart that shows months on the X axis and the total number of orders per customer record shown on the Y.

            If i need to, i can put the chart on teh customer layout so that it's shown on each customer record you go to.

            So on customer AAA's record, the chart reads :5 orders in March, 6 orders in April, and so on.

            After moving to next record, customer BBB's chart would read: 4 orders in March, 7 orders in April, and so on.

             

            What would the button look like? I could place it on any layout in orders that related to my customers database, correct?

            Then would i have it take me to my customers database?

            • 3. Re: Usage Chart Based on Various Records
              philmodjunk

              The button would be on a Customers layout and would find the records you want to chart for that customer on an Orders based layout.

              A "count of" summary field in Orders can be used for the y-axis to count your orders for each month.

              Then, with the options I described before, you can set up your chart on this Orders layout.

              Here's one possible script that you might use:

              #Performed by button on Customers Layout
              If [ Orders::OrderID //make sure that there are order records to chart]
                 Go To Related Record [Show only related records; From table: Orders; Using layout: "OrdersChart" (Orders)]
                 Sort [ Restores ; No dialog ] //Sort records by cMOnthShipped so that they are properly grouped for the chart
              End IF

              • 4. Re: Usage Chart Based on Various Records
                mgxdigital

                Thanks Phil,

                The only thing is when i did this, it took me to my orders page, but since customer AAA has 305 orders, it's just displays a single point on a graph on each order report. I am displaying a count of field on my Y axis, but it's not adding every related record. Its showing all of the related records, but not grouping them onto the chart. I hope that makes sense.

                • 5. Re: Usage Chart Based on Various Records
                  philmodjunk

                  The records must be sorted to group them by month and the "Use data from current found set" and "Show data points for groups of records when sorted" options must be specified for the chart.

                  • 6. Re: Usage Chart Based on Various Records
                    mgxdigital

                    I understand. Thank you again.

                     

                    Is there a way for the graph to only show orders that have shipped in the present fiscal year (whatever that may be). Right now it would be 2011, but in January, it would be 2012.

                    • 7. Re: Usage Chart Based on Various Records
                      philmodjunk

                      But of course.

                      Instead of Go To Related Records, you can perform a find for the records you want to see charted. This can include specifying a date range in addition to the customer ID.

                      Keeping the GTRR step, you can also modify the found set produced by entering find mode, specifying the date range and then using constrain found set to drop out records not in that specified date range.

                      Just don't forget to sort your records after you've done so...

                      • 8. Re: Usage Chart Based on Various Records
                        mgxdigital

                        You're the best.

                         

                        Last question about this, say i wanted a line on the chart for this fiscal years order, and last fiscal years orders (so we can see where the customer was this time last year type of snerio). Is this possible? 2 lines on the same graph?

                        • 9. Re: Usage Chart Based on Various Records
                          philmodjunk

                          2 lines are possible, but the implementation is a lot messier. As I understand it, you'd need two different fields, one for each fiscal year, so pulling your data into that format may require a dedicated graphing table that links by month and customer ID to your Orders table or some such instead of the current approach.