I am attempting to make an equipment rental inventory that will aid users with scheduling. What I've managed to do so far is this:
Users can select equipment in a portal that is then added to a customer's invoice. In the same row, it shows the amount of that equipment that we have in stock so for instance if we get a request for 5 pieces of one equipment type and the total shows we only have 4 in stock, we know that we have to amend the order. As each order is entered into the system, the quantity on hand is updated by subtracting the sum how many pieces of a certain type of equipment went out from the total of our inventory for that given item. Tables are set up as: Equipment and Invoice tables both connect to an Invoice Line Item table from where I get the quantity for each piece of equipment on each rental.
The problem is that this doesn't take time into account; it's as if every single order that gets entered is for the present. I want to have this only to show what quantity we'll have on hand during the given rental period. For example, say someone is renting from Monday to Tuesday, another from Tuesday to Thursday, and we get a request for something from Wednesday to Thursday. Right now it's counting all those requests against our total stock when I want it to only look at the one from Tuesday to Thursday. I managed to get close using a really convoluted method that's probably not what I should be doing; I created a portal that filters out only the invoice requests that overlap with the current request to be scheduled. Inside that filtered portal are the quantities of equipment that go out for each. I can't find a way to take the sum of only those quantities in the filtered portal and plug that into my calculation that figures out how much stock we'll have on hand.
Am I even going about this the right way or is there a much more efficient way?
Any thoughts would be much appreciated.