Record Status and date transfer.
I currently have a total of three tables involved in my inventory tracking system. Purchases, Sales and SalesMix. What we do is purchase a wide variety of products, create our own mix and sell them as new mixed products. For the purchases the inventory tracking is as simple as it gets. All the information coming in can stay within the one record which includes important information such as grade of product, date and quantity and record status. The reason why record status is important is because I want voided records to be excluded from the inventory (obviously).
The issue/tricky part is in the sales. Because any given product is often comprised of a combination of various purchase products I had to build a sales table where all the main information lives and then I created a second table where the mixed products get listed in a portal. Quantity and grade live on the SalesMix records which effect the inventory directly but the date and record status live on the sales records and I had no choice but to build a script trigger that transfers any date or record status changes over to the associated SalesMix records. In theory that works perfectly fine but in practice for some odd reason every now and then (about .5-2% of the time) the date or record status doesn't transfer and I can't seem to figure out why. So when we pull up or inventory reports to compare against our accounting software I'm forced to go into the back end and find the ones that have different status' and dates.
I'm wondering if there is a better way then to use scripts to make this work. I tried designing a relationship that used the sales for the date but the grades from the SaleMix and could not make it work.
Any input or ideas would be greatly appreciated!