5 Replies Latest reply on Feb 28, 2014 9:38 AM by philmodjunk

    items inventory

    CarlosAzevedo

      Title

      items inventory

      Post


           I am trying to create an inventory database for checking in/out multiple keys. Some keys have the same serial number followed by an individual tracking number.  I would like to be able to have some fields auto-populate (example: if I enter the tag number it's on it would populate the serial number and car it goes to) Is this something a novice can do i.e. me? I recently pickup the "missing manual" for Filemaker Pro 11, should i read through it before attempting this? I already have all the keys in an excel spreadsheet.

        • 1. Re: items inventory
          philmodjunk

               Are all keys uniquely labeled? (You've indicated that some keys have a particular label format...)

               Sounds like you need a table of keys, one record for each key and a table of people one record for each person to which a key might be checked out. The question next to answer is what besides tracking who has each key do you need to track about each key.
               Do you need to keep track of when it is checked out?
               Is there an expected date when the key is expected to be returned or are keys checked out for an indefinite length of time?
               Do you need to track the history of who had a particular key in the past?

               If each key has a unique identifier and you set up one record for each key and all you need to track is who currently has the key, you can do it with a simple one to many relationship between people and keys:

               People-----<Keys

               People::__pkPeopleID = Keys::_fkPeopleID

               If you need to track more info, then you may need a third table for recording that information.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: items inventory
            CarlosAzevedo

                 I'm sorry, there are up to 10 copies of each key (serial # - 1, serial # - 2, serial # - 3, etc) and multiple people may have the same key that is issued to them for an indefinite length of time.

                 Thank you for the quick reply I'm checking out the link you posted now.

            • 3. Re: items inventory
              philmodjunk

                   You may want an additional table where you have one record for each key serial number linked to the table I called "keys" in my earlier example. But this may or may not be necessary, it depends on what all you need to do with this information.

                   People----<KeyCopies>------KeySerialNumbers

              • 4. Re: items inventory
                CarlosAzevedo

                     Am I at least headed in the right direction?

                • 5. Re: items inventory
                  philmodjunk

                       It would be better to set up a primary key field in Keys and use it as the match field to a single foreign key field in People. That makes for a simpler relationship that is easier to manage.

                       Also, your current relationship assumes that every key only opens doors in a single building. If that's always the case, no problem. But if you have any master keys that open doors in more than one building, what you set up won't work--so that might be a second reason for using the fields that I have recommended.

                       Note also that if you need to track which keys open doors in a particular building, there may be a need for an additional table or two to record data on each building a single time for linking to the keys that open doors in them.

                       For similar reasons, you might even need a table of rooms, but all of this depends on what all you need to do with your database besides keep track of who has a given key.