1 2 Previous Next 21 Replies Latest reply on May 17, 2012 2:55 PM by bill1650

    Partial Orders received

    bill1650

      Title

      Partial Orders received

      Post

      I have my database setup with tables for vendors, products, purchase orders and purchase order line items. As things are now I open a new po pick a vendor and then pick what products I need to order. When the products come in they are markd as recieved with the date they are received.

       

      My problem is some of my vendors have been sending things in multiple shipments and many products are back ordered. I'm not sure how to set things up in my database to account for the partially recieved orders. Any help would be appreciated.

        • 1. Re: Partial Orders received
          philmodjunk

          Is this the setup you have now?

          POs---<LineItems>-----Products.

          How you process partial reception of an order depends on how much detail you need to record to support your business practices.

          You could use a script to generate records in an "orders recieved" table where you find or select the PO and a script generates a series of LineItem records to match the original lineItems of the order, but then with an added number field where you enter the quantity received. (There's no guarantee that exactly the qty ordered will be recieved. Partial shipments may be sent and mistakes may be made.)

          • 2. Re: Partial Orders received
            bill1650

            Phil,

            Yes, that is the setup I have now.

            The "orders recieved" table sounds like what I should do.

            Any chance you can help me with the script?

            • 3. Re: Partial Orders received
              philmodjunk

              First you need relationships, then you'll need to decide on an inteface that works for your setup, then you can figure out a script.

              POs---<Received----<RcvLineItems>----RecivedProducts

              would be the relationships that support what I have in mind.

              From there a script can generate both a new Received record linked to POs and new, matching records in RCVLinItems that come from the PO's lineitems but are linked to Received.

              Or you can pull up a list of the PO's lineitems and click a button next to each item to select it and create a matching record in revLineItems. Then you update the new records with quantity info.

              • 4. Re: Partial Orders received
                bill1650

                Okay I have created the tables and relationships and created a layout based on the received table with portals from both the purchase order line items and recvdlineitems.

                I have the po table linked to the received by purchase order ID.

                The received table linked to the rcvdlineitems table by received ID.

                The rcvdlineitems linked to the products table by product ID.

                Is this correct for what you had in mind?

                 

                • 5. Re: Partial Orders received
                  philmodjunk

                  Which option works better for you?

                  A) you click a button on the PO layout and you get taken to a Received Layout with a new record and all PO items listed in a portal.

                  b) You click the same button and are taken to the received layout, but the portal of received items is empty. You then use a conditional value list to manually select items to check off as received. The conditional value list limits your choices to the items in the original PO

                  c) You click that button and see two portals--one with the full list of items from the original portal and an empty portal. You then click a portal row of the PO items to see a matching record for the same item appear in the portal.

                  With all of these options, there are ways to make Entire shipment received as ordered and this line item was received in full single mouse click actions to speed data entry and improve accuracy.

                  • 6. Re: Partial Orders received
                    bill1650

                    I think I like option c best.

                    • 7. Re: Partial Orders received
                      philmodjunk

                      They are all variations on a common theme and are not mutually exclusive. You can even set up several different layouts--each with a different approach to test them out to see which fits your work flow the best.

                      First, let's start with the relationships you had already set up:

                      LineItems>------POs---<Received----<RcvLineItems>----ReceivedProducts

                      POs::__pkPOID = LineItems::_fkPOID
                      POs::__pkPOID = Received::_fkPOID
                      Received::__pkRcvdID = RcvLineItems::_fkRcvdID
                      ReceivedProducts::__pkProdID = RcvLineItems::_fkPRodID

                      All __pk fields are primary key fields and best defined as auto-entered serial numbers or FMP12's new Get(UUID) function.

                      Now to get that portal of a PO's LineItems on the Received layout make a new occurrence of LineItems, POLineItems and link it like this:

                      Received::_fkPOID = POLineItems::_fkPOID

                      Now you can set up a portal to RcvLineItems and a second portal to POLineItems.

                      This script, performed by clicking a button on the PO layout will create a new Received record for that PO. (And you can continue to click this button each time you receive a shipment for that PO.)

                      Set Variable [$POID ; value: POs::__pkPOID ]
                      Go to Layout [Received]
                      New Record/Request
                      Set Field [Received::_fkPOID ; $POID ]

                      This script will copy data from a clicked portal row in the POLineItems portal into the RcvLineItems portal:

                      Freeze Window
                      Set Variable [$ProdID ; value: POLineItems::_fkProdID ]
                      Set Variable [$QtyOrdered ; value: POLineItems::Qty ]
                      Set Variable [$RcvdID ; value: Received::__pkRcvdID ]
                      Go to layout [RcvLineItems]
                      New Record/Request
                      Set Field [RcvLineItems::_fkRcvdID ; $RcvdID ]
                      Set Field [RcvLineItems::_fkProdID ; $ProdID ]
                      Set Field [RcvLineItems::QtyOrdered ; $QtyOrdered ]
                      Go to Layout [original layout]

                      You can either add a button to the portal row or select all fields in the portal row and use button set up to group them as a button to run this script.

                      • 8. Re: Partial Orders received
                        bill1650

                        Thanks Phil the scripts work perfect.

                        After testing them I now have another question. How do I make sure I don't mark something as received twice? Also If I receive only 3 of 5 items ordered how do I keep track that I am waiting for only 2 more?

                        • 9. Re: Partial Orders received
                          philmodjunk

                          I knew that question or one similar would come up. It's the logical extension of this process.

                          We can set up this relationship:

                          POLineItems::_fkPOID = RcvdLineItems::_fkPOID AND
                          POLineItems::__pkLineItemID = RcvdLineItems::_fkLineItemID

                          RcvdLineItems would be a new occurrence of RcvLineItems.

                          Sum ( RecvdLineItems::QtyRcvd ) > POLineItems::QtyOrdered

                          is a test that will tell you that there should not be any more items to receive off of that PO Line Item.

                          You can use this a version of this logic to filter the portal where you click a POLineItem or the script performed when you click it can use the above expression and throw up a warning message that there shouldn't be any more items to receive for that entry. Since it's possible for a shipping error to send you more than you ordered, I'd lean towards the scripted warning message myself.

                          This same type of test can be used with conditional formatting to change text or fill color in the POLineItems portal as a visual cue that the item has been fully "received".

                          Note that this approach allows you to order 20 shovels and get 5 shipments of 4 shovels each.

                          • 10. Re: Partial Orders received
                            bill1650

                            For some reason this is not working the way I expected.

                            I must have made a mistake somewhere but cannot figure out wheat it is. If I try and filter the po lineitems portal on the receiving page it comes up blank. If I try and use the condidtional formating on the po line items portal it doesnt react the way I expect it to either.

                            I cant seem to figure out the script with a message either.

                            I am attaching pictures of my relationships for the receiving table as well as pictures of my scripts and on of how the receiving page looks if I use conditional formating to change the text color when items are clicked as recieved.

                            • 11. Re: Partial Orders received
                              philmodjunk

                              One thing that you can't see at a glance from a screen shot of Manage | database is which table occurrences refer to the same data source table. I've gotten into the habit of color coding occurrences so that occurrences with the same data source table are also the same exact color.

                              I don't think I need that here--your labeling seems pretty straightforward.

                              I think I see the problem, I got a bit ahead of the details with my last post. The script doesn't copy over all the data needed to establish the needed link to the Received_PurchaseOrderLineItems_RcvLineItems table occurrence. Variables should be used with additional script steps to copy the two key values for _fkPOID and __pkPOLineItemsID from Received_PurchaseOrderLineItems_RcvLineItems to POs_received_RcvdLineItems.

                              • 12. Re: Partial Orders received
                                bill1650

                                I added the additional script steps but am getting the same results.

                                The formula for the conditional formating and portal filtering I am using is - 

                                Sum (received_RcvLineItems::Quantity received) ≥ received_PurchaseOrderLineItems::Quantity

                                attached is another screenshot of my script and my received line items table with only the one entry.

                                 

                                 

                                • 13. Re: Partial Orders received
                                  philmodjunk

                                  On what portal did you place the filter?

                                  Sum (received_RcvLineItems::Quantity received) ≥ received_PurchaseOrderLineItems::Quantity

                                  Change your expression to this for the conditional format:

                                  Sum (received_PurchaseOrderLineItems_RcvdLineItems::Quantity received )≥ received_PurchaseOrderLineItems::Quantity

                                  As written and used on the upper portal as a filter, you'd only see those line items that have been fully received. Use < to see all items that have not yet been fully received. Due to the possibility that an error could send you more than was ordered, you may not want the portal filter.

                                   

                                  • 14. Re: Partial Orders received
                                    bill1650

                                    Problem solved. Thank you for all your help.

                                    1 2 Previous Next