6 Replies Latest reply on Dec 16, 2013 2:31 PM by philmodjunk

    Where does the SKU Number fit???



      Where does the SKU Number fit???


                I work on a television show and deal with equipment management. I have a lot of equipment checked out from the studio and working on different stages and sets.  I'm trying to get a handle on how much of what I have and where it currently is located and when it's due back for return. 
                I've been working hard with Filemaker trying to get keep track of it and for the most part its been successful. I had never heard of Filemaker until 4 months ago, so I'm sorry if these questions seem simple...
                With that said, I was doing well keeping track of everything, until I tried to return a light to the rental house that was checked out on another set.  I have (40) 20K lights checked out.  It's the same item except the rental house has each light barcoded (SKU), giving it a unique identifier. 
                I had no idea where the specific light was and I was on the search for it's SKU number. 
                I now need to add a SKU field to my database and assign it to a specific light and location. 
                The problem is: I'm trying to figure out where exactly a SKU number will fit in my current database.  I've looked at other examples in the Filemaker Starter Solution, but I have my concerns with how they are set up.  
                Here is my first example: http://d.pr/i/FQzx
                My problem with Example 1 is that I don't really know where I can add the SKU number? 
                I asked this question in the forums some time ago and was told to place it in the Equipment table.  That makes sense, however, how will my "Accessory" table work now? 
                Here is my 2nd example: http://d.pr/i/xnvK
                I should also note that I have already imported the Equipment records from an existing database and I have over 4,000 items (Equipment) in my table.  Out of the 4,000 records in that table, I have only (1) record for a 20K. This table is really just the items information (make; model; wattage; weight, etc), but no unique fields for any specific item (SKU, Manufacturer Serial,etc) 
                It seems that if I took the suggested method of adding the SKU field into the Equipment table then I would keep adding items to that list. This is something I'd like to avoid so I can keep the original information clean (item information) and carry this database to my next show, without the SKU numbers of items that are attached to this current show. 
                I hope this makes sense and I'm sorry to keep bring this up, but I feel like I'm really starting to run around in circles.  
                Thanks for the help. 


        • 1. Re: Where does the SKU Number fit???

               You have two different ways that you need to be able to work with your equipment: by type: ( I need 5 20K lights at location 3 for the following dates...) and by the specific item. The 20K light with barcode: ab12345gh is currently in Studio 1 and will be needed there for the following dates...)

               Thus, you need two tables, one with a single record for the type (one record for 20K light) and another table with one record for each physical item in your inventory. This second table is where you would have a field for that barcode. The first table is what you would use for setting up your equipment lists. The two tables would be linked by type so that from a record in the first list, you can see how many items of that type you have in your inventory and what barcodes are on each.

          • 2. Re: Where does the SKU Number fit???

                 Thanks again for the advice, Phil.


                 That makes sense.


            • 3. Re: Where does the SKU Number fit???
                   Phil, would you mind taking a look at this and see if I'm on the correct path:  http://d.pr/i/ZmU3
                   My thoughts are that when I create an Orders record, I can add the equipment to the order via portal into OrderLineItems.
                   I create a value list based off the EquipmentList table with the values of EquipmentIDpk and Name.
                   When I select 20K from the value list it populates OrderLineItems:EquipmentIDfk with the appropriate value and enter my QTY.  I would like to say that QTY won't be the total number of items I have, but the total number of items I ordered for the package "Stage 23"
                   I would use my temp. named "SomeMultiKey" to add any new serial numbers or select an existing Inventory record. 
                   I'm curious about my "SomeMultiKey" field... I believe I need to create a value list based off the serial numbers from Inventory. 
                   I would like to select 20K and then have "SomeMultiKey" display only serial numbers from 20K's.  I'm fairly positive I need to create a "Conditional Value List".
                   I'm not really asking how to do that, I'll try my best to figure it out and go from there.  I'm more concerned with whether this is the correct approach?  
                   My thoughts are that I could add Serial numbers to the items that have serial numbers and leave "SomeMultiKey" blank for items that don't have serial numbers.  
              • 4. Re: Where does the SKU Number fit???

                     Wouldn't it be simpler to enter/scan ID's off of the equipment as you pull them from storage as a way to log the specific items used to fulfill the "order"?

                • 5. Re: Where does the SKU Number fit???
                       That would be nice, but unfortunately I'm starting this project late. Most of the gear is checked out. I do have my paperwork that I can refer to, though.  
                       Example: http://d.pr/i/42kw
                       I would add (4) 20K's to my Inventory table with the following serial numbers:
                       I would just have to manually type them in, for now.  
                       Many items don't have any barcode or serial numbers and only have records in "Equipment", which is why I thought using the EquipmentIDfk to add gear to the list and, if needed, assign the serial numbers with the proper OrderID, so I can find the items whereabouts.
                       I was worried that if I joined only the EquipmentList table to OrderLineItems, I would not be able to assign the specific item record from Inventory to the Order record.  
                       on the flip side...
                       I was worried that if I joined only the Inventory table to OrderLineItems, I would not be able to assign additional "non-barcoded" item records from EquipmentList to the Order record.  
                       I hope this makes sense, I had a very early morning and I'm not really thinking very clearly...
                       Regardless, thanks for the help Phil
                  • 6. Re: Where does the SKU Number fit???

                         For Clarity, I'm referring to Equipment List as the table with one record for each type of equipment and Inventory as the table with one record for each and every individual item you have to track in this system.


                              Many items don't have any barcode or serial numbers and only have records in "Equipment"

                         Then you will not be able to track those specific items from your database. I would suggest setting up bar codes and putting "asset tags" on all the equipment that you don't rent from another source so that you can do this in the future. In the meantime, you could use the ID value that identifies the equipment type in place of a serial number that you don't have. The draw back to that is that you then have to allow duplicate values in a field that really shouldn't be set up to permit that. I would instead use an auto-entered ID in your inventory table. If you later issue bar codes or engrave/stamp/stencil an asset number on these items, you can use that auto-entered ID as the number to put on the equipment.

                         Whether you log these ID's as equipment is checked out or after the fact from paper records, it's the same process, logging each specific item by a unique id--whether marked on the item or not in order to log each item as being "out" to a specific location and/or organization and then logging them back in when returned.

                         PS. As protection against possible fraud, I'd label all items with unique ID's and log them in the system just as soon as possible.