cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EKabina
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
DavidJennaway
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)

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

PaulD1
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
User Groups Public Preview

Join us for our User Group Public Preview!

Power Apps User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (51,272)