10 Replies Latest reply on May 13, 2010 12:28 AM by nenupharvn

    purchase order system - one record many field

    BryanBoyd

      Title

      purchase order system - one record many field

      Post

      Please help. I created a purchase order table that has many field in each record. The unique field is the purchase order number, however, there are six lines that have are designated

       

      Purchase Order Nu

       

      line 1  description 1 job number 1 price 1

      line 2  description 2 job number 2 price 2

      line ?  description ? job number ? price ?

       

      etc., all the way through six lines.

       

      Even though these are individual fields and are not related by line, in other word line 1 is not related to description 1, etc. . . . . I really need to have each line be a record that includes the purchase order number. I have been able to create six new tables that have each line as a record using relationship with purchase order and job numbers, but then I am having trouble combining the records so that I can report purchasing by job number. I am not sure if I set the original table up correctly because there are many fields, the job number field being the one I want to use to look up information.

       

      Can you help me?

        • 1. Re: purchase order system - one record many field
          Jade
            

          Hi Bryan,

           

          I suspect that the structure that you are trying to get to is the following:

           

          1. Purchase Order table that contains the Purchase Order Number, Customer ID, Order Date, and other fields that are common to all the lines.

           

          2. Purchase Order Item table that contains the Purchase Order Number (as above) and an Order Item Line number.  This table also has the description, job number, quantity, and selling price for each line item.

           

          3. Customer table

           

          4. Product table that contains the descriptions and prices.

           

          5. Job table that contains the job numbers (optional).

           

          The Purchase Order table must be related to the Purchase Order Item table using the Purchase Order Number as the key (one to many relationship).

           

          This structure will allow you to add a portal containing from 1 to n line items on the Purchase Order layout.  You can have any number of line items and not be limited to 6.

           

          If this is your goal, give it a try and then post back with any questions or problems you have. 

          • 2. Re: purchase order system - one record many field
            BryanBoyd
               thank you for responding. i think you have identified one of my problems. I did not set up two separate tables, one with the purchase order number and common fields (supplier, requisitioner, order date, etc). and the other table with line items related to the purchase order. I do have tables (like job number table) that are used to look up information for specific fields. I am still trying to figure out how each line items can be a separate record (or act like one) if I have used different field names for each input. Even with two tables, as you suggest, how do I sort each line by job number to put together a report or purchased items for a job? I have been trying to solve for several days now.
            • 3. Re: purchase order system - one record many field
              Jade
                

              The key to your problem is the Purchase Order Item table.  Each order item record in this table contains one line item and each record is linked to one Purchase Order and one job number, right?  

               

              So if you sort all of the records in the Purchase Order Item table by job number…you get your breakdown needed for the report.  Create a report layout based on the Purchase Order Item table that is sorted by job number.

              • 4. Re: purchase order system - one record many field
                Jade
                  

                "I am still trying to figure out how each line items can be a separate record (or act like one) if I have used different field names for each input."

                 

                By adding a "portal" of the Purchase Order Item table to the Purchase Order layout, all the line items that are related to the Purchase order are shown in the portal.  

                • 5. Re: purchase order system - one record many field
                  DonorTracker
                    

                  Brian,

                   

                  The Intermediate tutorial at this site would seem to deal with the design issues you face. You can do about a third of the lessons for free. And you can also get a free invitation for a one-month pass by downloading the latest FileMaker Trial version.

                   

                   

                  • 6. Re: purchase order system - one record many field
                    BryanBoyd
                       This has worked well. Thank you. One more question . . . using the portal works great and I have it set for 10 records for each order number. Can you tell me why that a new portal line is establish every time that I enter information into the first field of the current record? I have checked the set up entry modes and nothing is out of whack there, there is not a button on the field executing a script to execute a new record, so it must be something in the portal feature that I am unaware of. Can you help?
                    • 7. Re: purchase order system - one record many field
                      Jade
                        

                      Aren't portals neat?

                       

                      I think what you are describing is that an empty (blank) line is created in the portal when you add a new Purchase Order record, is that it?  If so then you probably allowed the creation of Order Line Items via the relationship when you setup the relational link between the Purchase Order and the Order Line Items tables.

                       

                      That means that you can use the portal to enter new line items which is probably what you want. The line item record is not created until you enter data. There will always be a new blank line at the end of the portal list for this purpose…another neat feature. 

                       

                      Enjoy, 

                      • 8. Re: purchase order system - one record many field
                        BryanBoyd
                           One last question and then I think I am done. Yes I did set up in the relationship the ability to add new records. Does this mean that there will be a blank record in the file for each purchase order that is established? This is so much more efficient than my original purchasing system. I went from one inefficient table to two tables that have 17 fields that is very efficient to manipulate data in. Thank you.
                        • 9. Re: purchase order system - one record many field
                          Jade
                            

                          No, there are no blank records in the table; only in the portal on the layout.

                           

                          One more thing… Some of the fields in your Order Items table should be Lookups.  These are copies from the source data.  Normally, we try not to duplicate data but occassionally, it's necessary.

                           

                          For instance, I think that you would want to copy the price into the Order Item record. That way, if you change the price on the product later, it will not change the selling price on the Order Item.  Conversely, if you discount the price on the Order Item record, it will not affect your standard price on the product. 

                          • 10. Re: purchase order system - one record many field
                            nenupharvn

                            These are many positions of    Purchasing department. It can include:

                             

                            1.  Purchasing coordinator
                            2.  Purchasing assistant
                            3.  Purchasing supervisor
                            4.  Purchasing executive
                            5.  Purchasing assistant manager
                            6.  Purchasing associate
                            7.  Purchasing manager
                            8.  Purchasing clerk
                            9.  Purchasing officer

                            10.   Purchasing director

                            11.   Purchasing specialist

                             

                            Based on the above positions can help you to set up    Purchasing dept, design job descriptions,    Purchasing interview questions.

                             

                            Apart from that, this link below may be useful: http://www.humanresources.hrvinet.com/purchasing-job-description

                            I hope that this comment can help some info for our communities.

                             

                            Rgs