4 Replies Latest reply on Nov 18, 2011 5:42 PM by ErikWegweiser

    Attaching Multiple Vendors to a Single LineItem

    Jason_Farnsworth

      Hello, all

       

      I am not of the best way to add several Vendors to the same Lineitem of an Inquiry?

       

      Currently my table looks like this

      LineItems.PNG

      I want to send the Inquiry to several vendors to bid but only on the lines that apply to them.

       

      The end Result should be printed reports for only the lineItems that each vendor is being requested to bid on. And then of course a overview printed report that tells us all vendors requested to bid on each of the lineitems.

       

      I currently have a seperate Vendor table, with the usual things. Would it be better in the product layout to assign all the vendors to the products that they would be on? Or is this location the best link spot?

       

      Any help in understanding this is greatly appreciated.

       

      Best regards,

       

      Jason Farnsworth

      Midland, Tx

        • 1. Re: Attaching Multiple Vendors to a Single LineItem
          ErikWegweiser

          Hi, Jason:

           

          If I understand correctly, this one inquiry, 8076, requires four parts, each of which might be bid on by the same or different vendor.

          Furthermore, you would only submit to particular vendors, bid requests for appropriate parts, not necessarily all four.

           

          From your screen shot, I'm not sure if the current structure means that Navajo Refining Company is the only vendor

          attached to the entire inqiry, which now must be attached to potentially multiple vendors, by way of the line items instead.

           

          What's presumably missing here is some kind of 'join' table linking individual inquiry line items to one or more vendors each.

           

          Without yet addressing details of how the interface might be redesigned to accommodate the structure, I would recommend adding a new

          table. So in addition to hypothetical INQUIRY and INQ_LINEITEM and VENDOR tables (among several others), add INQ_LI_BIDREQ.

          This table contains, at minimum, key fields to link each record to one inqiury line item and one vendor.

          FileMaker Pro AdvancedScreenSnapz003.jpg

           

          This would allow you to associate each part line item to one or more vendors from which you wish to request bids, because each

          line item may have several related bid request records related to it (each being related to a different vendor).

           

          As mentioned, the interface could get compicated, but I'm imagining including some of the following:

           

          - When you add a part to the inquiry, you could search for one or more vendors to send a bid request, or choose from a pop-up list of vendors already being sent requests for parts of the same inquiry (so you need not do the same vendor search for each part).

          - The line items portal could be filtered to show either all parts for the inquiry or only parts where a selected vendor is being sent a request.

           

          Sounds like an interesting challenge. I hope this was helpful. Best of luck.

          • 2. Re: Attaching Multiple Vendors to a Single LineItem
            Stephen Huston

            Hi Jason,

             

            It's not completely clear from you image what you are getting at, but this is what I am getting from your post:

             

            You have a table of items which need bids, and you have another table of Vendors to make bids. You are looking for way to link more than one Vendor to a single Item requiring bids.

             

            In such cases, the most common a simplest practice is to created a "join table" which I will call  Bid_Requests. It needs only a couple of fields to work: a foreign key to link it to a Vendor, and a foreign key to link it to an Item. You might want to date- or time-stamp it so you know when it was generated to help with identifying which ones to use at any time, and maybe a timestamp to indicate when it was sent. That's just 4 fields. You could add another to store the bid amount you receive on it, etc. This table coud be visible for data-creation from a portal in either parent table, Vendors or Items, populated via Value Lists for selecting the correct value.

             

            Then do your reporting from the Bid-Requests table, where you now have a unique record for each Vendor-Item combination you have created.

            • 3. Re: Attaching Multiple Vendors to a Single LineItem
              Jason_Farnsworth

              Thanks Erik and Stephen

               

              I will start with trying to tinker with these suggestions.

               

              I was wondering if it would be a good Idea to link them long before they reach the Inquiry.

               

              For instance, If in the product table I created a portal that listed all the Vendors then checked the Vendors that provided that type of product. Then when I create a report it pull the infomation from the product table based on all vendors linked to that product?

               

              Again, Thanks

               

              Jason

              • 4. Re: Attaching Multiple Vendors to a Single LineItem
                ErikWegweiser

                That's an interesting concept, and I think it all depends on the intended 'business rules' or process.

                 

                You could start by creating yet another vendor-product type join table that stores all the instances of any vendor providing a particular product type.

                From the vendors table, you could build a portal list of all product types they provide. From the products table, then, you would see a list

                of all vendors that provide whatever type that product is.

                 

                OK, so you've got your 'library' of vendors and their product types. The next question is what should happen when you create an inquiry and

                start adding parts of various product types to it. Do you want to manually, for each part, select one or more vendors who provide the same

                type of product? That would allow you to choose, but would require you to do so.

                 

                The other extreme—and I'm not sure if you're heading in this direction—is to essentially have a fully automatic system whereby whenver you are

                done creating an inquiry with all its part line items, you just push a button and generate a quote request for ALL vendors with matching product types.

                Cool, and it can be done. But is that what you'd wnat it to do — or maybe something in between (also doable).

                 

                -- Erik