13 Replies Latest reply on Sep 6, 2016 4:53 AM by globe11123

    Despatch - Qty left in balance.

    globe11123

      Hi,

       

      This is probably going to be more of a database structure question than scripts.

       

      I want to create like a floating value which will represent the balance of items which need to be despatched or are left over from a previous despatch.

       

      I have already set the system up so I can despatch part of an order or full.

       

      On a part despatch order you need to enter a quantity of how many are going out on that particular note. This then will update a balance which will be taken into account when the next despatch note is created. Showing how many you need to despatch.

       

      I use the Despatch_Notes table to hold account level information. All of the products are sent to Despatch_Details maybe a join table is needed to be able to have a balance?

       

      estet.pngTable Structure

       

      esteset.png

      Layout view.

       

      Ordered is a value that is copied over from the Order_Details table. Despatched is a value that the user will have to type in to affect balance which will be used across any despatch notes related to this specific order. Currently Balance is just a calculation (Ordered - Despatched)

       

      Sorry if Im abit vague on my explanation.

        • 1. Re: Despatch - Qty left in balance.
          philmodjunk

          On which Table Occurrence (box in the relationships graph) is the layout shown based? (What shows as "table:" in the status tool bar when you enter layout mode?)

           

          And on which TO is your portal based? (Check name at bottom of portal when in layout mode and compare to your graph)

          • 2. Re: Despatch - Qty left in balance.
            globe11123

            Despatch_Notes is the layout and the portal is Despatch_Details

            • 3. Re: Despatch - Qty left in balance.
              philipHPG

              You could create a calculation field in Order_Details: Ordered - Sum ( Despatch_Details::Quantity)

               

              Alternatively you could create a self-join table occurrence of Despatch_Details (call it Despatch_Details_siblings for example) that is connected by OrderIdFK (assuming the OrderIdFK is correctly transferred from Despatch_Notes to Despatch_Details) and then add a calculation field to Despatch_Details: Quantity_to_Dispatch - Sum ( Despatch_Details_siblings::Quantity ). I assume that Quantity_to_Dispatch (I like how the 'i' spelling showed up here) is the field that has the Ordered value from Order_Details.

               

              Both of these possibilities will result in unstored calculations that could cause slowdowns. The alternative is to have a Despatched value in Order_Details that is updated through a script trigger every time an order is despatched.

               

              A few thoughts, hopefully they are helpful.

              • 4. Re: Despatch - Qty left in balance.
                globe11123

                balan ce.png

                Just looking at this again.

                 

                I am only dispatching line by line rather than looking at a total quantity of dispatch. The balance works fine ( Despatch_Details::Quantity - Quantity_To_Dispatch ) I had to change it from sum as it was adding up all the lines.

                 

                Only thing is that because we might do two separate despatches therefore Prev. Desp'd would probably be the place for the despatched quantity to go which then would update the balance.

                 

                Although I'm not sure how to achieve this as of yet.

                 

                It would be good for somehow the value of Despatched to be carried across to Prev. Desp'd after each seperate despatch this would probably work

                • 5. Re: Despatch - Qty left in balance.
                  philipHPG

                  The recommended approach for something like this is to use a script that will update a Despatched amount in the Order Details table (my third suggestion), rather than using an unstored calculation in the Despatch_Details.

                   

                  However, if you really want to calculate this at the Despatch level with a different Previously Despatched value for each Despatch line item, you will want to follow my second recommendation (creating a self join relationship). Since you're now indicating that you want the "Previous" amount (and not a total amount), you'll need to a second condition to your self-join relationship to ensure it doesn't match with itself. If your Despatch_IDFK is a serial number you could add: Despatch_Details::Despatch_IDFK > Despatch_Details_siblings::Despatch_IDFK to the self-join relationship. (If you're not using a serial ID then it becomes a little more complicated.)

                  1 of 1 people found this helpful
                  • 6. Re: Despatch - Qty left in balance.
                    globe11123

                    I have a script for despatching which generates a despatch note and updates the status. I could add something to this script as it would be done at the end of the process.

                     

                    I can see where you are coming from though because all you would need to do is find the correct line that is associated with the despatch details and update the orderdetails despatched value or something.

                     

                    How would I be able to update OrderDetails from Despatch_Details as they are two separate tables?

                    • 7. Re: Despatch - Qty left in balance.
                      philipHPG

                      I assume the OrderDetails and Despatch_Notes table occurrences are related through the OrderID (PK/FK pair). As long as the table occurrences are related you can update fields from the OrderDetails table through that relationship. So even though your layout is based on the Despatch_Notes table occurrence, you automatically have access to the OrderDetails table occurrence based on the relationship. (Likewise with AccountDespatch, Despatch_DeliveryAddress, Despatch_DA, Courier_Despatch and others).

                       

                      So in your script you would just use a Set Field [ OrderDetails::Despatched ; OrderDetails::Despatched + Despatch_Details::Quantity ]

                      • 8. Re: Despatch - Qty left in balance.
                        globe11123

                        Would I have to loop through the records? As that will only do the first line of the products.

                        • 9. Re: Despatch - Qty left in balance.
                          philipHPG

                          Ah. So you likely have an Order_Notes/Order_Details distinction similar to the Despatch_Notes/Despatch_Details distinction (the Order part of your table relationship graph is not shown on your screenshot)?

                           

                          In this case you will need to create a new table occurrence of Order_Details and create a relationship between Order_Details and Despatch_Details based on the OrderIDFK and ProductIDFK. Then you can update the field in that table occurrence and each Despatch_Details record should reference the corresponding Order_Details.

                          • 10. Re: Despatch - Qty left in balance.
                            globe11123

                            Correct sorry i should have included all the TO but there's so many haha.

                             

                            Its still only doing the one record? I've created another TO and updated the line in the script.

                             

                            OrderDetails_Despatch is the occurance of OrderDetails.

                             

                            Screenshot.png

                             

                            Script

                             

                            code.png

                            • 11. Re: Despatch - Qty left in balance.
                              philipHPG

                              Yes, you will still have to loop through the products and update the corresponding OrderDetails_Despatch records individually. Since you mentioned there is a Despatch_Details portal on the layout, you can loop through the portal rows and update through that. I would suggest doing that perhaps around line 17 or below.

                              • 12. Re: Despatch - Qty left in balance.
                                globe11123

                                Got it!

                                 

                                Thanks a lot for your time and patience.

                                 

                                Finally have a decent despatch model!!

                                • 13. Re: Despatch - Qty left in balance.
                                  globe11123

                                  Edit: Solved.