AnsweredAssumed Answered

Inventory Count DB help

Question asked by asafain on Feb 25, 2017
Latest reply on Feb 27, 2017 by DanielShanahan

Hello Community,


I need some assistance please. 

I am long time FM user and have built some decent apps that work great and we use every day.  Though, I have been working on an inventory DB that I just can't get right.  I have modified it and changed it numerous times over the years and I am hoping someone here can help with the current versions of what this DB should do...


OBJECTIVE:  A DB app that is used to count all the bottled beverages (items) on the bar, in the cooler, and in the storage.  The NEW RECORD is a list of all the beverages (items) that we have and we would select location, and select qty for item for this date that we count.  The ITEMS would be kept in a table that would auto update to the COUNT table.  As items change we would update them in the ITEMS table and then when we did a count the items would be listed there in the COUNT table.  Important,  the NEW RECORD would be based on the DATE.  This new record would have all the items in the ITEMS list in the table in one view.  Perhaps we would remove the items we didn't need or have per NEW RECORD count. 


After we create two COUNTs (could have same technical day/date, or could be days apart) then we could get a difference calculation between the two COUNTS. 


Ex.  If on 2/10/17 at 5pm we clicked "new record" and within this record a long list of all our beverages is accurately listed then we count Bell's Oberon at 20 bottles.  Then on 2/10/17 at 11:30pm we created another COUNT record and counted Bell's Oberon at 10 bottles. 


Then we could somehow execute a calculation between the two records to show the difference in  Bell's Oberon as 10. 

Not sure how this would be handled.  I assume there would be another table to pull any two counts up and execute the calculation and create new record of this DIFFERENCE COUNT??


We could keep COUNTS and at any time create DIFFERENCE COUNTS and we could go back a look at these record over time and compare to sales.  Note:  we do have an inventory component in our POS but it doesn't do this.  It doesn't let you take snapshot counts and compare at any time you wanted.  We would want this to be usable in FM GO. 


Now if I can get this working I would then want to modify to use with liquor weight units of g/oz (instead of bottle units). 


I hope this makes sense and you can help.  Thanks in advance.