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

Re: Inventory over time and at specific date

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

Re: Inventory over time and at specific date

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

Highlighted
Super User
Super User

Re: Inventory over time and at specific date

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!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (7,969)