3 Replies Latest reply on Mar 9, 2011 9:29 AM by philmodjunk

    New to inventory

    MichaelBailey

      Title

      New to inventory

      Post

      I'm looking to setup an inventory database for a bar/restaurant and i'm having some trouble creating a functional data entry list. I have a set list of items in stock and each week i need to take a count of the items. I could create a value list from a field that lists all the items and select them from a auto entry or drop down list, but when i'm entering the weekly inventory I want to be able to specify a week in the header and automatically generate a list of the items and quickly enter the amount in stock of each item. Is there a script I can create that will automatically generate a new field based on the date I enter in the header?

      I want to be able to specify the date once, and have the items on the left listed automatically so i can then enter the stock and tab between entry cells. When I set a new week I want to hide the "in stock" field from the prior weeks and insert a new blank field.

      Enter Date:  4/4/11                            (prev weeks hidden....)

      ITEM           |           IN STOCK      |     IN STOCK (prev)    |

       

       _______________________________________________

      Well Vodka   |           12                 |        15

      Abs Vodka    |           8                  |         12

      Well Rum     |           10                 |         14

      I figure this is a common desired application for filemaker users but I haven't really seen something similar in other posts. If you can help me out or direct me to a similar inquiry i'd appreciate it

      Thank You,

      Michael Bailey

      filemaker pro 11 for mac

        • 1. Re: New to inventory
          philmodjunk

          FileMaker cannot create a new field on the fly. It can, however create new records and enter data into them as they are created.

          A looping script can be set up that duplicates last weeks records, but moves the Instock counts to the instock Prev fields as it does so.

          You'd have a table with at least these fields:

          Item               Text
          Instock            Number
          Instock Prev     Number
          InvDate           Date
          gCurrentDate   Date, global storage enabled.

          You can perform a find to pull up last week's inventory, enter the new inventory date into gCurrentDate and then click a button to run this script:

          Go to record/request/page [first]
          Loop
             Duplicate Record/Request
             Set Field [yourTable::invDate ; Yourtable::gCurrentdate ]
             Set Field [YourTable::Instock Prev ; YourTable::Instock]
             Set Field [YourTable::Instock ; "" ]
             Omit Record
             Go To Record/Request/page [first]
             Omit Record
             Exit Loop If [Get ( FoundCount ) = 0 ]
          End Loop
          Enter Find Mode []
          Set Field [YourTable::Invdate ; YourTable::gCurrentDate]
          Perform Find[]

          In your layout, you can set this up with the InvDate in the header and the Item, In Stock and Instock Prev fields listed in the body with view as list specified for this layout to get a tabular format for recording your inventory data.

          • 2. Re: New to inventory
            MichaelBailey

            Thanks for your response!

            I understand that script will help to maintain the prior week's record, but what if I want to have the inventory from each week saved in a field so that I can track usage over a series of weeks?

            I also want to consider the possibility of creating 52 fields and naming them "week 1, week 2, etc...", then maybe I could have an "inventory display" field that uses a calculation to reference the date from a drop down calender to display the proper field. I know that I can use a weekofyear (date) calculation to get the week number and then i could have some sort of "case test" in the "inventory display" field that tests the weekofyear (date) field and then displays the corresponding inventory field for that week.

            For example, when I select 1/17/11 from a drop down calender, the "Inventory Display" field will show the "Week 3" field for all the inventory items.

            I'm not too familiar with the calculations and maybe someone can point me to one that is similar to the "Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )". In essence I want a calculation to look up the weekofyear (date) field and show the corresponding field, but I don't want to have to write out 52 test and result cases.

            Any recomendations are greatly appreciated!

            Michael Bailey

            • 3. Re: New to inventory
              philmodjunk

              Thanks for your response!

              I understand that script will help to maintain the prior week's record, but what if I want to have the inventory from each week saved in a field so that I can track usage over a series of weeks?

              The data for each week is saved in a different group of records. The step that moves the data from inStock to InStock Prev is just one simple way to show the previous inventory counts next to the newly entered data on the new records for comparison purposes.

              I also want to consider the possibility of creating 52 fields and naming them "week 1, week 2, etc...",

              Not a good idea. You should use a separate group of records for each week. using 52 fields like this will quickly prove very cumbersome and awkward to work with.

              I'm not too familiar with the calculations and maybe someone can point me to one that is similar to the "Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )". In essence I want a calculation to look up the weekofyear (date) field and show the corresponding field, but I don't want to have to write out 52 test and result cases.

              Which is a very good reason NOT to set up your tables with 52 such fields. Wink With separate records, you likely would need only one such calculation field or a small group of calculation field. You may want to research aggregate functions and summary fields in FileMaker help to see how you can pull together values computed from a group of individual recors such as the inventory counts for a given product over all 52 weeks.