Not sure if the title is the best description. Here is what the client is asking…
We have a SalesOrders table, with a child SOitems (line items) table.
We also have a PackingList table, with a child PLitems (line items) table.
There is a 1 to many relationship between SalesOrders and PackingLists.
Depending on when individual items are completed, each SOitem may have its QtyOrdered broken across multiple PackingLists, with each item having a QtyShipped. The SOitems ID # is used to enter each line in a PackingList, so we can also relate to each shipped item from the SOitem. So far, so good.
Now, they want to update the SalesOrders::Status field to "Complete" whenever the QtyShipped = or > QtyOrdered, for all line items. The only problem is that the QtyShipped gets entered on the PackingList layout, so the fields in the SaleasOrders layouts don't automagicially update.
And the Status field can't be a calculation field - they need to be able to manually change the status at any time. They just want something to trigger the update when all items have shipped completely.
Hopefully I'm explaining that clearly. I'm sure there are a number of ways to solve this.
I have something kind of working now, but it feels clunky and it's bullet proof. I have a OnObjectSave script trigger on the PackingList PLitems portal QtyShipped field that does the following:
Set Field [PackingList_SALESORDERS::Status ; Case ( Sum (PackingList_SOITEMS::QtyRemaining) ≤ 0 ; "Complete" ; "OPEN" ) ]
Refresh Window [ Flush cached join results ]
It works, but in the off case that they ship more of an item than was ordered, and if the difference is more than the QtyOrdered of the other line items, it will produce a false "Complete" Status.
Thanks in advance.