10 Replies Latest reply on Sep 12, 2014 9:51 AM by philmodjunk

    Tracking Assigned Products

    leftear13

      Title

      Tracking Assigned Products & Sales by Distributor

      Post

      Hi,

      I am looking for best way to track products assigned to certain distributors along with such distributors sales on those assigned products.

      We have our  line item table related to the products table (via item#) and customer table (via cust#). <Products>------<LineItems>-------<Customers>

      Every month we assigned products to distributors abroad.  We work with multiple distributors in a single territory. For example, Product A might be assigned to Customer XXX in the UK, Customer YYY in Germany and Customer ZZZ in France. Product B might be assigned to Customer AAA in the UK, Customer BBB in Germany and Product B assigned to Customer ZZZ in France, etc. After we ship inventory to each Customer, they then report back each month on units sold of each Product.  We want to be able to track those products assigned to them along with their unit sales, if any. If a product was assigned to a certain customer but there's no unit sales from them on the product, we want to be able to see that (as well as those products they have sold).

       

      How should we setup this up? We thought to create territory fields in the Products Table and use value lists for each populated with each customer in respective territory, but I am not so sure that's right.

      The ultimate goal is to (1) show a list of all products during a given month and pick which customers to assigned them to by territory (we work with 5 customers in germany, 3 in France, 5 in the UK, 4 in the Benelux, etc. Once assigned each product then is exclusive to that customer for their territory). And (2) track units sales, if any, on those products assigned.

      Thanks!

        • 1. Re: Tracking Assigned Products & Sales by Distributor
          philmodjunk

          Is a distributor and a customer one and the same?

          I will assume yes for this response.

          Can a distributor/customer be assigned more than one product?

          If, so, you have a many to many relationship. A given customer can be assigned many products and a given product can be assigned to many customers.

          Customers-----<Customer_Product>-----Products

          Customers::__pkCustomerID = Customer_Product::_fkCustomerID
          Products::__pkProductID = Customer_Product::_fkProductID

          You can place a portal to Customer_Product on the Customers layout to list and select  Product records for each given Customer record. Fields from Products can be included in the Portal to show additional info about each selected Product record and the _fkProductID field can be set up with a value list for selecting Products records by their ID field.

          At the same time, you will need to set up a table that tracks the sales of each product by each customer. That needs to be yet another table also linked to an Tutorial: What are Table Occurrences? of Products and an Occurrence of customers.

          CustomerSales-----<Sales>-----ProductSales

          A Creating Filemaker Pro summary reports--Tutorial based on Customer_Product can show what products have been assigned to each customer. A summary report based on Sales can show your sales data for monthly, quarterly, etc sales with sub totals broken down by customer.

          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Tracking Assigned Products & Sales by Distributor
            leftear13

            Thank you.

            Yes, I figured this was a many to many relationship considering customers/distributors can be assigned to many products and many products can be assigned to many customers/distributors. I have read your explanation via your first post, which was very, very helpful, and understand (i think!) what needs to be done. Though, I am still uncertain on a couple of things.

            In your example: Customers-----<Customer_Product>-----Products

            What exactly is Customer_Product?  Is it a table occurrence of Customers (or Customers 2) related to table occurrence of Products (or Products 2)?

            I have a few other questions, but thinking they might be answered based on the answer to the above.

            Just some background and setup of how we've been doing this in the past.  Basically, additional "territory" fields were created in the Products Table.  Each field represented a country (for example: territory_germany, territory_france, territory_uk, etc).  Each of those 'territory' fields were a popup with valuelist listing each of the customers/distributors in that respective territory.  Separate Layouts representing each territory were created.  Each 'territory layout' listed all the products to be announced along with the popup territorial field specific to that layout, and then we'd pick from the popup list which customer to assign the product to. 

            So, the process was for example:  In our Products layout, we'd find all our products setup to be announced for October.  We'd then go to our Germany layout which lists all the products to be announced, and then we'd pick from the popup list, which customer/distributor in Germany to assign each of the products to. Then, we move onto the Layout for France, Uk, Benelux, etc. and assign those October products to a customer/distributor in those territories.  This worked for us.  The only problem though is that I couldn't match up Products/Distributors unit invoiced sales from the Line Item Table accordingly. Below is what each territory layout looks like. 

            Product A......Assigned to Distributor B

            Product B.....Assigned to Distributor A

            Product C......Assigned to Distributor F

            Product D......Assigned to Distributor A

            Product E.....Assigned to Distributor B

            Product F.......Assigned to Distributor A

            Product G......Assigned to Distributor F

             

            Ideally, what'd like it to look like this:

            Product A......Assigned to Distributor B.........5 units sold

            Product B.....Assigned to Distributor A.........1 unit sold

            Product C......Assigned to Distributor F.......0 units sold

            Product D......Assigned to Distributor A.......10 units sold

            Product E.....Assigned to Distributor B.......3 units sold

            Product F.......Assigned to Distributor A........0 units sold

            Product G......Assigned to Distributor F.....7 units sold.

             

             

             

            • 3. Re: Tracking Assigned Products & Sales by Distributor
              philmodjunk

              What exactly is Customer_Product?  Is it a table occurrence of Customers (or Customers 2) related to table occurrence of Products (or Products 2)?

              It is the table occurrence of a data source table that is neither Customers nor Products. It's a separate join table. note the different fields specified for it that would not be part of either customers or products.

              Basically, additional "territory" fields were created in the Products Table.  Each field represented a country (for example: territory_germany, territory_france, territory_uk, etc).  ...

              I don't see how that can possibly work for what you describe. The territories would seem to be something you specify for a given customer, not a product since each customer has a specific territory and products can be in any number of territories.

              • 4. Re: Tracking Assigned Products & Sales by Distributor
                leftear13

                It is the table occurrence of a data source table that is neither Customers nor Products. It's a separate join table. note the different fields specified for it that would not be part of either customers or products.

                Totally got it now. Had implemented this prior to posting but the problem was that I included the different fields specific for the join table in the Products and Customers tables. 

                 

                So now, I have a portal on my Customers layout. The Portal includes the _kfProductID field with valuelist to select/assign a product to the customer record. But along with the ProductName field from the Products Table. When selecting a product; however, the ProductName doesn't populate accordingly. When going other Customer records, the ProductName field is populated for some reason.   Images below:

                 

                 

                • 5. Re: Tracking Assigned Products & Sales by Distributor
                  leftear13
                  /files/09b72a9e2a/Capture2.JPG 1189x261
                  • 6. Re: Tracking Assigned Products & Sales by Distributor
                    leftear13
                    /files/2e3ab5be86/Capture3.JPG 1188x210
                    • 7. Re: Tracking Assigned Products & Sales by Distributor
                      leftear13

                      So now, I have a portal on my Customers layout. The Portal includes the _kfProductID field with valuelist to select/assign a product to the customer record along with the ProductName field from the Products Table.

                      When selecting a product; however, the ProductName doesn't populate accordingly. When going to a different Customer records, the ProductName field is populated for some reason.   Any insight, or reason why the ProductName field would be populated?

                      • 8. Re: Tracking Assigned Products & Sales by Distributor
                        philmodjunk

                        It's hard to tell from here. It might be a relationship problem. You might not have the link to products defined correctly. It might be a data problem, the value in one of the match fields doesn't match to the data in the other record's match field or it might be a "table occurrence" issue where you specify a field from PRoducts, but it's not the table occurrence of projects that is linked to your join table.

                        Here's an older format demo file on many to many relationships that you may find helpful as a way to compare what you have set up to what is in the file: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                        If you are using FIleMaker 12 or newer, use Open from FileMaker's File menu to open this file. That will produce a copy converted to the newer File format. This file might, in the future, be replaced by an entry in my new series of instructional database files called "Adventures in FileMaking", Many to Many Relationships is one of several topics under consideration for #3 in the series.

                        Caulkins Consulting, Home of Adventures In FileMaking

                        • 9. Re: Tracking Assigned Products & Sales by Distributor
                          leftear13
                          You nailed it, it was a relationship issue which I've since fixed.  All is working accordingly now and then some. Thanks for your initial post. After reading it, it, it sparked the need for additional research including schema design, importance of pk and fk keys, etc. To say the least, I've learned a heck of a lot. So, thanks again.  Not sure what I would do without this forum.
                          • 10. Re: Tracking Assigned Products & Sales by Distributor
                            philmodjunk

                            You may find my first two "Adventures in FileMaking" offers of assistance in "furthering your research":

                            They are free to download.

                            Adventures in FileMaking #1 - Conditional Value Lists (also includes details on how to set up a basic field based value list)
                            Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

                            Caulkins Consulting, Home of Adventures In FileMaking