7 Replies Latest reply on Jan 8, 2010 12:37 PM by liquidcooled

    Creating a Dual entry in one table for multiple location inventory.

    liquidcooled

      Title

      Creating a Dual entry in one table for multiple location inventory.

      Post

      I'm new to filemaker, but I'm slowly getting places. I'm creating a multiple location inventory system where items in inventory can be placed in different locations and the inventory will be able to tell the user which items and how many are in each location. I'm planning on using an item transaction table to keep track of all movements of inventory with a field for location, qty in and qty out. My problem now is moving an item from one location to another. I know that what I need to do is create a dual entry in this table. One entry for which location and Qty out and one entry for which location and Qty in. I just can't figure out how to go about doing this. In an ideal world I would like to create one from with a field for Item being moved, Qty Move from location drop down and move to location drop down. 

       

      Any advice on how to tackle this problem would be greatly appreciated  it's starting to hurt my head.

      Thanks 

        • 1. Re: Creating a Dual entry in one table for multiple location inventory.
          comment_1
             Wouldn't one record {FromLocation; ToLocation; Item; Quantity} be sufficient?
          • 2. Re: Creating a Dual entry in one table for multiple location inventory.
            philmodjunk
              

            I'd use global fields for each field on your "move form":

             

            gItem, gQtyMove gLocationFrom, and gLocationTo

             

            Your script would look something like this:

            Go To Layout [TransactionLog (Inventory)]

            New Record/Request

            Set Field [Inventory::Item; gItem]

            Set Field [Inventory::Location; gLocationFrom]

            Set Field [Inventory::QtyOut; gQtyMove]

            Set Field [Inventory:: Date; Get ( CurrentDate) ]

            New Record/Request

            Set Field [Inventory::Item; gItem]

            Set Field [Inventory::Location; gLocationTo]

            Set Field [Inventory::QtyIn; gQtyMove]

            Set Field [Inventory:: Date; Get ( CurrentDate) ]

            • 3. Re: Creating a Dual entry in one table for multiple location inventory.
              liquidcooled
                

              This seems like it will work. I've tried it, and it works sort of. My problem now is clearing the global fields. (I don't have any experience with them.) I could also use some advice on how best to handle the global fields for this application. What I have done now is to place the global fields on my Item_Transaction Table (the table where the Inventory in out data is stored) I made a new table occurrence so that I could connect the global fields with the correct tables such as gItem connected to the Items table. 

               

              I have two questions about this.

               

              Is it better to create a new table with just global fields and set up my form based on this new table? 

               

              How do I clear the global fields so when the user goes back to the form it is empty?

               

              • 4. Re: Creating a Dual entry in one table for multiple location inventory.
                philmodjunk
                  

                First the easy part, to clear any field global or otherwise use:

                 

                Set Field[table::field; ""]

                 

                In your case, once your script has logged the transfer, do one such step for each global field.

                 

                Global fields are accessible from any table, any layout even if the table itself has no records. Thus, from a functional stand point, you can put the global fields anywhere as long as you aren't using them to define a relationship.

                 

                I've been leaning more and more to using a single Globals table to list all such fields not used in relationships. I don't have to do this, but it seems to be a nice way to keep track of all of them.

                 

                "I made a new table occurrence so that I could connect the global fields with the correct tables such as gItem connected to the Items table. "

                Don't see why you would need to do that as gItem is strictly used to hold data that will be moved to your log table. (You can format it with a value list without having to define a relationship linking it to your items table.)

                • 5. Re: Creating a Dual entry in one table for multiple location inventory.
                  liquidcooled
                    

                  Ok thanks that seems to be working. I do have a question about the drop downs though. As far as I can tell I need to have the related tables so that it will show the Item or location name after the user has made a selection.

                  I want the selection from my drop down to be the primary key which is a number. I know that when the user selects the drop down it can display any other field that you want it to from that table, but after the selection is made it only shows the value of the first field.

                   

                  Is there a way to have it record the primary key, but only show the user the name field?

                   

                  Thanks for your help! Getting that form working has gotten me close to wrapping this project up.... I think : )

                   

                  • 6. Re: Creating a Dual entry in one table for multiple location inventory.
                    philmodjunk
                       Ok, that explains why the extra relationship is needed. You could change your format from Drop down list to Pop Up menu. A two column value list with the first column hidden used with a pop-up menu results in the menu storing the ID number (column 1) but displays the name (column 2).
                    • 7. Re: Creating a Dual entry in one table for multiple location inventory.
                      liquidcooled
                        

                      I forgot about that option. 

                      Thanks