11 Replies Latest reply on Mar 9, 2011 12:04 PM by philmodjunk

    Look up Items in Different Tables



      Look up Items in Different Tables


      Hi There,

      In our warehouse, we have rows of shelving with different items kept in different compartments.  I would like to set up a form which uses variables to look up where the items are in the warehouse.

      For example:

      item: 2" plastic boxes

      I would like the operator to put in the item on a form and then it searches the rows, shelves and finally the compartment to find the item.

      I'm assuming I would need $row, $shelve and $compartment as variables.  but how do I get these variables to go and look up (if that is the right term) in a different table:

      Table: Warehouse

      Fields: ItemID, RowNo, ShelfNo, CompNo, ItemNo

      I should also mention that the warehouse changes after an item is used up.  Its more efficient and so an item doesn't always stay in the same place and therefore the reason for the look up.

        • 1. Re: Look up Items in Different Tables

          I should have mentioned that when an item gets below a certain level, there should be a report that searches the warehouse for the same item but in a different location.  If found, it displays the next location and how many.  If Not found, then it warns the operator items need to be ordered.

          I hope I'm not making this "clear as mud".

          • 2. Re: Look up Items in Different Tables

            Pretty muddy still Wink

            I get that RowNo, ShelfNo, CompNo report the location of an item logged in the Warehouse table, but why to you have to search each of these fields?

            Don't you just have to search the table for ItemID? (and what's the difference between ItemNo and ItemID?)

            Once you have found records with the specified ID, the above three fields tell you where it is located. If you need to establish priorities as to which item is pulled from the warehouse first (Last In First Out or whatever works for your operation), you can sort the list of found records accordingly so that the first record is the one pulled from the warehouse.

            • 3. Re: Look up Items in Different Tables

              I will try to move the mud!

              As items come into the warehouse even though they are the same as something else they are assigned different ItemID.  I know, I know, but that is the way they do it.  

              It also helps to move the earlier stuff out first before moving the new stuff.  This also means the warehouse can put items in different compartments which are more closely sized to the item.

              Apparently by doing this we have 33% more room instead of the way we used to do things.  However, I still need to look up where the items are and what was the earliest item put on the shelf to use it first and then move on to the next compartment.

              Sorry I was trying to keep this uncomplicated and if I can figure out how to find something I can figure out the rest ... I hope!

              So the operator puts in a request on the form that says, for example: 2" plastic boxes (actually it is a drop down box).

              From there the computer goes into the Warehouse table and looks for 2" plastic boxes.  It puts into variables the Row, Shelf and Compartment No and the Date.  It should show this as a list so the operator knows which is the earliest and which is the latest to enter the warehouse.

              I hope that is clear(er) than mud.

              • 4. Re: Look up Items in Different Tables

                Not really. There should be only one field in the warehouse table that identifies the item at that location as '2" plastic boxes' and it really should not be that text, but an ID number that matches to a related table that lists all items that might be stored in your warehouse and assigns them unique ID numbers. Then you can search that one field in your warehouse table for the specified item.

                I still can't tell from what you have posted, which field tells you that a given location stores 2" plastic boxes.

                I think this is the key area that is still cloudy here:

                It also helps to move the earlier stuff out first before moving the new stuff.

                That's typical FIFO warehouse management (First In First Out) and I don't see from the tables in your warehouse table how you can tell which field tells you how long the item has been stored in your warehouse. I can outline the process you need until I get to that point and then I have missing info that keeps me from completing this suggested solution.

                Step 1, define a global search field, gItem, you can either format it with a two column value list where column 1 is the number that identifies the item type (2" plastic boxes) and column 2 is the text that helps the user know what they are selecting. Or you can set this up as a text field and use the text entered to perform a scripted search of an Item table in order to find the Item Type ID number.

                Step 2, now that the Item Type Id has been looked up, the script searches the warehouse table for all locations that store this item.

                Step 3, I can't tell you how to do this yet, but it performs a sort to arrange the found records in FIFO order. The first record then tells you what location to pull the item from.




                • 5. Re: Look up Items in Different Tables

                  Part of the problem is for the order taker they get emails or a phone call from a customer requesting, 2" plastic boxes, so the order taker has to go through all of the warehouse and find all of the 2" plastic boxes and then narrow the search from there.  Most of the customers know the item number but you would not believe how many customers call with requests like the above.

                  From the list that they get they can usually narrow it down to the item that the customer needs.  They get calls from drilling companies way up north and because the part is broken they can't find the part number.  

                  So that is why I am stuck.  If I had a part number, sure this would be easy.  So, is there a way I can do this with an ambiguous term like, 2" plastic box? Hope this helps.

                  I should also add we have in the Warehouse table, fields that include things like '2" plastic boxes', simply because of what are customers are used to calling them.

                  • 6. Re: Look up Items in Different Tables

                    What if you set up a search layout that has a global text field to enter what your customer is requesting.  Then a portal showing related records from your Warehouse table listing the item descriptions and item numbers. This would give you some leeway for the odd customer requests, you could enter "plastic boxes" and see a list of all plastic boxes in stock. or enter a specific Item number you would get just that one item in the list.

                    You can then find the closest match from the lines in the portal, cliick on that and go to another portal showing where that item is located.  If there are multiple locations, have the portal sorted based on the date the item was received, acending or decending depending on whether you want FIFO or LIFO.  If you also display the quantity for each location you can use that to determine the best locations to pull to fill the order.

                    • 7. Re: Look up Items in Different Tables

                      Mark and "fileMaker" customer:

                      This is what I have been trying to get at with my questions.

                      You indicated that "even though they are the same as something else they are assigned different ItemID"

                      Thus, you have yet to indicate what field in the table can be used for this search to find any given item.

                      You still haven't answered this question. "What field in warehouse can be used to tell when the item was stored there?" Without that info, you can't use any kind of FIFO or LIFO strategy to determine from which location you should pull your item.

                      Here's the basic structure you need as far as I can understand from your preceding posts:

                      Warehouse needs these fields:

                      ItemID, RowNo, ShelfNo, CompNo, ProductID, DateReceived

                      My ProductID may be the same as your ProductNo but you never answered the question I asked about it so I'm using my own fields for this example.

                      You'd also have a table called Products with
                      ProductID (auto-entered serial)
                      ProductDesc (Text)
                      //and any other fields such as price, size, weight etc. that you need to describe the item.

                      This relationship:

                      Warehouse::ProductID = Products::ProductID

                      On a search form, the user can select a ProductID from a value list where Products::ProductID is listed in column 1, ProductDesc is listed in Column2. The user uses the Product Description to select an item, but the value list enters the ProductID. The field set up for this drop down list would be a global number field, gSelectedProduct.

                      Now your script can work like this:

                      Go To Layout [Warehouse]
                      Enter Find Mode[]
                      Set Field [Warehouse::ProductID ; YourTable::gSelectedProduct ] //gSelected Product must have global storage and can be defined in any table
                      Set Error Capture [on]
                      Perform Find[]
                      If [Get (foundCount ) = 0 ]
                         Show Custom Dialog ["This item is out of stock"]
                         Sort [No dialog; Restore] //Store by date Received in ascending order
                      End IF

                      From that list of records you can start pulling items, starting with the oldest item on the shelf to follow th FIFO strategy that you wanted.

                      There are more sophisticated approaches that you can use for this. They all follow this basic pattern. They just provide more user friendly ways to help the user find a product and get it's ProductID number entered into gSelected Product for this script to use.

                      Here's one method:  Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

                      It's also possible to set up a partial text match search widget with a portal that lists all matching items from the Products Table. The user can keep entering more and more text to refine the search or they can just click a row in the portal of matching items to select it and initiate your search of the Warehouse table.


                      • 8. Re: Look up Items in Different Tables

                        Oops, sorry I thought I answered your question PhilModJunk.  Actually I like your way better, ProductID versus ProductNo.

                        Argh! You can tell I'm a Newbie at this.  The person who got us Filemaker Pro 11 has quit and has left me with the job of cleaning this all up.

                        I have to say it has been a LEARNING process.  Don't get me wrong, I really like Filemaker Pro (especially because we were using MS products before this and this seems much more in tune with what we are doing).

                        Thanks to you and Mark for your suggestions.  I will try it now and see if I can get it up and running.  I will post with the results.

                        • 9. Re: Look up Items in Different Tables

                          Phil, I was saying the same thing as you just trying to explain it from a different angle.

                          pademo, Filemaker is nice to work with, the only problem is that as you learn more things you get more ideas - can get kind of addictive.

                          • 10. Re: Look up Items in Different Tables

                            Your right, it is so addictive, and believe me, I am learning that so fast!!!

                            • 11. Re: Look up Items in Different Tables

                              Welcome to the club. The "FileMaker Addiction Recovery Group" meets just down the hall...Wink