7 Replies Latest reply on Dec 1, 2016 1:11 PM by philmodjunk

    Inventory Mapping using scripts & portals Please Help

    dgarmsd63

       

       

      Hello all!

      I have followed and mined the knowledge of this community for years now, but now I've finally joined.

       

      I manage a 32000 sq ft warehouse for a local nursery company as well as write and maintain all of our databases. I'm currently writing a new database solution for tracking all chemical product movements & stock between distributor to warehouse and warehouse to store. I'm attempting to add a visual warehouse map function to my database and I've hit a brain block. I'm using FileMaker Pro 11 on Windows.

       

      Here's the concept. You go to the warehouse plan layout ( see attachment 1 ) you click on a rack location button that takes you by script to the rack layout ( attachment 2 ). The script will pass the product information to the appropriate portal based upon location data. The portals are named using object naming from bottom to top "portal1 / portal2 / portal3". My first test script attempt yielded no results ( attachment 3 ) whereas my second script ( attachment 4 ) filled in the sku# & the product quantity # but not the description in every portal. See attachment 5 for my table relationships. The way I see it in my mind is that every location button on the warehouse plan will be linked to its own unique script to pass the correct "rack_ID / bay_ID / level_ID / side_ID" data to the rack layout. I'm sure this is a case of me setting up something wrong that is very simple.

       

      Thanks in advance

      D Garms

        • 1. Re: Inventory Mapping using scripts & portals Please Help
          philmodjunk

          There are quite a few problems in your scripts.

           

          You shouldn't create new records just to display data in a portal. All you need do is update any match fields and/or fields/variables referenced in a portal filter to control what records are shown in a portal.

           

          The way I see it in my mind is that every location button on the warehouse plan will be linked to its own unique script to pass the correct "rack_ID / bay_ID / level_ID / side_ID" data to the rack layout.

          This also is not ideal. You can pass that list of information as a script parameter and use a single script to update any one of these portals.

          • 2. Re: Inventory Mapping using scripts & portals Please Help
            philmodjunk

            I think you should start from these relationships to show where items are located and there quantity at each location:

             

            Inventory----<Inventory_Rack>------Racks

             

            Inventory::SKU = Inventory_Rack::_fkSKU

            Racks::__pkRackID = Inventory::_fkRackID

             

            Inventory_Rack serves as a join table so that a given item in inventory can be located on more than one rack and so a rack can hold more than one inventory item. The quantity for a given item on a given rack would be stored in Inventory_Rack::Qty.

             

            To list all items on a given Rack, you can do one of the following:

             

            1) set up this relationship and use it for all "rack" portals:

             

            Layout'sTableoccurrence::anyField X Inventory_Rack::anyfield

            Then set a portal filter expression on the portal that filters only for a specific ID in _fkRackID. You can make copies of this portal and just edit the portal filter expression to filter for a different rack each time.

             

            2) set up a relationship like this:

             

            Layout'sTableOccurrence::SelectedRackID = Inventory_Rack::_fkRackID

            A script can set a value to SelectedRackID to limit the items in the portal to a specific Rack.

             

            This is just to introduce the concepts that I think that you need. Since you also want to track the actual Bay where an item is located, this approach will need to be extended to use an even more complex data model where you have a join table with one record for each inventory item located in a specific Bay.

             

            But you might want to test these ideas for just Racks before getting that higher level of granularity.

            • 3. Re: Inventory Mapping using scripts & portals Please Help
              dgarmsd63

              Thank you for your answer. I currently have a stack of printed answers of yours to questions by others on my desk and they've all been helpful in my endeavor.

               

              in response to your first paragraph. There are approximately 205 rack views in my warehouse. And you're saying I can funnel all of my locations to those same 3 portals using just portal filtering? Should I have 1 unique match field per portal on the rack layout? Essentially every rack view is level 1, level 2 and level 3.

               

              on your last paragraph, I believe I see what your saying about using a script parameter to pass the location data to the rack layout. I'm just having difficulty visualizing it at the moment.

               

              sincerely,

              D garms

              • 4. Re: Inventory Mapping using scripts & portals Please Help
                dgarmsd63

                Whoops! I was typing as you were giving me more info. Give me a bit to absorb your additional help sir.

                 

                thank you so much!

                • 5. Re: Inventory Mapping using scripts & portals Please Help
                  philmodjunk

                  I gave two options.

                   

                  In the first option, you don't use any specific match fields. The Cartesian join operator ( X ) can match on nearly any field in each table and you get the same results. Filtered portals can make the set up easier, but you also have to test the results with realistic numbers of records in your production environment to make sure that you don't have performance issues..

                   

                  The second option uses a single, script updated match field in place of the portal field. This takes a script where the first option does not, but the absence of a portal filter means that you have generally better performance.

                   

                  In neither case am I specifying a different matchfield (and different relationship) for each rack. But I am also assuming in this second case that you are only viewing one portal at a time. This might be a portal located in a popover that opens when you click on a button placed on top of a rack location on your warehouse map.

                   

                  And there are a lot of details to iron out here that i have not gone into in these posts.

                  • 6. Re: Inventory Mapping using scripts & portals Please Help
                    dgarmsd63

                    Thanks again for the tips! I'm going to do some testing using your suggestions and then get back to you if I have further questions.

                     

                    i did want to address one thing you said in your last post.

                     

                    "But I am also assuming in this second case that you are only viewing one portal at a time."

                     

                    The way I wanted it to work is when the Rack Layout came up is that you would be able to view all of the products on all 3 levels at the same time. So you would be viewing all 3 portals at once.

                     

                    Sincerely,

                    D Garms

                    • 7. Re: Inventory Mapping using scripts & portals Please Help
                      philmodjunk

                      Wouldn't that be one rack with three levels? That could still be just one portal. And this only applies to the option where you use a single match field to control which records appear in the relationship. The filtered portal option does not have this limitation and you can set up more than one match field if you need to avoid a portal filter and need to show more than one portal at a time.