Combining line items to a singe item on another table
I have an existing app that counts products coming and going on trucks. Right now the main tables are Checkout and Line items, each Checkout has many line items. A truck is either returning (product returned) or leaving (product sold) and a truck has different items. What I would like to do is create a sales order (existing table in another app) based on this information. It needs to be sorted by the day and similar items need to be grouped into one line item.
Truck 1 returns 2x Product A and 1x Product B
Truck 2 sold 4x Product A and 6x Product C
So the sales order would have 3 line items
Product A: 2
Product B: -1 (negative because more were returned than were sold that day)
Product C: 6
*if a product ends up having a QTY of 0 for the day then it won't show up on the Sales Order
Right now I have a report that displays the correct information. It still has multiple of each line item but the summary counts are the correct amounts and it is only showing for the day.
How can I move this information to an existing table (Sales Orders) and combine the line items (products) and get the correct QTY count? As well as have this done each morning for the previous day?
Truck 1 returns 2x Product A and 1x Product B
Truck 2 sold 4x Product A and 6x Product C
So the sales order would have 3 line items
Product A: 2
Product B: -1 (negative because more were returned than were sold that day)
Product C: 6
*if a product ends up having a QTY of 0 for the day then it won't show up on the Sales Order
Right now I have a report that displays the correct information. It still has multiple of each line item but the summary counts are the correct amounts and it is only showing for the day.
How can I move this information to an existing table (Sales Orders) and combine the line items (products) and get the correct QTY count? As well as have this done each morning for the previous day?
