Whenever you think "Many to many"...you likely also should think "Join table". I think you are one table short...the join table.
ITEMS---< JoinTable >----PurchRequest
Each Join record would have PurchReqID# and ItemID#.
Set this up, and show the Join Table records in your portal...then you should be able to pull over the information you are looking for.
Thanks for your advice.
Is it the table PRLine not a JointTable? Or, you are suggesting save PurchReqID# and ItemID# in the PRLine table instead of using foreign keys only?
Why one table for purchase requests and another for purchase orders?
Seems to me that the main difference between a "request" and an "order" is a change of status. A "request" is still being completed or is awaiting appropriate approval. An "order" would seem to be the same record but with a status change showing that it has been approved and submitted to the vendor.
If so, you can use one table for both Purchase Orders and Purchase Requests with a status field to show the difference. This can then leave you with a single Line Items table that can serve as a join table linking it to a master table of Products or inventory.
If this is possible, it would simplify the design of your DB.
I do know that would be much simpler. However there are more requirements for the design.
1. the price of the item could be changed during the "request" and "order" steps. for example discounts. and my user do want to know the original and current price of those items.
2. in one request there could be more than one providers and in one order only one provider can be used. For example in the request there are FIVE items came from THREE different vendors. I will give the user the ability to chose each one of them to create THREE different orders. On each order only one vendor shows up.
Right now in the Order layout, I can make a portal showing all items requested in one particular REQUEST base on the chose the request id. I need to make them grouped by the name of vendors.
basically in my testing scenario
the user should use the solution like this:
click the request list, showing req1 and req2; DONE
clike req2; DONE
clike the producer dropdown which is showing XX and YY; NEED HELP
click the XX; NEED HELP
showing itemA, itemC and itemD NEED HELP
Issue 1 can be handled with 2 different fields in the same line item record--> one for the requested amount and one for amount when ordered.
Issue 2 is exactly why our system has both requests and orders also, but it was not clear that you did in your original post so I was checking.
The basic data model I would use would look like this:
P_Request::__pkRequestID = LineItems::_fkRequestID
P_Order::__pkOrderID = LineItems::_fkOrderID
__pk fields auto-enter a serial number or Get ( UUID ) text
_fk fields are normal data fields of the same data type as the __pk field to which they are linked.
This lets you use one set of line items for both Requests and Orders. It assumes that the total number of line items stays the same though different groups of line items can be linked to different requests and different orders.
Note that line items serves as your Join table here and that you have two pairs of ID fields for setting up the needed relationships. There are a number of different User Interface strategies that would allow you to start from a single Request with multiple line items in a portal and then produce 1 to many Orders with one order for each vendor.
One method is to specify a vendor ID in the line items table and then use Go To Related Records pointed at a layout based on line items to create a found set of just one request's line items. The records can be sorted to group them by vendor and your script can loop through them and assign each group of line items to a different Order with a link to a different Vendor.
Thank you very much for your effort.
The suggestion looks very complicated to me. Could you give me a script example or a little bit more details on how to do that?
Let's add more to our data model:
P_Request::__pkRequestID = LineItems::_fkRequestID --> enable Create option for LineItems in this relationship
P_Order::__pkOrderID = LineItems::_fkOrderID
LineItems::_fkVendorID = Vendors::__pkVendorID
On your P_Request based layout set up a portal to LineItems with _fkVendorID formatted with a drop down list of VendorID's and Vendor names.
A sample script to set up 1 or more Purchase Orders, 1 for each vendor:
If [ Not IsEmpty ( LineItems::_fkRequestID ) // make sure that there are lineitems to process ]
GoTo Related Records [ Show Only Related ; table: LineItems ; Layout: "LineItems" ( LineItems) ]
Sort Records [Restore ; No dialog ] ---> Sort on _fkVendorID
Go To Record/Request/Page [First]
Go To Layout ["PurchaseOrders" (P_Order) ]
Set Variable [$POID ; value: P_Order::__pkOrderID ]
Go To Layout ["LineItems" (LineItems) ]
Set Field [LineItems::_fkOrderID ; $POID ]
Go To Record/Request/Page [Next ; exit after last]
Exit Loop if [ GetNthRecord ( LineItems::_fkVendorID ; Get ( RecordNumber ) - 1 ) <> LineItems::_fkVendorID ]
Exit Loop If [ Not IsEmpty ( LineItems::_fkOrderID ) ]
After running this script, you should be able to go to a layout base on P_Orders and use a portal to LineItems to view the items on that order.
Caution: I have not tested this script so I could have missed a detail. I also entered this on an iPad at a time when I didn't have access to scripts workspace to check exact syntax on these screen steps so names an parameter order on them could be a little different if my memory failed to perfectly recall the syntax.
I changed a little bit. now it works very good. thank you very much!