1 2 Previous Next 15 Replies Latest reply on Jul 2, 2013 3:49 PM by philmodjunk

    Best way to set up a "parts" inventory?

    MeganMcFadden

      Title

      Best way to set up a "parts" inventory?

      Post

           I work for a distributorship that has an inventory of:

           sets (137 total and some of the same thing) these sets consist of items with : item number, description, and quantity.

           Each set may have similar items in it.

           I want to have a layout that will house the name of each set and its home & current location (they travel between different hospitals).

           I know i want to have a portal to the contents of each set on the layout for each individual set but i dont know how to set up the table for the contents. I have ready about a "joining" table but still dont understant what information i can put where.

           I also have a table that already has the an auto serial number, set name, home location, home representative. Can i link the set name to another able where i put the contents so i dont have to reenter all of it?

           I want the portal to look like this for each individual set (with a scroll bar)

                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                          Set Name                     Item Number                     Description                     Quantity
                          a                     1                     as                     2
                          a                     2                     df                     4
                          a                     3                     gh                     2
                          a                     4                     jk                     1

            

        • 1. Re: Best way to set up a "parts" inventory?
          philmodjunk

               I wouldn't use the set name, but rather a serial number unique to each set, but yes the basic concept can be done:

               Sets------<Parts

               Sets::__pkSetID = Parts::_fkSetID

               The data you show in your original post would, except for the set name, be individual records in Parts. Your Set name woudl be a field in Sets where it would be entered exactly once for each set and a portal on the sets layout can list each of the parts that make up that set.

               But if you have detailed info to record about each "part", you may need something more like this:

               Sets-----<Part_Assembly>-----Parts

               Sets::__pkSetID = Part_Assembly::_fkSetID
               Parts::__pkPartID = Part_Assembly::_fkPartID

               This second approach would allow you to record detailed info about any given part a single time and yet that part can be a member of any number of different sets--this is called a "many to many" relationship and also can be modified to support parts that are also sets in their own right.

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

          • 2. Re: Best way to set up a "parts" inventory?
            MeganMcFadden

                 I was able to add a serial id auto fill field after the fact to my table but i cant figure out how to make it a primary key...

            • 3. Re: Best way to set up a "parts" inventory?
              philmodjunk

                   In FileMaker, you don't "make it a primary key". There's no such designation--as exists in some other database systems. You just use it as such in your relationships when you link to other tables in your database.

              • 4. Re: Best way to set up a "parts" inventory?
                MeganMcFadden

                     I have figured out i need the following tables:

                     Sets ( set id, set name, location, manufacturer etc)

                     Usage ( date, borrower, shipping mehtod, new location, reason needed, anticipated date of return, actual date of return)

                     Contents (item number, description, *quantity*)

                      

                     This is where i get stuck. For the contents table, i know i will need a many to many relationship because one part can be in many sets. However, do i put in a quantity field and leave them blank so when i have a portal on the set layout i can enter my own quantity. ( will that even work)

                     Or do i just have item number and description on the contents table. Then once i have a portal on the sets layout add my own field for a quantity and manually enter the quanity of each part per set layout? Will a portal allow me to do this or will the quantity be too disonnected from the portal to make sense?

                      

                     Thanks for the help so far!

                      

                • 5. Re: Best way to set up a "parts" inventory?
                  philmodjunk

                       The quantity field should be a field you add to the join table. That way one set can show that it has a quantity of 3 for item A and a different set can show a quantity of 4 for item A.

                       Here's a demo file on many to many relationships I keep around to show people trying to figure out many to many relatiionships: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                       It matches "contacts" to "events" in a many to many relationship, but the concepts are the same.

                  • 6. Re: Best way to set up a "parts" inventory?
                    MeganMcFadden

                         So i need to have:

                         Sets Table     Join Table     Contents Table

                         For the join table i would need to create a field that says set name and then pulls from the sets table the names through a many to many relationship?  And the same for any fields on the contents table that i want to bring together with the set name?

                         So i should actually have:

                         Set info table     Set Contents table    Parts table

                         and then my portal would display the set contents table with the name and individual parts/quantity displayed?

                          

                          

                    • 7. Re: Best way to set up a "parts" inventory?
                      philmodjunk

                           Basically right, but:

                           

                                For the join table i would need to create a field that says set name and then pulls from the sets table the names through a many to many relationship?  And the same for any fields on the contents table that i want to bring together with the set name?

                           Put the set name in the sets table, not the join table. Link records by a primary key field--an auto-entered serial number field instead of the name. The same should also be true for the "contents" table.

                      • 8. Re: Best way to set up a "parts" inventory?
                        MeganMcFadden

                             I  now have a Sets Table, Set Parts Table and my join table will be Set Assembly.

                             For the Set Asembly table I need to:

                             Make a set name field and link it to the set name field in the Sets Table by a many many relationship,

                             Make a item number field and link it to the item number field in the Set Parts Table by a one to many relationship

                             Make a description field and link it to the item number field in the Set Parts Table by a one to many relationship

                             Make a quantity field and fill it in for the individual sets on the Set Assembly Table.

                             Does this sound right?

                              

                        • 9. Re: Best way to set up a "parts" inventory?
                          philmodjunk

                               It does not. There should be no name field for the set in set assembly. There should be no description field in set parts assembly.

                               To repeat, the set name field should exist solely in the sets table. An ID number generated in sets should serve as the match field to a number field in set assembly. There is no need for a description field in set assembly as you already have this data recorded in Set Parts. You can add this field from set Parts to a portal row of a portal to set assembly, there's no need for an additional field.

                               But you do need to define the quantity field in set assembly.

                          • 10. Re: Best way to set up a "parts" inventory?
                            MeganMcFadden

                                 I have my portal set up and it is showing my join table (set assembly) when i enter a new record using the set id number it shows in the corresponding Set record.

                                 However, if in my join table i only have the set id number and the quantity, how do i get the portal on the layout to show: set ID, Item number, description, and quantity?

                            • 11. Re: Best way to set up a "parts" inventory?
                              philmodjunk

                                   Use the field tool to add those fields to your portal row from the table where they have been defined. You can find examples of this in the demo file that I shared in an earlier link.

                              • 12. Re: Best way to set up a "parts" inventory?
                                MeganMcFadden

                                     I have moved on to my usage table. I want the portal to my usage table to automatically add an antry to my "territory" field when a specific "rep" is selected.

                                     How do i set up an auto populated, if-then field?

                                • 13. Re: Best way to set up a "parts" inventory?
                                  philmodjunk

                                       I'm not sure that I fully understand your question and don't see a need for If-then, but I think you'll need a table of territories and reps that you can link in by the territory field.

                                       See this thread for a discussion of two ways to do that: Auto Fill

                                  • 14. Re: Best way to set up a "parts" inventory?
                                    MeganMcFadden

                                         Thanks, I got that part to work.

                                         My latest endeavor is to have a "current location" field on my Main Page Layout  that will update with each new record/transaction of loaning sets.

                                         I have a portal on my Layout to my usage table which has the field "new location". I also added a "current location" field to my Main Page Layout so you can instantly see where the set is without going through the records.

                                         I want the "current location" field on the Main page to auto update with the latest "new location" from my usage table.

                                         I looked at this discussion:

                                    Inventory check in check out (help)

                                         It seems like my check out date field on my usage table could work as an "out" field, and my "returned date" on my usage table could serve as an "in".

                                         I made a relationship between my "new location" field on the layout to the "new location" field on the usage table but once i returned from the edit screen NONE of my usage data was populating anymore. I had the "allow creation of record in this table" buttons checked for both tables.

                                         How do I get the "current location" field on the main layout to update with the newest location from my usage records. AND, how to I set a default value for when the set is "in" to be the "home location" (also a field on the main layout page). ??

                                    1 2 Previous Next