2 Replies Latest reply on Jun 19, 2011 8:06 PM by WilChen

    Please help - Date Validation per Item.



      Please help - Date Validation per Item.


      Hi all, 

      I need a big help here, 

      I have 3 fields in this database. (1) is Item ID, (2) is DateOUT, (3) is DateIN

      Item ID is my equipment to be rent out. >>This field is copied from another database (Equipment Database)

      DateOUT is the day item being rent out, DateIN is item being returned.

      All below is talking about one single item/equipment .

      Let say there is already have a record ITEM ID 28, will be rent out on 15JUN2011 (DateOUT) and return on 26JUN2011 (DateIN).

      So when I make a new record entry I input ITEM ID = 28, DateOUT=16JUN2011, DateIN=27JUN2011,

      I wish there is a pop up msg to say the day I input is not available "Item is checked out in that period of time." and cancel the new record entry.

      I have about 500 items, the thing now I am creating is like every item have their own calendar, i just don't know how.

      MySkill>Average Beginner

      My File is a single file with 4 database inside, all created from scratch, It run with simple script steps and relationships graph.

      Anyone can help? thanks ^^

        • 1. Re: Please help - Date Validation per Item.

          I helped another couple of users with a room reservatations problem that dealt with this same issue. (Don't let a room be reserved if a previous reservation record for the same room overlaps the date range entered.)

          First, I'd enter the three pieces of information into global fields and use a script to check for availability and create the new record only if the item is available. Define three new fields in your Rentals table: gItemID, gDateOut, gDateIn and select global storage for each.

          You only describe one table here for your rental records so I will just set this up using that one table.

          There are three ways a date range can overlap here.

          1. Date Out can fall within the date range, Date Out ... Date In.
          2. Date In can fall within the range.
          3. The date range of the new rental period "encloses" the date range of an existing rental record. (DateIN of the new rental is earlier than DateIN of an existing rental record and DateOut of the new rental is later than the DateOut of an existing rental record.)


          It takes three relationships to check all three possibilities. Since we're working with a single table here, that requires 3 new table occurrences of your Rentals table. To create a new table occurrence of an existing table, select it in Manage | Database | Relationships and then click the button with two green plus signs. You can then double click this new occurrence to open a dialog where you can change its name.

          You'll need these three relationships to check the above 3 possibilities:

          Rentals::gItemID = RentalsOut::ItemID AND
          Rentals::gDateOut > RentalsOut::DateIn AND
          Rentals::gDateOut < RentalsOut::DateOut

          Rentals::gItemID = RentalsIn::ItemID AND
          Rentals::gDateIn > RentalsIn::DateIn AND
          Rentals::gDateIn < RentalsIn::DateOut

          Rentals::gItemID = RentalsEnc::ItemID AND
          Rentals::gDateIn < RentalsEnc::DateIn AND
          Rentals::gDateOut > RentalsEnc::DateOut

          RentalsIN, RentalsOut and RentalsEnc are your three new table occurrences of Rentals.

          On your Rentals layout, add your three new global fields to the layout and put a button next to them to perform this script:

          If [IsEmpty ( RentalsOut::ItemID ) and IsEmpty ( RentalsIn::ItemID ) and IsEmpty ( RentalsEnc::ItemId ) ]
             New Record/Request
             Set Field [ Rentals::ItemID ; Rentals::gItemId ]
             Set Field [ Rentals::DateOut ; Rentals::gDateOut ]
             Set Field [ Rentals::DateIn ; Rentals::gDateIn ]
             Show Custom Dialog ["This item is not available"]
          End If

          • 2. Re: Please help - Date Validation per Item.

            Hi, thank you very much, this is very very helpful..... you are the best~