filling 'in between dates'

[crazybake]

I have just changed our system to allow individuals to checkout items for 2-days or a week (used to be all just one day, which made everything easy) - and I have a calculation field to determine when the item is due.

 

So, an Individual can checkout equipment on May 5 and it will be due back on the 11th.

 

However, I have a reservation option as part of this solution -  and I cannot figure out a way to:   

 

Today, I have an individual reserve an item for June 1st (on an item that is allowed for a week) which, when checked out would be due on June 7th. I have another individual reserve the same item for June 4th - I need to figure out a way to 'block' the dates in between June 1st and June 7th so it doesn't allow the second reserve. I have been trying a repeating field for the reserve transaction which would add the additional dates, but there has to be a better way.

[Rick Whitelaw]

You could start by creating a calculation field that basically says If(GetCurrentDate) >reserve start date AND Get(CurrentDate)<reserve end date the value should be: probably 0. Go from there.

[crazybake]

That makes sense, but it would have to compare this data with all reservations - maybe you could guide me a bit further. And, I may not have set this up correctly. All transactions have status numbers assigned (0 for complete, 1 for checked out and 2 for reserved). When an individual wants to reserve an item I have it currently setup to do a find for all reservations (2s) for a particular item number and chosen date

 

Not sure how your suggestion above fits

[BrammersUK]

I remember having to do something similar to this quite a while ago.

 

Not sure if it was the best way, but it worked, and was foolproof... Basically I had a start date field, an end date field, and an auto calculated field that listed all the possible dates in between the start and the end. Once I had that information associated with each record, it was easy to have a script check against the calculated field to see if a particular date was in the list or not. I think I had the  'check' script run whenever a new record was committed, and used script parameters to make sure it was checking against the correct existing records etc.

 

Hope this helps, or at least gives you another method to think about.

[crazybake]

Is it logical to do something like:

If an individual wants to reserve an item it would-

Perform a find of all records which are reservations or currently out (1,2)

then loop the script of the found records so that

if globalRserveOut >= outDate and globalRserveOut < dueDate or

if globalRserveDue >= outDate and globalRserveDue < dueDate

error

exit loop if result is zero

 

any ideas