1 2 Previous Next 24 Replies Latest reply on Feb 7, 2014 1:39 AM by margagb

    Sales Back Order Script - Help needed



      Sales Back Order Script - Help needed



           As I am no expert in scripting I am asking for some help. I'd like to create a script to process back orders, i.e. there are open line items on a sales order.

           For example: Order was placed for a quantity of 100. Only 20 were shipped. How do I create a script so that I can ship the remaining quantity of 80. I have set up ordered qty, received qty and back order qty in the line items table. I also have identified a line item status field. Apart from this scenario it is also possible that there will be multiple shipments against one line item, in other words it is possible that the back order quantity of 80 will require another few shipments.

           Any help how I go about scripting this will be highly appreciated. Thanks so much.

        • 1. Re: Sales Back Order Script - Help needed

               Before you can script, you need to figure out the data model you are going to use to manage multiple shipments to fulfill a single order.

               What tables and relationships do you have in place now?


               is the basic starting point used in most such databases to manage taking orders and then you'd link in other tables to manage the shipping process.

          • 2. Re: Sales Back Order Script - Help needed

                 Thanks for your quick reply and your help. Much appreciated. I have the following data model… see uploaded image.

            • 3. Re: Sales Back Order Script - Help needed

                   If you have a back order situation for a given order, do you intend to create two records in DELIVERY linked to ORDER?

                   If so, you need a Delivery_DELIVERYLINEITEMS table to list the items from LINEITEM that are to be delivered in that order. Since a given line item might be split between two or more deliveries, you'll need to use the records in LINEITEM to generate the list of items delivered for each delivery but then modify them to get the specific items and quantities delivered in each order.

                   That's the basic concept. Make sense?

                   let me know and then we can move to a description of how you might set that up so that there is a minimum of user input needed to handle the back orders.


              • 4. Re: Sales Back Order Script - Help needed

                     Yes, it makes sense and that's how it should be.. a minimum user input would be very nice. 

                • 5. Re: Sales Back Order Script - Help needed

                       Define your new DELIVERYLINEITEM table and link it to Delivery by _kp_ReceiptID. On your Delivery layout put two portals: One to this new table and one to delivery_order_LINEITEM. For starters, what you can do, is put a button in the portal row of this second portal that uses the data in the clicked portal row to create a record in the DELIVERYLINEITEM portal. You can then edit the Qty field in DELIVERYLINEITEM to show the specific amount delivered.

                       Set Variable [$ItemID ; value: delivery_order_LINEITEM::_kf_ProductID ]
                       Set Variable [$Qty ; value: delivery_order_LINEITEM::Qty  ]
                       Set Variable [$DeliveryID ; value: DELIVERY::_kp_ReceiptID ]
                       Freeze Window
                       Go to Layout [ "DELIVERYLINEITEM" (DELIVERYLINEITEM)]
                       New Record/Request
                       Set field [ DELIVERYLINEITEM::_kf_ReceiptID ; $DeliveryID ]
                       Set Field [ DELIVERYLINEITEM::Qty ; $Qty ]
                       Set Field [ DELIVERYLINEITEM::_kf_ProductID ; $ItemID ]
                       Go to Layout [ Original Layout ]

                       See if you can get this to work first. Additional enhancements, such as a "ship All" button or filtering the LINEITEM portal to only list incompletely shipped items can also be set up.

                  • 6. Re: Sales Back Order Script - Help needed

                         Thanks so much. I will start on this and see if I can get it to work… will update you later, i.e. tomorrow as I am in the Netherlands and it is 7:00 PM here already. Thx again.

                    • 7. Re: Sales Back Order Script - Help needed

                           Yes, this works so far… How do I continue?

                      • 8. Re: Sales Back Order Script - Help needed

                             You must be staying up very late in your time zone. I know the feeling. You get something working and you hate to quit messing with it...wink

                             A simple additional feature to add is a "ship all" button to click when there are no back orders. Such a script would use steps very similar to what I have already posted, but in a loop so that all the lineitem data is copied over instead of just a selected line item.

                             Set Variable [ $ItemList ; value: List ( delivery_order_LINEITEM::_kf_ProductID ) ]
                             Set Variable [ $QtyList ; value: List ( delivery_order_LINEITEM::Qty ) ]
                             Set Variable [$DeliveryID ; value: DELIVERY::_kp_ReceiptID ]
                             Go to Layout [ "DELIVERYLINEITEM" (DELIVERYLINEITEM)]
                             Freeze Window
                                Set Variable [$K ; Value: $K + 1 ]
                                Exit Loop If [ $K > ValueCount ( $ItemList ) ]
                                New Record/Request
                                Set field [ DELIVERYLINEITEM::_kf_ReceiptID ; $DeliveryID ]
                                Set Field [ DELIVERYLINEITEM::Qty ; GetValue ( $QtyList ; $K ) ]
                                Set Field [ DELIVERYLINEITEM::_kf_ProductID ; GetValue ( $ItemList ; $K ) ]
                             End Loop
                             Go to Layout [ Original Layout ]

                        • 9. Re: Sales Back Order Script - Help needed

                               Phil, Thanks again. This now works fine. I can copy all the order line item data into the new delivery line item record. However, the next problem I run into is when I do not ship the entire delivery line item quantity, but part of it. In other words, it is a partial delivery from a partial delivery.

                               Example: Original order was for a quantity of 100. Only 50 were delivered. So there is a back order for 50. I now want to deliver a quantity of 25 against the 50 that is still on back order, i.e. the delivery line item qty. I can change the default quantity of 50 into 25, but where do I store the remaining quantity of 25? 

                               I really appreciate your help in this and I am learning a great deal. So I can't thank you enough.

                          • 10. Re: Sales Back Order Script - Help needed

                                 Using your current set up. You could click the "ship all button" to list all items for delivery in the second portal and then edit the Qty fields in that portal for any items that cannot be completely shipped to show that a smaller Qty will be shipped than what was ordered.

                                 My original plan was to build on this with you to add in the ability to see the total qty shipped from previous deliveries and to filter out records from the LineItem portal when that item has previously been fully shipped.

                                 But overnight, I had a brainstorm--recalling something I helped another person set up. This produces a much simpler more straight forward process but it doesn't use the two scripts I just had you create. blush

                                 Imagine a layout with just one portal that will list the items and quantities to be shipped from the order linked to this Delivery record. In that portal, you see the following fields:

                                 Product ID | Product Description | Qty Ordered | Qty Shipped | Qty This Delivery | Back Order Qty

                                 To set up your order, you just enter quantities into the Qty This Delivery field and the Qty Shipped and Back Order Qty fields automatically update. Scripts can still be used to "ship All" but now they'll do that by updating the Qty This Delivery field to a value that will cause the Back Order Qty to calculate a zero quantity.

                                 Does this sound like a better user interface?

                                 It requires these relationships:

                                 Enable "Allow creation of records via this relationship" for delivery_order_lineitem_DELIVERYLINEITEM|thisdelivery

                                 match fields:
                                 delivery_order_LINEITEM::_kf_orderid = delivery_order_lineitem_DELIVERYLINEITEM|thisorder::_kf_orderid

                                 delivery_order_LINEITEM::_kf_orderid = delivery_order_lineitem_DELIVERYLINEITEM|thisdelivery::_kf_orderid AND
                                 delivery_order_LINEITEM::gReceiptID = delivery_order_lineitem_DELIVERYLINEITEM|thisdelivery::_kf_receiptid

                                 Qty Shipped is defined as a calculation field in Lineitems with delivery_order_LINEITEM specified as the "context table" in the drop down found in the top of the Specify Calculation Dialog. It would have this expression:
                                 Sum ( delivery_order_lineitem_DELIVERYLINEITEM|thisorder::Qty This Delivery ) + 0 with the "do not evaluate if all referenced fields are empty" check box cleared.

                                 Back Order Qty would be defined in Lineitem as Qty - Qty Shipped

                                 Qty This Delivery would be defined as a number field in deliverylineitem.

                                 gReceiptID would be a global number field defined in lineitem. It will need to be automatically updated with this script set to be performed by the OnRecordLoad trigger on your Delivery layout. (It's set in Layout Setup...)

                                 The script: Set Field [delivery_order_LINEITEM::gReceiptid ; DELIVERY::_kp_receiptid ]

                                 Your portal would be a portal to delivery_order_LINEITEM placed on a DELIVERY based layout. You'd add Qty This Delivery from the delivery_order_lineitem_DELIVERYLINEITEM|thisdelivery table occurrence. When you enter a value in this field the first time, the "Allow creation..." option enables FileMaker to automatically create a new related record in deliverylineitem with the two match fields automatically updated to link it to the correct records in your database.

                                 Apologies for leading you on a bit of a wild goose chase yesterday, but this looks like an approach that is so much nicer for the user that I cannot keep from posting it as the next step to take here. See how far you can get with this and then post back with what happens. Once you can manually enter a value in Qty This Delivery, we can look at scripts to speed up the process so that you only need edit this field manually for back ordered items.

                            • 11. Re: Sales Back Order Script - Help needed

                                   I'll try this out this evening… and will update you later.Thx.

                              • 12. Re: Sales Back Order Script - Help needed

                                     Phil, This works perfectly if it is just one line item in the order. When I create an order with multiple line items, as soon as I enter Qty This Delivery in the first line item, it updates all the other line items with the same quantity… I guess we'll need another script here. Thx.


                                • 13. Re: Sales Back Order Script - Help needed

                                       No, I think you have something not set up correctly either with your layout design or the relationships. What you report indicates that your field is not from a correctly linked occurrence of DeliveryLineItems.

                                  • 14. Re: Sales Back Order Script - Help needed

                                         Here a new cleaned up data model … I noticed that the naming was a bid weird and I attached the PRODCT table. 

                                         I am in the middle of checking the layout design and the relationships but I can't find anything wrong so far…. 

                                    1 2 Previous Next