It's not that simple.
Ideally you should have a function or a script that gets 2 date parameters and an item ID, giving you back the minimum of item "ID" you have in that period.
There's an interesting article marginally touching the problem here.
That does sound complicated as you say.
I wonder if a simpler solution could be found.
Would I be able to link 20 items being included on a packing list with the transactions field already included in the solution. If entry of 20 x A was recorded as a transaction "out" and return of same became an "In" then I would always have an accurate stock count of whats available.
I am not sure how I might begin to set this up but, assuming this is achievable it would negate the need to reference the date ranges in the original question.
Is that making any sense?
If the "job is out for n days" has a fixed duration, i.e. n = constant, it can be simplified, otherwise I'm afraid not.
Sorry if I'm not giving up here but if n = constant makes the process easier, does n multiplied by a given quantity go back to being complicated? i.e.. If n = 1 day can i not then have a separate way of selecting number of days?
That would collide with
Original post: need for 2 dates, hence my first post, and
Next post: negate the need to reference the date ranges
Sounds like you need a "Reservations" table by equipment and by date. Each time you create a packing list, you add the quantity needed for each piece of equipment to each day record in that table…checking that the sum of the quantities for that day does not exceed the equipment total.
Yes, it would.
However, I am simply trying to look for a solution that is not over complicating matters.
Apologies if the subsequent posts don't follow the same questions - when the original idea was seemingly difficult I realised i needed to change thought process and try to find an easier way.
Jade - That sounds like a workable approach.
Would you think that would be difficult to set up?
It will take a bit of patience, testing, and time but not overly difficult. (I aways say that at this stage ;-)
I assume you already have an equipment table with an equipment id and a packing list table with its id.
Roughly, the relationship graph might look something like this:
Packing_List—< Reservations >—Equipment
Reservations is a many-to-many join table with these fields:
Running Total, Summary, or…??
When you add equipment to the packing list you need to add a record to Reservations for each day in the packing list's date range.
When you change an equipment quantity in the packing list, you must find those records that match the equipment id and date and then change those quantities in Reservations.
When you delete a packing list or equipment within the packing list, you must delete those matching records in the Reservations.
You will need a means (script) to validate the reserved quantities. I haven't figured out whether a running total, sum, summary or find matching records could be used to compare the sum of each day's reservations against the equipment quantities on-hand.