9 Replies Latest reply on Dec 12, 2013 1:28 PM by philmodjunk

    PO when a manufacturer is also a supplier

    Badam

      Title

      PO when a manufacturer is also a supplier

      Post

           I have a solution and I am creating a Purchase Order section. I have a parts catalog and within that table I have a field that is the primary supplier and also a secondary supplier for each part. I also have a Manufacturer Table and a Vendors table and they are connected by a Join table (many to many). I want to use that field in the parts table to group items on PO’s by supplier. Now to my question:

           There are times when we buy directly from the Manufacturer and other times when we buy from a vendor (distributor) instead of the manufacturer. Is it best to enter the manufacturers who we buy from into both tables or is there a way to point back to the manufacturer’s table when they are both the manufacturer and vendor?

        • 1. Re: PO when a manufacturer is also a supplier
          AppGuy

               I would suggest having a table with all your venders/manufacturers in there.  Then in that table just classify them. Either in one field or multiple fields..  If they belong to both categories, you could just put some check boxes on the interface so people can check the appropriate boxes.

               This way you could also run reports to see who is a distributor, manufacturer, or both..  Unless you have a specific reason to separate them??

          • 2. Re: PO when a manufacturer is also a supplier
            Badam

                 Thanks for your response. There are some other reasons in other areas of the database that require these to remain separate. 

            • 3. Re: PO when a manufacturer is also a supplier
              philmodjunk

                   Then you had better state those "other reasons" as it is very likely that this is not the case or that there is a way to deal with those issues while keeping all these records in a combined table.

              • 4. Re: PO when a manufacturer is also a supplier
                Badam

                That is a great point. Let me be more specific:

                Here are the reasons I assumed they needed to be in 2 tables. (you know what they say about assuming!! blush  )

                There is some information that is applicable to manufacturers that does not apply to vendors and vice versa:

                The manufacturers need to go through an "approval" process in terms of our Quality Dept. (that status can change annually). That status does not apply to vendors.

                If they are in 1 table, vendors will have an account number, terms, etc. as well as some manufacturers but not if we don't buy directly from them.

                Within the Parts Catalog there needs to be 3 separate fields one for Manufacturer and one for Primary Vendor and the third field would be a Secondary Vendor, so we know who makes it, and who we get it from (I was assuming that needed to be separate tables since that could be the same or it could be different). 

                • 5. Re: PO when a manufacturer is also a supplier
                  philmodjunk

                       I'm not surprised by your reasons for assuming that they need to be in separate tables. This is a classic issue and the possible solutions have been known to trigger passionate debates for and against particular approaches.

                       

                  There is some information that is applicable to manufacturers that does not apply to vendors and vice versa:

                  There are two basic approaches used in many databases:

                  One is to put it all in one table and leave blank the fields that do not apply to a particular group of records. If there is a relatively small number of fields that are only used for suppliers or just manufacturers, this can be a very workable approach. You can set up different layouts for the two groups and just leave out the fields that do not apply. This can even work for very large numbers of fields that are specific to one group or the other, but it then can make working with the list of fields for that table excessively tedious.

                       The other is to add a related "detail" table where you only create a record if there is a need for it. Thus, your account field might reside in a related record that is only created for suppliers (vendors) and not for manufacturers.

                       I once was responsible for managing a database (not in FileMaker) for very different items manufactured or purchased by the company that then employed me. We had a unified table of Skus, where we had one record for every item we might purchase or manufacture, but then there were very differently structured related tables for each type of material and product that we used to "spec" each item for purposes of purchase, quality control and manufacture.

                  • 6. Re: PO when a manufacturer is also a supplier
                    Badam

                         Thanks so much for that explanation. I think that putting them into one table makes sense for my circumstance but if they are in one table how does that work when the user performs a find on a field or fields that apply to both? For example if a user searches for a vendor by Name, that could return names of vendors and manufacturers? I am sure I could use radio buttons to ask "do you want to find Vendor, Manufacturer or both?" but I am hoping to make this fairly transparent to the user.

                    • 7. Re: PO when a manufacturer is also a supplier
                      philmodjunk

                           There are a variety of methods. One way is to set up a "search form" with global fields and a script that uses the data in the global fields to find records. Such a script can include additional criteria if the find needs to filter the results to only show matching records from one group or the other.

                           Here's a thread for scripted find examples: Scripted Find Examples

                           Another way is to set up a layout script trigger that performs a script when the window exits find mode. This script can use constrain found set to constrain the found set to just records of a particular group.

                           Enter Find Mode[]
                           Set field [CombinedTable::ContactType ; "Supplier" ]
                           Set Error Capture [on]
                           Constrain Found Set []

                      • 8. Re: PO when a manufacturer is also a supplier
                        Badam

                             Sorry for the delay in this response, So I have now put in my manufacturers and vendors into one table Called Manu_Vend and within that table I have a "Manu_vend type" field. The 3 possible entries "manufacturer" , "vendor" or "both". per Jason and Phil's advice (thanks!!)

                        Now I am trying to create a value list that shows "vendor" or "both" record but not "manufacturer" to be used in the Purchase Order section. Is that best done via a Relationship or Calculated value?

                        And is there a post that shows a step by step how to set that up?

                        • 9. Re: PO when a manufacturer is also a supplier
                          philmodjunk

                               I'd be inclined to set up the type field as a text field with two checkboxes. If a contact is both a vendor and a manufacturer, I'd click both check boxes. That would make for a very convenient match field to use in a relationship that only matches to Vendor records in order to get a conditional value list of just the vendors.

                               Using such a field you could set up this relationship:

                               POs::constVendor = Manu_Vend::Type

                               where constVendor is defined as a calculation field, with "Text" as the result type and "Vendor" as the sole calculation term.

                               Then you'd set up your value list to list values from Manu_Vend and select "Include only related values starting from POs". to limit the values returned to just those that have the Vendor check box selected.

                               Here are some links for additional info on Conditional Value lists. The forum tutorial link includes an alternative method for getting such a list of values.

                               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                               The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                               Forum Tutorial: Custom Value List?

                               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                               Hierarchical Conditional Value lists: Conditional Value List Question

                               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.