Perhaps you simply need to approach the problem from a slightly different angle. Your inventory transactions have a date field, correct?
The following calculation will extract the year from that date field:
Now you can enter find mode and type in a year to pull up all inventory transactions for a given year. If you need inventory on hand, enter < 2010 for example, to see YTD for 2010.
You can also refer to this year field in a relationship to compute totals on hand without having to find all the records in your inventory table first.
Define a global number field, gYear in your parts table.
Make a new table occurrence of your inventory table (I'm assuming you already have a relationship linking the two tables that we don't want to change) and call it InventoryByYear.
Relate your table occurrences:
PartsTable:: PartID = InventoryByYear:: PartID AND
PartsTable::gYear > InventoryByYear::cYear
With this relationship, you can use the sum function to compute the year to date inventory totals for each part. If you enter a different year in gYear, all the parts records will compute new totals based on the new year.
You have pointed out a glaring inadequacy in my solution. My inventory adjustments do not have a date attached.
I have been changing stock levels directly from my job card layout by a calculation that only adjusts quantity in inventory.
I think I know what you are describing by an 'inventory transaction' but not completely sure, is it a join table between inventory and jobs? I think I looked at something like that in one of the FM tutorials but I did not need to worry about changing prices of stock so I didn't go that way.
I think I better go back and investigate it as your solution looks like it will fit my needs perfectly.
Thankyou again for sharing your expertise on this forum, your continued help is appreciated.:smileyvery-happy:
There are a couple of threads here you can find that discuss an "inventory log". You should be able to pull them up under those key words if you use the advanced search link to look for them.
The basic idea is to structure your table so that it works like a check register. You have an "in" field where you log the reception of new stock, an "out" field were you log the sale or disposal of stock, a "balance" calculation field (in - out) and a running total summary field of the balance calculation. If you set this up right in a list or table view layout, you can group your inventory changes by Item and see the current total on hand by looking at the last transaction in that item's group.