I have 3 tables which are ITEMS, PURCHASE_REQUEST, and PURCHASE_ORDER.
Since the ITEMS and PURCHASE_REQUEST are many-to-many relations, I have a portal named PRLine in between this two tables. This portal, PRLine, works in the layout of PURCHASE_REQUEST without any problem.
Here is the question, should be very simple
In the layout of PURCHASE_ORDER, I would like to have a drop down list showing every "REQUEST id" from the table PURCHASE_REQUEST. Then, on the other drop down list there should be dynamically showing records grouped by the "producer" in that particular PURCHASE_REQUEST. Once user choose one particular producer, the third area should showing the details. For example, in the database there are two Requests, req1 and req2. In each request there are multiple items.
In req1, itemA and itemB came from producerXX; itemK came from producerYY
In req2, itemA, itemC and itemD came from producerXX; itemJ and itemK came from producerYY
the user should use the solution like this:
click the request list, showing req1 and req2;
clike the producer dropdown which is showing XX and YY;
click the XX;
showing itemA, itemC and itemD
I have tried may times to set up this portal but no luck. Please help!
Tables are like these:
ITEMS: kp_item_id, item_name, producer, price
PRLine: kp_prline_id,kf_item_id,kf_PR_id, quantity
PURCHASE_REQUEST: kp_pr_id, kf_po_id, date_submitted, submitted_by
PURCHASE_ORDER: kp_po_id, date_ordered, ordered_by