6 Replies Latest reply on Jul 3, 2012 2:33 PM by Stephen Huston

    Distributors that are also Customers - Best structure


      I'll be as concise as I can, while seeking help to understand what the best table structure is for this scenario.


      • Distributors sell my product, and so do I. I am a Distributor.
      • Some distributors ask me to send the product to the end Customer, but they invoice them, and I bill the Distributor periodically
      • Some distributors ask me to send product and bill the Customer. Then I pay them a commission.
      • Some distributors buy product from me and handle their own sales.



      • One Distributor can have Many Customers
      • A Customer can belong to only One Distributor, but doesn’t have too.
      • A Customer can be a Distributor, but may not be


      How can I use the Customer file to note all the contact details of Distributors?

      EG Flag them with a Distributor Code (T/F) in order to get the REPORTS I need - primarily "Sales by distributor"


      I am enoying coming back to FileMaker after a few years absence, but having been a long-time developer (of modest applications) since 1985.

      Your help would be gratefully received :-)




      Skype: jmsinnz

        • 1. Re: Distributors that are also Customers - Best structure
          Stephen Huston

          I'm just going to tackle the issue of how to flag a Distributor, but it can be used to create an ID based on any "flagged" value, usually from a radio or checkbox choice field.


          First each record needs its own Primary Key field whcih is unique, a basic of any relationship structure. A second field can be named DistributorKey, and should be set up as a stored text or number field (same as the Primary Key field type), but with an auto-enter Evaluated calculation to populate it as the same as the Primary Key IF the checkbox/radio field contains (PatternCount function used) the text string "Distributor" ; otherwise it is to be empty. This field should be set to auto-enter when either the primary key field or the checkbox/radio button field is edited.


          That will give you the same Distributor Key as the Primary Key if they are a Distributor, and you can use that to do the relationships andas the basis for reporting. You can even set a field for Distributor Name to behave the same way for a field from which to collect the names of just Distributors for a value list.

          • 2. Re: Distributors that are also Customers - Best structure

            Thank you Stephen for your very clear description.
            I'm going to try this and let you know how I get on, but I wanted to send a quick thank you for taking the time to reply.

            • 3. Re: Distributors that are also Customers - Best structure
              Stephen Huston

              You're quite welcome.


              I have several places where I use these types of aut-enter/evalutes for Keys and Names fo building value lists and special relationships so that only records of the correct type appear in the lists and reports. By making them auto-enter/evaluates they can be stored/indexed so that the relationships work both directions, unlike unstored calcs.


              Good luck.

              • 4. Re: Distributors that are also Customers - Best structure

                I'm doing pretty good, and have come up with a structure which works. I thought I had a breakthrough understanding this morning, of how TO's can filter records to create TO's which contain a subset of records based on the defined relationship.


                I'm wanting to display a VList (in Invoices) to display only those Customers who have a Customer_Key the same as the Customer_ID. The Customer_Key calc is working and displays the correct text for the chosen customers, and for the others it is blank.


                I've created a TO for Contacts related to Contacts where the Contact_ID = Customer_Key


                I've attempted to create the VL to "Include only related values starting from" and using the VL TO - but it comes back blank (nothing in the list - unless I "Include all value" and then it gives me everything in the Contacts Table.


                Please help me. Clearly there is something that I'm not understanding?

                • 5. Re: Distributors that are also Customers - Best structure

                  OK, I found the answer. It took me a while, lots of experimentation, and referencing the FM Training Series (Capter 4 - Page 33).

                  The elements that I learnt and need to remember are around:


                  • using a constant global as one half of the relationship match
                  • making sure the fields are of the same type (eg Text)
                  • 6. Re: Distributors that are also Customers - Best structure
                    Stephen Huston

                    You can create a value list of all values in the field(s) for the auto-entered special Key and special Name fields, which will list only those who have that status. You don't need and TO fitering to do that.