7 Replies Latest reply on Jan 23, 2014 8:34 AM by Badam

    Create Purchase Order(s) from a Bill of Material

    Badam

      Title

      Create Purchase Order(s) from a Bill of Material

      Post

      I am working in FM 12 and I have a group of items on a Bill of Material (BOM), the items usually come from different vendors so therefore from one BOM there would be multiple Purchase Orders created (one for each vendor needed). The Purchase Order is made up from 2 tables, “Purchase Order” and “Purchase Order Lines”. The BOM uses parts from a parts catalog and within that catalog each item has an assigned vendor. I am looking for a script or something that would sort through the BOM and create however many Purchase Orders are needed depending on however many vendors are required to get all of the items. I am not sure if this is a script or some type of filtering etc. or whatever is the best way to process this? I have searched the forum for this topic but either I am not using the correct search or it is not there. Thank you in advance for any help or suggestions. 

        • 1. Re: Create Purchase Order(s) from a Bill of Material
          philmodjunk

               You would appear to have these tables and relationships:

               Item----<BOM>-----Parts (in many systems, Item and Parts are two occurrences of the same table.)

               Vendor-----<POs----<POLines

               Do you need/want each PO to be linked to a specific item or could a PO combine needed parts for more than one BOM as long as they are from the same vendor?

               Are you assembling just one item or do you need to multiply the part quantities specified in the BOM by the number of Items you plan to manufacture/assemble?

               Also seems like this may be more complex than currently illustrated here. Don't you keep any Parts in stock? Do you really order all parts before you assemble a given Item? If you have an inventory of parts on hand, the script needed to generate your POs would also need to check your parts inventory to determine what parts and quantities should be ordered.

               The basic outline of the process is the same, a script can loop through each of the records in the BOM, using the Vendor id from Parts to determine which vendor to put on the PO and the system can generate POLines for an existing "open" PO or it can start a new PO for that vendor if one does not exist.

          • 2. Re: Create Purchase Order(s) from a Bill of Material
            Badam

            Thanks for the feedback, here are the answers to your questions:

                  

            The Tables are as follows:

            Do you need/want each PO to be linked to a specific item or could a PO combine needed parts for more than one BOM as long as they are from the same vendor?

            They do not need be linked to a specific item, combing needed parts for more than one BOM would be fine.

            Are you assembling just one item or do you need to multiply the part quantities specified in the BOM by the number of Items you plan to manufacture/assemble?

            We are (at present) ordering/assembling one unit at a time, however moving forward that may not always be the case. So at this point the BOM already has the total quantity needed to build one unit.

            Also seems like this may be more complex than currently illustrated here. Don't you keep any Parts in stock? Do you really order all parts before you assemble a given Item? If you have an inventory of parts on hand, the script needed to generate your POs would also need to check your parts inventory to determine what parts and quantities should be ordered.

            Although we do just in time ordering, and order what we need, the reality is that we may end up with some inventory so that should incorporate checking stock as well. 

            • 3. Re: Create Purchase Order(s) from a Bill of Material
              philmodjunk

                   In your screen shot, the link from Parts LIst to Purchase Order would only make sense if you PO is for a single part rather than a list of multiple parts.

                   There are several variations of the basic method that could be implemented here. I'll spell out one such approach and you can then see if it works for you or if you need to make some adjustments to it. I'm also going to assume that you are 100% "Kanban" and thus have 0 stock levels on hand for this first take on the process.

                   I am also assuming that you have only one Vendor for any given part. This is often not the case in the Manufacturing world.

                   Add a status field to Purchase Order. Set it to auto-enter "New". When you are ready to transmit a PO to the vendor, you'll need to change this field's value to something else. Our script will look for a "new" Purchase Order record for a given PO in order to add more PO LInes records to it. If it does not find one, it will create one.

                   Now add number  a field in Products, called SelectedVendorID, this field can be a global field. Add a calculation field, constNew with "New" as it's calculation and Text specified as it's return type.

                   Link a new occurrence of Purchase Orders, Purchase Orders|New and Link it to Products like this:

                   Products::SelectedVendorID = Purchase Orders|New::Vendor ID AND
                   Products::constNew = Purchase Orders|New::Status

                   Enable "Allow creation..." for this new TO.

                   #This script should be run from a layout based on Products It will generate or update PO's to list all BOM parts on PO's specific to that Part's vendor
                   #Gather the data needed for the POs in lists of values in variables
                   Set Variable [$PartList ; value: List ( BOM::Part_ID ) ]
                   Set Variable [$QtyList ; value: List ( BOM::Quantity ) ]
                   Set Variable [$VendorList ; Value: List ( Parts List::Vendor ID ) ]
                   Loop
                      Set Variable [$K ; value: $K + 1 ]
                      Exit Loop If [$K > ValueCount ( $PartList ) ]
                      Set Field [Products::SelectedVendorID ; GetValue ( $VendorList ; $K ) ]
                      Commit Records/Request[]
                      Set Field [ PurchaseOrders|New::Vendor ID ; Products::SelectedVendorID ] ---> this step creates a new Purchase Order if  "new" one does not exist
                      Set Variable [$POID ; value: PurchaseOrders|New::PO Number ]
                     
              Go To Layout [ "PO Lines" (PO Lines ) ]
                      New Record/Request
                      Set Field [PO Lines::PO Number ; $POID ]
                      Set Field [PO Lines::Part_ID ; GetValue ( $PartsList ; $K ) ]
                      Set FIeld [PO Lines::PO Quantity ; GetValue ( $QtyList ; $K ) ]
                      Go to Layout [Original Layout]
                   End Loop

                   The script step shown in Blue is crucial. It relies on the new occurrence of Purchase Order and is used to create a new Purchase Order record if one does not already exist. If a "new" Purchase Order for the specified vendor already exists, this step does nothing and the rest of the script simply adds more PO Lines records and links them to it. It's very important that Status be set to auto-enter "New" and that you change the value in that field once you are ready to send the PO to the vendor or this script will not work.

                   This is just a "get you started" script example. In addition to the issue of possibly having multiple vendors or checking available inventory before ordering, If you run this script from several different Products records before sending out PO's, you can get the same Part listed twice on the same PO--each from a different product's BOM. There are ways to combine quantities such that you don't get such multiple entries on the same PO. A "clean up" script can loop through the set of PO Lines records to combine them, or you can add an occurrence of PO Lines that matches to Products by PO Number AND Part Number and modify the above script to use it to avoid generating the entries with duplicate part numbers in the first place.
                    

              • 4. Re: Create Purchase Order(s) from a Bill of Material
                Badam

                     That Looks really good, so to be sure I understand in terms of the link from Parts List to Purchase Order can just be eliminated?

                Also when I run the script on the step that is:

                Set Variable [$VendorList ; Value: List ( Parts List::Vendor ID ) ]

                When I run Data Watch that list does not look right, We are looking for a list of vendor ID's that correlates to the BOM lines......this list seems to be a strange order possibly formed directly from parts list not the BOM?

                The other two variable lists ($PartsList and $QtyList)  are in the same order as the they appear on the BOM

                Any Thoughts??

                • 5. Re: Create Purchase Order(s) from a Bill of Material
                  philmodjunk
                       

                            so to be sure I understand in terms of the link from Parts List to Purchase Order can just be eliminated?

                       All I can say is that I see no reason for that relationship and it is not one used by the script that I am suggesting.

                       

                  Set Variable [$VendorList ; Value: List ( Parts List::Vendor ID ) ]

                       

                  When I run Data Watch that list does not look right, We are looking for a list of vendor ID's that correlates to the BOM lines......this list seems to be a strange order possibly formed directly from parts list not the BOM?

                       This data does indeed come from Parts List. I'm trying to avoid extra switches back and forth between layouts here, but I may be assuming details that aren't actually true--a hazard one runs when suggesting a script without testing every detail and I certainly did not have a DB on which to test this script. You should see one Vendor ID for each part in the BOM. But I am now wondering if they will be in the correct order. If you can tell that they are not in the correct order when you check them in the Data Viewer, there are two options that you can try:

                       Define a calculation field in the BOM with Parts List::Vendor ID as the sole term in it's calculation so that it just copies this value from the related parts record. Then use the List function to list this calculation field instead of the one in Parts List.

                       Use this script step:

                       Go To Related Record [Show only related records; From table: BOM; Using layout: "BOM" (BOM) ]
                  Set Variable [$VendorList ; Value: List ( Parts List::Vendor ID ) ]
                       Go to Layout [orginal layout]

                        

                       Since FileMaker Scripts can trip script triggers when the script changes layouts, I am trying, as much as possible, to suggest scripts that avoid switching layouts so as to avoid this possible complication.

                  • 6. Re: Create Purchase Order(s) from a Bill of Material
                    Badam

                         Phil,

                         Thanks for your reply, I can only imagine how difficult it must be to spell out these scripts went you are only going by my description!! thanks so much for all of your time on this!

                         I Defined the calculation field in the BOM with Parts List::Vendor ID and made it so that it just copies this value from the related parts record. Then used the List function to list this calculation field instead of the one in Parts List and it works perfectly! 

                    For anyone else reading this post I am including the final script below, ( there was a very minor typo in the original)

                              #This script should be run from a layout based on Products It will generate or update PO's to list all BOM parts on PO's specific to that Part's vendor
                              #Gather the data needed for the POs in lists of values in variables
                              Set Variable [$PartsList ; value: List ( BOM::Part_ID ) ]
                              Set Variable [$QtyList ; value: List ( BOM::Quantity ) ]
                              Set Variable [$VendorList ; Value: List ( BOM::Vendor ID ) ]
                              Loop
                                 Set Variable [$K ; value: $K + 1 ]
                                 Exit Loop If [$K > ValueCount ( $PartsList ) ]
                                 Set Field [Products::SelectedVendorID ; GetValue ( $VendorList ; $K ) ]
                                 Commit Records/Request[]
                                 Set Field [ PurchaseOrders|New::Vendor ID ; Products::SelectedVendorID ] ---> this step creates a new Purchase Order if  "new" one does not exist
                                 Set Variable [$POID ; value: PurchaseOrders|New::PO Number ]
                                 Go To Layout [ "PO Lines" (PO Lines ) ]
                                 New Record/Request
                                 Set Field [PO Lines::PO Number ; $POID ]
                                 Set Field [PO Lines::Part_ID ; GetValue ( $PartsList ; $K ) ]
                                 Set Field [PO Lines::PO Quantity ; GetValue ( $QtyList ; $K ) ]
                                 Go to Layout [Original Layout]
                              End Loop

                         And the relationships are as follows:

                          

                    • 7. Re: Create Purchase Order(s) from a Bill of Material
                      Badam

                           Phil,

                           I am back at this after a long break. The above script works great to a point but it only creates the PO Lines but there is not a step in there (unless I am misunderstanding) that creates a new PO within the Purchase Order table….. So I need to add a step where the script looks at all “New” Purchase Orders created so far to see if there is already a PO for that vendor, and if not then create an additional PO to the appropriate Vendor, since the BOM may contain multiple items from the same vendor.

                           I think this would be some type of an if statement that would look at the vendor ID and see if there was already a “New” PO created for that vendor, if so add to that PO and if not create an additional PO. Once that has happened then the PO lines are created by the current script.