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)
Despatch_Notes is the layout and the portal is Despatch_Details
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.
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
1 of 1 people found this helpful
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.)
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?
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 ]
Would I have to loop through the records? As that will only do the first line of the products.
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.
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.
Thanks a lot for your time and patience.
Finally have a decent despatch model!!