3 Replies Latest reply on Feb 12, 2010 9:03 AM by philmodjunk

    Year to Date usage



      Year to Date usage


      Filemaker Pro 10, Windows XP & Vista, Hosted Network (6 users)


      I have a Parts Inventory table for the thousands of components we use. FM currently logs parts in and out keeping a running total.


      What I am trying to acheive is a Year to Date(YTD) Usage calculation that will reset only once on the year change (Company year begins 1st August).

      This would give a number of how many each year were used so future orders can be more accurate.


      I want it to show the YTD usage for the current year 'live', i.e. a calculation that changes each time we take from stock.

      I then need it to reset on the first day of each year and store the previous years total in a different field, possibly log of many previous years.


      The main problem I am having is the reset, I cannot base it on date as it may be opened more than one time on that date. I need it to automatically reset all stock items once only at the start of the year and reset all current YTD Usage to zero.


      Is there a calculation that can do this?

        • 1. Re: Year to Date usage

          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:


          Year (YourDateField)


          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.

          • 2. Re: Year to Date usage

            Thanks Phil,

            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:

            • 3. Re: Year to Date usage

              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.