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
A new table or additional fields for tacking the change amounts. How you calculate is up to you.
...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?
@DanielShanahan 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.
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.
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.
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.