11 Replies Latest reply on Nov 17, 2010 2:01 PM by philmodjunk

    Listing additional locations in an inventory

    anitaGillis

      Title

      Listing additional locations in an inventory

      Post

      Hello all,

      I am a beginner to Filemaker Pro and need help. I have an inventory database. It tracks the contents of 17 racks, each rack has 11 boxes, each box has multiple samples. I am trying to create a calculated field that will tell me where the same item is stored in all racks, boxes.

      Example. Item A is stored in Rack 1 Box 1, Rack 3 Box 2 and Rack 4 Box 4. When I pull up the record for Item A in Rack 1 Box 1, I want this new field to tell me that Item A is also in Rack 3 Box 2 and Rack 4 Box 4. Is this possible for Filemaker? Please help if you can.

      Regards

        • 1. Re: Listing additional locations in an inventory
          philmodjunk

          First a key question about your design.

          How are you currently recording the multiple locations where a given item is store?

          Ideally, you should have two tables. Table 1 lists each item in inventory. Table 2 should be a related item where a new record is created for each location where a given item is stored.

          That option enables you to display the existing locations in a portal or you can set up a calculation field with the List and substitute functions to get your horizontal list of existing locations.

          • 2. Re: Listing additional locations in an inventory
            anitaGillis

            Hi PhilModJunk,

            Thanks for replying. I have Table 1, it currently has 1000 records for all the items in the inventory.  If there are multiple locations for an item, they have a seperate record, for example:

            Item A: Rack 1 Box 1, is record 1

            Item A: Rack 3 Box , is record 2

            Item A: Rack 4 Box 4, is record 3

            I will start setting up Table 2. I'm sorry to ask, "you can set up a calculation field with the List and substitute functions to get your horizontal list of existing locations". Do you mean that I set the new field in Table 1, lets call it Field 1 to calculate the locations listed for Item A in Table 2? I'm not sure I understand what to do.

            • 3. Re: Listing additional locations in an inventory
              philmodjunk

              Well, what you have is close to table 2. What you don't have is table 1.

              Let's call the new table "Items" and the current table "Locations".

              Then you can relate them like this:

              Items::ItemID = Locations::ItemId

              Then you can define a calculation in Items as:

              Substitute ( List ( Locations::RackBoxField ) ; ¶ ; ", " )

              Rack and Box should probably be separate fields. If they are, you'll need to define RackBoxField as a calculation field in Locations to combine the Rack and Box information.

              • 4. Re: Listing additional locations in an inventory
                anitaGillis

                Thank you for the reply. I am trying to set this up. Rack and Box are seperate fields. So I've created a new field per  your suggestion, RackBoxField. The calculation is: RackBoxField= Rack and Box

                I'm a little stuck, I created the Items Table. I set the relationship of Items::VialID = Locations::VialID. So why can't I get the records in the Locations table for Item A to create a record in the Items table?

                • 5. Re: Listing additional locations in an inventory
                  Frinholp

                  Where you have joined the tables in your relationship graph, click on the box ( probably '=') and allow creation of records in this table via relationship underneath the items table.

                  Lee

                  • 6. Re: Listing additional locations in an inventory
                    philmodjunk

                    Your calculation should be something like Rack & " " & Box  (and is a logical operator and won't combine your two field values like you need here.)

                    Your items table won't load itself. You'll need to use Import records to create one record for each Item in your inventory.

                    Assuming that the field ItemID uniquely identifies each item, Open Manage | Database | Fields. Find ItemID in the Items table and double-click it. On the validation tab, select "unique values" and "validate always".

                    Now, go to your Items layout. Select  Import Records from the File menu, select the same file you have open as the source file, select the Locations table as the source table and import your records. The validation rule we specified will filter out duplicates and just create one record for item in your inventory.

                    • 7. Re: Listing additional locations in an inventory
                      anitaGillis

                      Okay, I seem to have missed something. 

                      Example: In the Items table,

                      Record 1 has Item A: Rack 1 Box 1

                      Record 2 has Item A: Rack 3 Box 1

                      Record 3 has Item A: Rack 4 Box 4

                      The new field, termed Additional Locations, now lists

                      Record 1: 1 1

                      Record 2: 3 1

                      Record 3: 4 4

                      The Additional Locations field calculation: Substitute ( List ( Locations::RackBoxField ) ; ¶ ; ", " ) , is yielding the same Rack/Box location that exists in the record. What I'm hoping to see is

                      Record 1: 3 1

                                      4 4

                      Record 2: 1 1

                                      4 4

                      Record 3: 1 1

                                      3 1

                      Is this possible?

                      • 8. Re: Listing additional locations in an inventory
                        philmodjunk

                        Yes, it's possible, and that's what you should be getting. There's something not right with the relationship defined linking your two tables.

                        What fields are you using to relate a record in the Items table?

                        BTW, you should have only one record in items for Item A, one for Item B etc. The import process I described was intended to use a validation rule to filter out these duplicates during the import.

                        It also looks like your rack and box fields are number fields. If so, you might need to update the rackBox field to be: 

                        "Rack " & Rack & "Box " & Box

                        The return type of this calculation field should be set to Text.

                        • 9. Re: Listing additional locations in an inventory
                          philmodjunk

                          You might take a look at this demo file:  http://www.4shared.com/file/WqAkhnPl/MultiLocationInventory.html

                          To get some ideas on how this might be set up.

                          • 10. Re: Listing additional locations in an inventory
                            anitaGillis

                            Okay, I fixed the RackBoxField calculation.

                            If I filter out duplicate records for Item A, how will I be able to tell that there are multiple additional locations?

                            Example:

                            If Item A has Records 1-3 corresponding to Rack1Box1, Rack3Box1 and Rack4Box4 and I filter out the multiples, how will I get the database to tell me that the other locations exist? Should I not filter?

                            AG

                            Also, attached is a picture of my Relationship table between the two tables. I have an Items table and an Inventory (Locations) table.

                            Filemaker screenshot

                            • 11. Re: Listing additional locations in an inventory
                              philmodjunk

                              Please take a look at the demo file. The one record for Item A, for example will related to all the location records for item A and thus the calculation field will be able to use the List function to get a list of all the locations.