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.
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.
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.
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?
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).