cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Inventory over time and at specific date

Good evening/morning,

Not 100% sure if I am posting this in the correct place but I work for an accounting firm and am working on testing powerapps to see if it is useful for clients. My initial project is to build an inventory tracking app in Power Apps that will then allow the data to be used in Power BI for Visualizations. I am stuck at step one which is figuring out a way to structure the data so that I have current quantity on hand as well as being able to filter by a specific date (end of Q4 for example 12/31/2019). My question is how should I structure this, should I use a field in Common Data Service for the date of each physical inventory count or is there a way I can design the app to do a count on "x" date and have it create records for each item and qty as of that date?

3 REPLIES 3
Highlighted
Solution Supplier
Solution Supplier

You'll find it easier to consume / report on data if you do a count on "x" date and have it create records for each item and qty as of that date. This would be best done on a scheduled basis using Power Automate (aka Microsoft Flow) to run a task on a schedule to create these records. The easiest way to do the count is to use a rollup field; note that rollup fields are automatically recalculated on a schedule (every hour)

Highlighted

Easiest way to do it with MS Flow on the scheduled basis and create records. 

Super User
Super User

Are you tied to CDS for this project? 

If you can use SQL (more powerful relational querying) you could create a query that for each product:

* Checks for the most recent stock check date/time and the confirmed quantity

* Sums all of the stock-in and stock-out movements since the most recent confirmed count (up to a specific date/time or to the current date/time)

This has the advantage that the data is alwasy 'live' - i.e. any modification is visible immediately in any reporting, rather than with roll-up fields where the data is only as fresh as the last time the roll-up ran.
Even if your data is in CDS, check to see whether they mirror it to SQL.

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Users online (10,463)