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
Continued Contributor
Continued Contributor

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
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (5,972)