13 Replies Latest reply on Nov 21, 2012 11:12 AM by philmodjunk

    Cascading Update for Inventory Item Location

    SkipperID

      Title

      Cascading Update for Inventory Item Location

      Post

           Hi 

           Newbie here, using FM12. 

           I have a db with inventory items, which are all stored in different locations. I have a [Locations] table which sort of works like an order details table linked to the [Item] table. 

           LocationRecordID (PK)
           ItemID (on [Items])
           Building
           Room
           Row
           Shelf
           Pallet
           Box

           What I'd like to be able to do is: 
           1. Scan barcode of Item
           2. Scan barcode of parent location
           3. Update higher level data into current field.

           For example:
           I have previously recorded that Box #5 is in Building 1, Room 2, Row 3, Shelf 4, Pallet 5

           I want to put Item #22 into box #5. So I scan Item #22 and Scan Box #5, and then want the rest of the fields to note that it is in Building 1, Room 2, etc. 

           I want this to cascade (upwards, I think) so that if I can go to the warehouse and make changes to any part of this storage chain (move the pallet to a different shelf, Row, Room, Building and still know where the item is.)

           SO: If I move box #5 to a different pallet, shelf, etc. all I would need to do is scan Box #5 and the new pallet/shelf/ etc. # and I would have the current location of any part of the location of the item. (because the item is still in Box #5)

           Notes: 
           - not worried about the scanning scripts in this post
           - Some items can be in a room but not on a shelf, not on a row, pallet, etc.
           - I am not only concerned with where the item is, I am also concerned with where it has been

           Not sure I am going about this the right way... any thoughts would be appreciated!

           Thanks!

        • 1. Re: Cascading Update for Inventory Item Location
          philmodjunk

               There should be no need for any such "cascade" if I am understanding you correctly. The Location data for a given box (or other designated location) should be only recorded in one record of one table. Any situations where you need to show that location info such as when you record the location of a given item, should employ a relational link to that location record.

          • 2. Re: Cascading Update for Inventory Item Location
            SkipperID

                 I have two tables... [Items] and [Locations]

                 The only relationship I have is from the Item:ItemID to Locations:ItemId

                 I don't think I'm conceptually on the right track. I'm willing to start from scratch, but can you explain a better setup. 

                 Thanks

            • 3. Re: Cascading Update for Inventory Item Location
              philmodjunk

                   Then what fields do you need to update in your "cascading upate"?

                   This sounds like you have fields in both tables that store the same data when the fields should only reside in Locations.

              • 4. Re: Cascading Update for Inventory Item Location
                SkipperID

                     I think I am looking at this more from a "Tracking" point of view. 

                     So... I put empty box #5 on pallet #4. I scan the box and the pallet and now I have a record: 

                     ID |  Item | Building | Room | Shelf | Pallet | Box  
                     01                                                              4         5  

                     Now I put item #22 into Box #5. I scan the item and the box

                     ID |  Item | Building | Room | Shelf | Pallet | Box  
                     02     22                                                                 5

                     I put pallet #4 on Shelf #2. I scan he Pallet and the Shelf

                     ID |  Item | Building | Room | Shelf | Pallet | Box  
                     03                                                  2          4

                     Assume then that I would put a shelf into a row, into a buiding. How do I calcluate where Item #22 is?

                     Then, I put a new item into Box 5. I scan the Item and the Box
                     ID |  Item | Building | Room | Shelf | Pallet | Box  
                     04    23                                                                  5

                     Where is Item #23? (Answer: Box 5, on Pallet 4, on Shelf 2) But how do i calculate that?

                     Thanks for helping!

                • 5. Re: Cascading Update for Inventory Item Location
                  philmodjunk

                       I think you need to rethink your tables an relationships.

                       Each record in Locations should be the record for one single location. You should never have more than one record in this table for Box #4.

                       Each record in Items should be the record for a single item. You should never have more than one record for any given item.

                       Then your relationship is:

                       Locations----<Items

                       Locations::__pkLocationID = ITems::_fkLocationID

                       See: Common Forum Relationship and Field Notations Explained if you are unfamiliar with this notation.

                       When you scan your two barcodes, you use that data to find the item record and update it with the location ID that you scanned.

                       Then, on your Items layout, you can add any fields you need from the Locations table to show the current location of that item.

                  • 6. Re: Cascading Update for Inventory Item Location
                    SkipperID

                         Thanks for sticking with me. 

                         I think I understand the relationships in the link you provided, but can't yet see how to apply it to this scenario. 

                         - If there is only one record for an item, how do I know an item's history? (Where are the last 4 places it was? I want to print a report like a FedEx tracking of a package) 

                         - In order to move a pallet to a different location (and the boxes on it) would i approach it like this:
                         1. Scan Pallet + Scan New Shelf
                         2. Perform FIND of all boxes on that pallet
                         3. Update Found Records to indicate new Shelf Location
                         - but that still doesn't update what Row, Room, or Building location the new shelf is. 

                         I guess I want to just update the Pallet location, and that update where the boxes are. 

                         Almost there?

                    • 7. Re: Cascading Update for Inventory Item Location
                      philmodjunk
                           

                                - If there is only one record for an item, how do I know an item's history? (Where are the last 4 places it was? I want to print a report like a FedEx tracking of a package)

                           For me the need to know the history of past locations is new information.

                           That should be logged in a separate table by ItemID and should just log the ItemID, The LocationID and the current date, or TimeStamp. That's all you need to build a history of past locations for each given item.

                      • 8. Re: Cascading Update for Inventory Item Location
                        SkipperID

                             Hello

                             I really appreciate the help. I've been working on this a while and still haven't figured out how the solution you suggest solves my needs. 

                             If i make a locations table, I assume it becomes a list of all the many, many location possibilities and then I assign an item to a location. But I still don't understand how to deal with the fact that boxes will change pallets often, and pallets will change locations (rows/shelves, etc) 

                              

                              

                        • 9. Re: Cascading Update for Inventory Item Location
                          philmodjunk
                               

                                    If i make a locations table, I assume it becomes a list of all the many, many location possibilities and then I assign an item to a location.

                               This is not the case. You only create a record in this table at the time you store it at that location.

                          • 10. Re: Cascading Update for Inventory Item Location
                            SkipperID

                                 If this information isn't going to be dymanic, why don't I just keep it in the item table? 

                                 Can you please point me to a more detailed explanation? I think I understand the point you are trying to make, but it doesn't seem to answer the question of what I am trying to accomplish. 

                                 Thanks

                                  

                            • 11. Re: Cascading Update for Inventory Item Location
                              philmodjunk

                                   The location and identity of each item IS dynamic. It will change each time you move an item, remove an item or store a new item.

                                   Because this table lists every item at evey location at which it has been stored and each entry also includes the date at which it was stored at that location, you have a complete history of every location a given item has occupied.

                                   You did post earlier that you needed to keep track of past locations did you not? You can't do that if you keep in in the item table without creating multiple records for the same item--generally not a good idea if you are recording any additional data about that item besides it's ID number encoded in the barcode, it results in duplicated data and the need for your "cascading update", which is what this approach avoids.

                                   Items-----<Item_Location>------Locations

                                   Items::__pkItemID = Item_Location::_fkItemID
                                   Locations::__pkLocationID = Item_Location::LocationID

                                   Item_Location serves as your "log" of present and past locations for a given item. A date or timestamp field can auto-enter the creation date or timestamp to record when the item was stored at that location.

                                   A Portal to Item_Location placed in the Items layout will list all present and past locations for that item. Fields from Locations can be added to the portal row to provide additional information about each location is such information is needed in that portal.

                                   In similar fashion, a portal to Item_Location on the locations layout will list all items that have ever been stored at that location, both past and present. If you need to filter such a list to show only the items currently stored there, this can also be done, though I haven't discussed that option with you at this point.

                              • 12. Re: Cascading Update for Inventory Item Location
                                SkipperID

                                      

                                I understand the portal apects of your comment (but I am more likely to look at it by "where is an item" than "what is stored at this location", but i think all of that is solveable wih a find for an itemID and the most current timestamp.)

                                I think I understand the setup of the tables. What you talk about makes sense to me if the fields in -Locations- are:

                                      

                                Location::__pkLocationID
                                Location::Building
                                Location::Room
                                Location::Row
                                Location::Shelf

                                     Those are easy to assign because they don't really move- every item is assigned to a location.  But I am confused when I get to these fields: 
                                Location::Pallet
                                Location::Box

                                     Because these two items change frequently and when they do change, the earlier fields in the record may need to change too. Within <Location>, there are no relationships between all of these fields. I don't understand the workflow.

                                     If I move a box to a different pallet, I scan the barcode of the box (which contains ITEMS) and the Pallet which creates a record in <Item_Location>  How do I get the Shelf, Row, Room, Building to populate their appropriate fields with just these two barcode scans? The only way this seems possible is in <Location> there is a record for every possible permutation, which seems like overkill. 

                                     Just to be thorough, the same question exists at any point in the chain... if i move a pallet from Shelf A to Shelf B, I scan the Pallet and the New Shelf but how am I populating the other fields in the record (shelf, row, building, and also which boxes, items are on that pallet) 

                                     -OR- are you suggesting that if a pallet is moved to a new shelf it requires a new record in <Locations>? If so, doesn't that make for infinite permutations because every space could, in theory have every single pallet at one time or another, and I'd need to create those locations before I can put a pallet anywhere.

                                     I appreciate your responses, I just don't see what piece I am missing to translate this into making sense for me.

                                     Thanks

                                • 13. Re: Cascading Update for Inventory Item Location
                                  philmodjunk

                                       That definitely complicates things because moving a box or a pallet changes the location for an entire group of items all in one scan of a pair of bar codes. This then DOES lead to a need for a script that does a cascading update of the relevant items given our current structure here.

                                       What I'm now mulling over is the fact that neither a pallet nor a box is actually a location. That's not just picky semantics, but a case where the functions involved actually differ from the building, room, row, shelf indicators that mark a true, fixed location.

                                       As I see it, boxes and pallets are really special kinds of items that can contain groups of other items.Thus, moving a box from one shelf to another, for example changes the location of the box  as well as the items stored inside of it.The interesting thing here, is that if you know the box that currently contains an item, you could, in theory, know the item's location by checking the location for the box. What I'm unsure of if what you need in terms of a location history for items contained in a box or Pallet. The Item_Location field could be updated via script each time you move a container, or it could just log the location change of the container.

                                       However we work out those details, I'm thinking a "container" table can be used to log what items contain other items and this can "nest" for more than one level. You can log what boxes are loaded on a pallet and then also what items are stored in each box on that pallet.

                                       Items>----Containers

                                       Items::_fkContainerID = Containers::__pkContainerID

                                       Containers can be an additional occurrence of Items.