4 Replies Latest reply on Jun 15, 2010 7:20 AM by smkoonjul

    Tracking Daily Reservations

    smkoonjul

      Title

      Tracking Daily Reservations

      Post

      I'm building a lending database (FM 10) and have integrated a calendar system (seed code) to make reservations for equipment.

       

      I have one table for Transactions (that is, check outs/check ins) and one table for Reservations. What I want to make now is a Quick Sheet that will show all the Equipment and tells you the check out status and whether it's reserved for the Current Day or not.

       

      The first part is easy (Check Out Status on Equipment Table, if Equipment is checked out, Check Out Status = Out, if not, = In)

       

      But I'm not really sure how to track the second one.

      I tried making a field in equipment (Reserved Today). My thought was if a record in Reservations has the same start date as the current date, then I could have a field for the equipment that said it was Reserved Today. Calc:

       

      If (  GetAsNumber(Reservations::DateStart) = GetAsNumber(Get(CurrentDate)) ; "Yes" ; "No" )

       

      but I'm not getting the results I want. The dates from DateStart don't seem to be carrying into the Equipment Table to well...

      Reservations::DateStart is related to Equipment::Reserved Today, and so is Item ID.

       

      Is there a better way to go about this?

       

      Thanks,

      SK

        • 1. Re: Tracking Daily Reservations
          philmodjunk

          It would help to have a clearer picture of each table involved and how equipment is reserved. Just one example where info is lacking, when an item is reserved for 3 days, does that result in three reservation records for it--each marked with a different date or is there just one record with two dates to specify the range of dates for which it is reserved?

          • 2. Re: Tracking Daily Reservations
            smkoonjul

            Let's see:

             

            File is composed of tables:

            Users

            Reservations

            Equipment

            Transactions

            Reports

            Calendar

             

            The calendar is used for reservations. A new record in Reservations is created with the user id, the item they've reserved, and the day that they've reserved it. Each reservation is separate, so user 1 reserves camera 1 on monday is one record, user 2 reserves camera 2 on tuesday is a separate record.

             

            I've made it so that equipment can not be reserved by two different people on the same day, summary field: date + item id which is unique.

             

            When a consultant opens a reservation record, they can check that item out to the user who reserved it right from there, it creates a new transaction and the item is marked out.

             

            Alternatively, a consultant can use a check out script outside of reservations, which makes sure that the item isn't reserved by a different user. Script:

             

            Go to Layout [“Check Out” (Transactions)]
            Sort Records [Specified Sort Order:Transactions::UserID;ascending]
            [Restore; No dialog]
            New Record/Request
            Set Field [Transactions::CCon Out; Get ( UserName )]
            Go to Field [Transactions::CMUID ]
            Pause/Resume Script [Indefinitely]
            Set Field [Transactions::UserID; Users::UserID]
            Set Variable [$$tu; Value:Transactions::UserID]
            Set Variable [$$t; Value:Transactions::SummaryCheckTransactions]

             

            Run some checks whether user has access, equipment is available, etc

             

            Go to Layout [“Event Details” (Reservations)]
            Set Error Capture [On]
            Enter Find Mode [  ]
            Set Field [Reservations::SummaryCheck; $$t]
            Perform Find [  ]
            If [Get(FoundCount) > 0]
            Enter Find Mode [  ]
            Set Field [Users::UserID; $$tu]
            Constrain Found Set [  ]
            If [Get(FoundCount) > 0]
            Go to Layout [“Main Record” (Transactions)]
            Enter Browse Mode
            Refresh Window

            Fill in the rest of the transaction, info like date due, time, etc

            Changes Equipment Checkout Status to Out


            Else If [Get(FoundCount) = 0]
            Go to Layout [“Home” (CalendarInterface)]
            Show Custom Dialog [Title:"Message"; Message: "This item is reserved by someone else."; Buttons:“OK” ]
            Go to Layout [“Main Record” (Transactions)]
            Delete Record/Request
            [No dialog]
            Go to Layout [“Home” (CalendarInterface)]
            Exit Script [  ]
            End If
            Else If [Get(FoundCount) = 0]
            Go to Layout [“Main Record” (Transactions)]
            Enter Browse Mode
            Refresh Window

             

            Fill in the rest of the transaction, info like date due, time, etc

            Changes Equipment Checkout Status to Out

             

            End If

             

             

            Hopefully this gives a better idea of what's happening.

             

            An item can only be reserved for one day at a time, so there really is only one date per record to worry about.

             

             

















            • 3. Re: Tracking Daily Reservations
              philmodjunk

              A key part of good database design is properly linking your tables with relationships and that's not really given in that last post.

               

              I think you are using Reservations to reserve each item of equipment for one day.

              Transactions are used to record when an item enters/leaves inventory (Items are "checked out" and "Checked in") It's not clear whether checking an item in updates the record that checked the item out or if you create a new record to check it back in.

               

              You may have the following relationships:

               

              Equipment::ItemID = Reservations::ItemID

              Users::UserID = Reservations::UserID

               

              Likewise in Transactions you might have:

              Equipment::ItemD = Transactions::ItemID

              Users::UserID = Transactions::UserID

               

              You mentioned a calculation (not a summary) field date + item id but don't name it. Is this called summaryCheck? Is it defined identically in Transactions and also in Reservations?

               

              You also haven't indicated what version of filemaker you are using.

               

              Let's assume you have the above relationships. If you are using filemaker 11, you could set up a one row  filtered portal to reservations on a list view of your equipment records using Get ( CurrentDate ) = Reservations::ReservationDate as the filter expression.

               

              Place one field such as Reservations::ReservationDate in the portal and any item of equipment that is reserved will show a date in the portal. Available equipment will show this portal as empty.

               

              You can get the same effect with older versions of filemaker, but you have to set up a separate relationship and include a pair of date fields in the relationship:

              Equipment::ItemID = TodaysReservations::ItemID AND

              Equipment::cToday = TodaysReservations::ReservationDate

               

              Where cToday is the unstored calculation field, get ( CurrentDate ) set to return a date and TodaysReservations is a table occurrence of Reservations.

               

              • 4. Re: Tracking Daily Reservations
                smkoonjul

                Hi Phil,

                 

                Thanks for the post, your assumptions are all correct.

                 

                Checking In updates the record that checked the item out.

                 

                Equipment::ItemID = Reservations::ItemID

                Users::UserID = Reservations::UserID

                Equipment::ItemD = Transactions::ItemID

                Users::UserID = Transactions::UserID

                 

                All true

                 

                In Transactions I have SummaryCheckTransactions as: GetAsNumber(Get(CurrentDate)) & " | " & Item ID

                In Reservations, SummaryCheckReservations is: GetAsNumber(DateStart) & " | " & ItemID

                As long as those are the same value, the Check Out can continue.

                 

                I did write in my original post I was using FM 10.

                 

                 

                I should have clarified my relationships in earlier posts. The first time I tried this I created a new table occurence for Equipment and related

                Reservations::ItemID =Equipment2::ItemID

                Reservations::DateStart = Equipment2::ReservedToday


                That's probably why I was having issues...

                 

                Now:

                Created a new table occurence of Reservations and got the setup to work, I have a field Reserved Today that is calculated as

                If (TodaysReservations::DateStart = ""; "Available"; "Reserved")

                 

                Then created a layout with Equipment that puts the equipment in a list view with checkout status and Reserved Today. Add some conditional formatting to change the color of the items that have a checkout status of Out or that have a Reserved today as "Reserved" to red. Easy way for a consultant to see which items are in and available, and which aren't.

                 

                Thanks again for all the help.
                SK