8 Replies Latest reply on Jul 28, 2016 3:52 PM by tech@phillipit

    very basic portal question



      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 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

        • 1. Re: very basic portal question

          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.

          • 2. Re: very basic portal question

            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?


            thanks again


            • 3. Re: very basic portal question

              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.

              • 4. Re: very basic portal question

                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



                • 5. Re: very basic portal question

                  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.

                  • 6. Re: very basic portal question

                    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?

                    • 7. Re: very basic portal question

                      Let's add more to our data model:


                      with this:

                      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) ]

                            New Record/Request

                            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 ]

                             End Loop

                             Exit Loop If [ Not IsEmpty ( LineItems::_fkOrderID ) ]
                      End Loop


                      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.

                      • 8. Re: very basic portal question

                        I changed a little bit. now it works very good. thank you very much!