Power Platform Community Forum Thread Details

I really wish I could download the Inventory Management templates – but I am not allowed to – so I am building old school. Definitely still learning how to tie my shoes.

I am using 2 Dataverse tables in a Canvas App to help manage inventory. I want to display/calculate the number of items available on the Canvas App so the customer knows how many items can be reserved.

The solution allows the customer to reserve items – then return them once they are done using them (banners, tables, displays, etc)

TotalReserved: I imagine that I need to filter through the Reservation table to count how many times a specific item is reserved.
TotalInventory: I also need to grab the total number of items in inventory – regardless of reserved or not. This is found in the Inventory table.
TotalAvailable: Then I need to calculate. TotalInventory – TotalReserved = TotalAvailable.

I also need to figure out how to filter the above using a date range – but I thought I would take this in chunks.

How would I write the code for this – using the 2 tables and calculating the difference?

Thanks!

Categories:

Microsoft Dataverse with Power Apps

Hey @jQuery

I have assumed a few things here, as in you have a Specific Item ID, the tables are called Reservations and Inventory, and that there is a quantity column on the Inventory table, the below might help you out

// SpecificItemID is a variable holding the ID of the item you want to check
Set(SpecificItemID, "123");

// Calculate the total reservations for the specific item
Set(
TotalReserved,
CountRows(
Filter(
Reservations,
ItemID = SpecificItemID
)
)
);

// Get the total inventory for the specific item

Set( TotalInventory, Sum( Filter( Inventories, ItemID = SpecificItemID ), Quantity ) );

// Calculate the total available items
Set(
TotalAvailable,
TotalInventory - TotalReserved
);

---------------------------------------------------------------------------

If I have helped you with an answer, please accept this post as a solution and thumbs up my reply. Thanks !

Blog - JonDoesFlow
Twitter - JonDoesFlow
YouTube - JonDoesFlow