6 Replies Latest reply on May 22, 2014 11:34 AM by JaredEkas

    Chart in Many to Many Database

    JaredEkas

      Title

      Chart in Many to Many Database

      Post

           Three tables in question are [customers], [update], [products]   The relationship is many customers to many products with update being the join table.   The only field in update is the three primary keys, and a date timestamp

           The fields I'm trying to chart are [customers - "category"]  with   [products - "group"]

           Customers Category list would be for example:  RED, BLUE, GREEN

           In the (product details layout) related to the [products table], which shows a single product detail and the related customers in a portal, i would like to have a bar graph that shows the Customers Category as the X axis and the count of how many in that category have the current product shown in the layout in the Y axis.

           Probably a simple solution, i have tried to count several fields to get it to work without luck.

           Thanks in advance for your help.

            

            

            

            

        • 1. Re: Chart in Many to Many Database
          philmodjunk

               First a bit of nitpicking:

               

                    The only field in update is the three primary keys, and a date timestamp

               That would be one primary key, two foreign keys and a timestamp. A Primary key is a field that uniquely identifies each record in the table where it is defined. It typically matches to foreign key fields in a related table that are typically not unique as many records in the related table may have the same value in this match field.

               Now a more important detail: Does "category" = "group"? If customer A has "green" selected, does that refer to all products ordered by that customer in the "green" group?

               Do you have FileMaker 12 or newer?

          • 2. Re: Chart in Many to Many Database
            JaredEkas

                 Phil,

                 Thanks i walked right into that one.

                 Category doesn't = group.

                 (customers table has "category" ) Red, Blue, Green

                 (Product table has "group") Hot, Warm, Cold

                 The goal is when looking at a customer, regardless of their category, i will be able to see the chart which displays the count of the Groups of products they have.

                 So when on this customer A,  if they have  5 green products, 2 blue products, and one red product, the chart would show this data.

                  

                 Then the second part would be to reverse engineer this so the same would work when viewing the product detail, and this chart would show when viewing product A / That Hot customers had 20, warm customers had 15, and cold customers had 30 ect.
                  

                 Im using 13 pro

                  

                 Thanks

                  

                  

                  

            • 3. Re: Chart in Many to Many Database
              philmodjunk

                   I don't see how "group" is relevant to what you want to see in the graph.

                   and

                   

                        In the (product details layout) related to the [products table], which shows a single product detail and the related customers in a portal, i would like to have a bar graph that shows the Customers Category as the X axis and the count of how many in that category have the current product shown in the layout in the Y axis.

                   appears to contradict

                   

                        So when on this customer A,  if they have  5 green products, 2 blue products, and one red product, the chart would show this data.

                   If you are on a Product layout (first post) it would make since to see a chart that counts customers, but if you are on a Customer layout (second post) it would make sense to count products

                   And I don't see where groups comes in here at all.

              • 4. Re: Chart in Many to Many Database
                JaredEkas

                     Sorry you lost me let's say the product is apples and the group is yellow green and red.  I know how to count how many apples the customer has I want to chart specifically how many red green and yellow apples he has.  That is where the group comes in.  

                      

                     The next product would be grapes again with yellow red and green.  

                     So I'm not counting how many of the specific products he has I'm counting the group how many red fruits does he have how many green fruits does he have etc.

                      

                • 5. Re: Chart in Many to Many Database
                  philmodjunk

                       I'm still lost. That reads to me that you have a groups of "Red", "yellow" and "green" apples. But you posted that Groups are not categories.

                       And you wanted this on the Products layout--which makes no sense to me. This sounds like you need this on the customer layout instead.

                  • 6. Re: Chart in Many to Many Database
                    JaredEkas

                         Ok let me start over, because i think what is confusing you is the reverse compatibility of the solution.  Lets totally forget about that and start over with only a single direction.

                         In the [PRODUCTS] table i have a (GROUP) field.

                         In the [CUSTOMERS] table lets assume there are no fields that will matter in this except the customer ID

                         When i look at the Customer Detail Layout, Which is related to the [CUSTOMERS] table i have Products related in the many to many direction as mentioned above in a portal.

                         In this Customer Detail Layout, i want to show a graph chart that counts how many products the customer has based on the product (GROUP) Field.

                          

                         So using fruit again,   lets assume i have these products related to this customer from the PRODUCTS TABLE

                         [PRODUCTS TABLE] (NAME FIELD): "APPLE" (GROUP FIELD): "RED"

                         [PRODUCTS TABLE] (NAME FIELD): "APPLE" (GROUP FIELD): "GREEN"

                         [PRODUCTS TABLE] (NAME FIELD): "GRAPE" (GROUP FIELD): "RED"

                          

                         the chart in the customers detail layout would show the following via graph.

                         RED: 2

                         GREEN: 1