By using this website, you agree to our Terms of Use (click here)
Struggling with this one. We are using a daily scheduled business event with this GI to update the last cost field on stock items. We use FIFO and need to update that stock item field with the first (oldest) cost layer.
I used some creative engineering with the joins of the same table (Thanks Tim?).
We use the InCostStatus to InCostStatusPrev relationship to ensure we are getting the first cost of the FIFO layer. We pull all of the costs for the FIFO tier and get the item cost by using TotalCost / QtyOnHand - this is the last cost we use. We also group by InventoryID otherwise it duplicates the line for each cost layer. When we do that the [InItem.lastCost] field adds the last cost to itself for each line so $10 turns into $30 if there are 3 cost layers.
What we want to do is limit the GI to only update if the [TotalCost / QtyOnHand] is different than what the [InItem.LastCost] field is so we aren't importing 2,000 items every night when it isn't necessary.
I've had no luck in doing so. Any help?
GI is attached:
I think I have it. I forgot to round the 2nd condition for LastCost by 4 digits so it was not matching. The math was going out to 6 or 7 digits so it obviously wasn't equal to the Stock items last cost which was rounded to 4 digits.