1 2 Previous Next 19 Replies Latest reply on Jun 15, 2017 1:29 PM by leeputnam

    Rental Tracking Help

    leeputnam

      I've been trying to figure this out for some time, any help would be greatly appreciated!

       

      Scenario - A company rents tables for parties. They have 3 tables in inventory (4ft, 6ft and 8ft).

       

      A customer wants to rent the 6ft table on Saturday. Jeff creates the rental and reserves the table.

      Another customer calls and Nancy answers the phone. The customer wants to rent the 6ft table on Saturday. Nancy creates a new rental and does an inventory search. She should only see the 4ft and 8ft avalible.

       

      HOW?

       

      Tables:

      Screen Shot 2016-09-16 at 11.28.34 AM.png

      I have a system in place now, however it does not prevent other users from double booking equipment.

        • 1. Re: Rental Tracking Help
          mikebeargie

          How are you entering in line items in "Rental Line Items"

           

          You need to pre-validate BEFORE creating records there to make sure the combination of Rental::DateOfRental & Inventory::ID does not already exist there.

           

          You can do this via script easily:

          Set Variable [ $date ; Rental::DateOfRental ]

          Set Variable [ $rental ; Rental::ID ]

          Set Variable [ $item ; Rental::global_InventorySelection ]

          Freeze Window

          Enter Find Mode

          Go To Layout [ RentalLineItems ]

          Set Field [ Rental::DateOfRental ; $date ]

          Set Field [ Inventory::ID ; $item ]

          Perform Find

          If [ get(foundcount) = 0 ]

             New Record/Request/Page

             Set Field [ RentalLineItems::RentalID ; $rental ]

             Set Field [ RentalLineItems::InventoryID ; $item ]
          Else

              Set Variable [ $error ; 1 ]

          End If

          Go To Layout [ Original Layout ]

          if [ $error ]

              Show Custom Dialog [ ERROR, inventory already rented for that date ]

          Else

              Show Custom Dialog [ Item added to rental ]

          End If

           

          I'll usually use a global field to select inventory so that I can then use that as seen above to check for a record and then only write a new record if that doesn't exist.

          • 2. Re: Rental Tracking Help
            leeputnam

            That is a great idea thank you! I'll give it a try and send you an update!

            • 3. Re: Rental Tracking Help
              mikebeargie

              Sure, you may need to spend some more time adapting it for things like multi-day rentals and such, but it's at least enough to get you started.

              • 4. Re: Rental Tracking Help
                leeputnam

                Yeah, what I'm going to try is converting the date to a number and then performing a find within that range.

                • 5. Re: Rental Tracking Help
                  mikebeargie

                  GetAsNumber ( yourdatefield ) will return the number of days since 01/01/0001.

                  • 6. Re: Rental Tracking Help
                    leeputnam

                    The script would look something like this correct?

                    ---

                    Set Variable [ $datestart ; Rental::DateOfRentalstart ]

                    Set Variable [ $dateend ; Rental::DateOfRentalend ]

                    Set Variable [ $rental ; Rental::ID ]

                    Set Variable [ $item ; Rental::global_InventorySelection ]

                    Freeze Window

                    Enter Find Mode

                    Go To Layout [ RentalLineItems ]

                    Set Field [ Rental::DateOfRentalstart ;  ≤ GetAsNumber ( $datestart ) ]

                    Set Field [ Rental::DateOfRentalstart ;  ≥ GetAsNumber ( $dateend ) ]

                    Set Field [ Inventory::ID ; $item ]

                    Perform Find

                    If [ get(foundcount) = 0 ]

                       New Record/Request/Page

                       Set Field [ RentalLineItems::RentalID ; $rental ]

                       Set Field [ RentalLineItems::InventoryID ; $item ]
                    Else

                        Set Variable [ $error ; 1 ]

                    End If

                    Go To Layout [ Original Layout ]

                    if [ $error ]

                        Show Custom Dialog [ ERROR, inventory already rented for that date ]

                    Else

                        Show Custom Dialog [ Item added to rental ]

                    End If

                    ----

                     

                    Would I need to convert the "Rental::DateOfRentalstart" to a number or will the find do that?

                    Set Field [ Rental::DateOfRentalstart ;  ≤ GetAsNumber ( $datestart ) ]

                    Set Field [ Rental::DateOfRentalstart ;  ≥ GetAsNumber ( $dateend ) ]

                    • 7. Re: Rental Tracking Help
                      mikebeargie

                      The find will take care of it, no need to transform it into a number.

                      • 8. Re: Rental Tracking Help
                        leeputnam

                        Can you tell me why you used "freeze window"?

                        • 9. Re: Rental Tracking Help
                          mikebeargie

                          because without it, the user would see all of the other script steps that come after it. basically a lot of flashing that is unnecessary since you're scripting it.

                          • 10. Re: Rental Tracking Help
                            leeputnam

                            UPDATE -

                             

                            Performing a find for any records that fall within the date range of the rental works, BUT it doesn't work if the previously booked rental has a date range larger than the new rental.

                             

                            Example:

                            The script performs the find:  (> Start Date) AND (< End Date)

                            As long as the already booked rental falls with in that range the script will stop and prevent the user from checking out the item. This does not work if the previous rental is out side of the new rentals date range. For example if the new rental is only for one day, and the previous rental is for a week. The equipment is not in stock, BUT FM thinks it is.

                             

                            Solution:

                            In Google Sheets I wrote a check sum that will check each day surrounding the previous rental. This works but I can't figure out how to write the formula into a FM script. Would to happy to share the document with anyone.

                             

                            HELP!

                            • 11. Re: Rental Tracking Help
                              greatgrey

                              You have an appointment/hotel booking type problem.

                              You need to check if

                              [  (Wanted_start_date/time => Rented_start_date/time and  Wanted_start_date/time =< Rented_end_date/time)

                              Or

                              (Wanted_end_date/time => Rented_start_date/time and Wanted_ end_date/time =< Rented_end_date/time)   ]

                              edit PS

                              Also I would use a field that IDs each rental record as active or inactive. mark as inactive once return or time period is past.

                              Then you only need to search for active records for the 6ft table, and then only have to test the active records for a conflict.

                              • 12. Re: Rental Tracking Help
                                leeputnam

                                Hi greatgrey, thanks for your reply. However this doesn't work.

                                 

                                Here is the example. The Number next to the days is just the numerical version of the date.

                                 

                                NEW RENTAL
                                Start Date1/3/201742738
                                End Date1/19/201742754

                                 

                                PREVIOUS RENTAL
                                Start Date1/10/201742745
                                End Date1/12/201742747

                                Using your formula Filemaker would allow me to book this rental.

                                 

                                In google sheets I wrote a formula that works, but I can't figure out how to write it into FileMaker. I need to some how run the script checking each day of the rental with the start and end date of any previous rentals.

                                 

                                Any thoughts?

                                • 13. Re: Rental Tracking Help
                                  greatgrey

                                  Need to add one more check

                                  [  (Wanted_start_date/time => Rented_start_date/time and  Wanted_start_date/time =< Rented_end_date/time) /* is start in already booked time */

                                  Or

                                  (Wanted_end_date/time => Rented_start_date/time and Wanted_ end_date/time =< Rented_end_date/time) /* is end in already booked time */

                                  Or

                                  (Wanted_start_date/time < Rented_start_date/time and  Wanted_ end_date/time > Rented_end_date/time] /* does it span already booked time */

                                  • 14. Re: Rental Tracking Help
                                    mikebeargie

                                    So are you using my earlier example or are you putting your find requests in the "perform find" script step.

                                     

                                    Where I mentioned this before:

                                    Enter Find Mode

                                    Go To Layout [ RentalLineItems ]

                                    Set Field [ Rental::DateOfRentalstart ;  "≤" & $datestart ]

                                    Set Field [ Rental::DateOfRentalstart ;  "≥" & $dateend ]

                                    Set Field [ Inventory::ID ; $item ]

                                    Perform Find

                                    Using sentences to speak this out, you are searching for Rental Line Items where a start date is matching the first set field AND where it matches the second set field.

                                     

                                    If you want to do OR searches, you need to use the New Record/Request script step to separate each search condition, like so:

                                     

                                    Enter Find Mode

                                    Go To Layout [ RentalLineItems ]

                                    Set Field [ Rental::DateOfRentalstart ;  "≤" & $datestart ]

                                    Set Field [ Rental::DateOfRentalstart ;  "≥" & $dateend ]

                                    Set Field [ Inventory::ID ; $item ]

                                    New Record/Request/Page

                                    Set Field [ Inventory::ID ; $item ]

                                    Set Field [ etc... ]

                                    Perform Find

                                    You can use "New Request" to create many "OR" search conditions.

                                     

                                    Additionally, you can add an "Omit Record" toggle that will omit matches records from your search like so:

                                    Enter Find Mode

                                    Go To Layout [ RentalLineItems ]

                                    Set Field [ Rental::DateOfRentalstart ;  "≤" & $datestart ]

                                    Set Field [ Rental::DateOfRentalstart ;  "≥" & $dateend ]

                                    Set Field [ Inventory::ID ; $item ]

                                    New Record/Request/Page

                                    Omit Record

                                    Set Field [ Rental::DateOfRentalstart ; "*" ]

                                    Perform Find

                                    That one would omit any records that have a DateOfRentalStart currently filled in.

                                     

                                    I would highly recommend you read through the querying section of the filemaker training series so you understand the difference. Find mode in FileMaker is much more powerful than simple calculations in google sheets, and having full command over it will help you.

                                    1 2 Previous Next