7 Replies Latest reply on Jan 19, 2011 12:30 PM by philmodjunk

    Purchase Order Database Solution?

    DanielClark

      Title

      Purchase Order Database Solution?

      Post

      I have a Parts Inventory table and a Purchase Orders table. I am trying to set up a way to add parts from my Parts Inventory table to my Purchase Order layout. I wanted it setup so you could choose the parts from a list (there are over 1000 parts, so through a list, some way to search by typing in the part name would be nice), and if the part wasnt there, one could just add it in, and the part would get added to the Parts Inventory table.

      On top of this, I would also like to see, under my Parts Inventory Layout, to see what Purchase Orders that part is featured on. For example, I go to a Purchase Order, see part "Phillips Screwdriver" in its layout, I then can click on that part, which brings me to "Phillips Screwdriver" part layout in my Parts Inventory table. From there I can see what other Purchase Orders that part is on, and I can click on any of the Purchase Orders, and I'll be brought to that Purchase Order's layout in my Purchase Orders table. (phew!)

      So how can I do this? Is it easy? Thanks.

      *BTW- I used to have this setup in Bento, except it wouldnt go both ways automatically, I would have to add everything manually. But I would just use the related records field functionality so that I could add a part from the parts inventory section by searching for the part name and neatly adding it in.

        • 1. Re: Purchase Order Database Solution?
          philmodjunk

          Have you looked at the Purchase Orders starting points solution for possible ideas?

          The first requirement is get the correct table structure and relationships.

          Vendors----<PurchaseOrders-----<LineItems>------Parts?------?Vendors 2    (>---- means many to one)

          The above is a typical way to do this where Vendors and Vendors 2 refer to the same table. Vendors is used to link a purchase order to a specific vendor to identify from whom the parts on a given order will be purchased and Vendor 2 can be used to document vendor info for a specific part. (In some businesses more than one vendor can supply a given item and in others, this is not the case, thus the relationship between Parts and Vendors 2 can differ depending on your business model.)

          Typically, a portal to LineItems is placed on PurchaseORders to list each type of item, quantity, price etc for the items purchased in that transaction. Parts supplies information about the part selected in the LineItem portal.

          Once you have that basic system in place, you can implement a simple value list drop down for selecting parts for your Purchase Order and you can also create more sophisticated tools that allow you to search for a part by name.

          Oh yes, and LineItems can be used to document changes in your inventory, while calculation fields in Parts can compute current inventory levels as well as documenting re-order points.

          • 2. Re: Purchase Order Database Solution?
            DanielClark

            Ok, thats a start but here are some follow up questions, maybe you can help me with:

            So if I portal LineItems over to my Purchase Orders layout, and setup a relationship between LineItems and Parts, I should be able to pull parts from my LineItems portal?

            Can I create new records in Parts via my LineItems portal (from the PO Layout)?

            How would I implement a searching feature to look for parts?

            And finally how can I see the PurchaseOrders that a specific part is located on via my Parts Layout?

            Thanks again.

            • 3. Re: Purchase Order Database Solution?
              philmodjunk

              My first post was just to lay the ground work.

              I should be able to pull parts from my LineItems portal?

              Depends on what you mean by "pull". Generally speaking, when you select a part ID in the LineItems record, you are indicating that you will purchase that item on a given purchase order. Thus data is usually "pulled" via one method or another from parts--not the other way around.

              Can I create new records in Parts via my LineItems portal (from the PO Layout)?

              No, nor does this really make any sense. If you are purchasing a part not already listed in Parts, you'd switch to the parts table and create a new record for it there first. Keep in mind that the "parts" table that I am suggesting acts as a "catalog" of items you may choose to purchase from one of your vendors. This process can be automated so that the first time you purchase a given part, you enter in the needed info and then a script takes this data from the LineItem row and creates the new record for you.

              How would I implement a searching feature to look for parts?

              There are several approches you can use. Here's one: 

              Tutorial: How to use an auto-complete drop down list when selecting records related by ID number I'm posting this here for your future reference. I don't recommend you spend any time with the above link until you've got a more basic way of setting up your system in place. Once you have that working, you should have a better understanding of how your database works and you'll better understand all the details listed in this thread.

              Before you try that approach set up your partsID field in LineItems as a value list where you can select a part by ID. Here's a demo file created by Comment that uses this simpler method:  

              It's for invoicing, but the table structure is the same. If you can't figure out how this method works, post back and I'll explain further.

              Please note thatu in a full up system where you both buy and sell your parts, your invoices and purchase orders can use the same LineItems table and this can lay the ground work for a full up inventory management system where you can log all changes to your inventory in this same table.
               
              finally how can I see the PurchaseOrders that a specific part is located on via my Parts Layout?

              You can either place a portal to Purchase Orders or a portal to LineItems on your parts layout. A portal to purchase orders can list details specific to the entire purchase order such as the PO#, vendor and date. A portal to lineitems can list that info plus the quantity of parts ordered. (Fields from the Purchase Orders table may be added to a portal based on LineItems.)

              • 4. Re: Purchase Order Database Solution?
                DanielClark

                Ok thats a lot of help, and I'll start digging through it. However, I just made a portal and am unable to add any records. My LineItems list is related to my PO list through a unique field (k_ID_PO - Purchase Order to _kf_ID_PO - LineItem) and my LineItem is linked to my Parts (using similar relationship except k_ID_Partno). Whats the deal?

                I mean, I know there are no records in my LineItems table right now, but I wanted the LineItem record to be created by finding a part (either dropdown value list, or search method) from my Parts table and then being able to edit quantity and price for that item (which are fields in LineItems) via this portal. And I would like to do this for many parts per Purchase Order. So I must be missing something here, because just a portal by itself doesn't seem to be the solution.

                • 5. Re: Purchase Order Database Solution?
                  philmodjunk
                  1. Open Manage | Database | Relationship
                  2. Find the line linking your Purchase orders to LineItems
                  3. Double click it.
                  4. Select the check box for "Allow creation of records via this relationship" for Line Items.
                  5. Return to your layout and you should now be able to add new records in line items just by entering data in the bottom blank row of the portal.
                  • 6. Re: Purchase Order Database Solution?
                    DanielClark

                    This allows me to create new ones, which is great, but I wanted the LineItem record to be created by finding a part (either dropdown value list, or search method) from my Parts table and then being able to edit quantity and price for that item (which are fields in LineItems) via this portal. In other words, I don't really want to be able to create new line items per se, I want to add to line items from my Parts table (via a dropdown list or search method). And I would like to do this for many parts per Purchase Order. So I must be missing something here, because just a portal by itself doesn't seem to be the solution.

                    • 7. Re: Purchase Order Database Solution?
                      philmodjunk

                      Did you check the invoices demo file? It does that.