8 Replies Latest reply on Aug 4, 2016 9:11 AM by bigtom

    Track Supplier Inventory change

    billsayagh

      We have a manufacturer that supplies us with daily stock updates for their items. What i am trying to accomplish, with no avail so far, is to create a way to track the daily changes for these items, to use in a dashboard that will track the daily inventory levels, giving us an idea to how the products stock levels changes throughout a period. Uploading the the inventory file on a daily basis, changes the quantities but does not track the changes. Is there anyway someone can point me in the right direction to accomplish this.

      Thank you very much in advance for any input

        • 1. Re: Track Supplier Inventory change
          Johan Hedman

          I would set up a new table that and run a FileMaker Script on FileMaker Server that saves data into the new table for your dashboard. Then you only need fields to show in your dashboard

          • 2. Re: Track Supplier Inventory change
            bigtom

            A new table or additional fields for tacking the change amounts. How you calculate is up to you.

             

            Product ID

            PrevQty

            CurrentQty

            QtyChange

            Date

            • 3. Re: Track Supplier Inventory change
              DanielShanahan

              billsayagh wrote:

              ...track the daily changes for these items, to use in a dashboard that will track the daily inventory levels, giving us an idea to how the products stock levels changes throughout a period.

              I'm a bit confused.  Are you saying you want to track the inventory stock levels of your supplier?  If so, why?  What is the business case for tracking their numbers?  Are you concerned that they cannot meet your future quantity requests?

              • 4. Re: Track Supplier Inventory change
                billsayagh

                @This new supplier carries 300 Brands and about 30000 items and they provide us with their inventory levels everyday.

                Since we cannot purchase everything form them, we wanna be able to use this data to get information about their best selling items and focus our marketing and advertising strategy on those items. 

                 

                So overtime we can track the performance of certain products or categories of products, by day, week, or month, and estimate the purchase levels needed before placing our orders. in addition to the fact that we could tie in our sales data with this table and estimate weather we are doing a good job stocking those products to limit holding customer orders.

                • 5. Re: Track Supplier Inventory change
                  DanielShanahan

                  Thanks for the clarification.

                   

                  I’m curious what data you are receiving from your supplier.  If it is just their quantity level then I don’t see how that tells you about their selling trends.  For example:

                   

                  Date              Item                 Qty

                  08/01/2016   Red Widgets   100

                  08/02/2016   Red Widgets   100

                  08/03/2016   Red Widgets     80

                   

                  • Are the 100 on August 2nd the same 100 as on August 1st or did they sell some or all of the widgets from August 1st and replenish with a new 100?

                   

                  • Presuming they sold 80 widgets between August 2 and August 3 - were those sold at full price or a discounted price in order to move the product?

                   

                  Of course, it could be that you are trying to get a rough overview, and perhaps the data you have gets you pretty close.  In any case, I concur with bigtom and Johan that you should import this into a separate table that is related to your product table.

                  • 6. Re: Track Supplier Inventory change
                    billsayagh

                    DanielShanahan Another question if you do not mind?

                    How would these changes be tracked by date on widget? Do I need to add a date field to track those changes or would filemaker establish that based on the daily updates?

                    Thank you guys both for your solution DanielShanahan & bigtom

                    • 7. Re: Track Supplier Inventory change
                      DanielShanahan

                      Yes, you would need a date field. You could have FMP automatically enter the current date.  However, that presumes that you'll always upload the data everyday.  If you miss a day, then your dates could be off.

                       

                      Does the data the supplier provides have a date?  If so, I would use that.

                      • 8. Re: Track Supplier Inventory change
                        bigtom

                        I believe this is a daily upload. So it would work. If you do two in one day you would need a time stamp rather than just a date.