1 2 Previous Next 17 Replies Latest reply on Sep 25, 2013 9:26 AM by philmodjunk

    Equipment Rental: Check to See if Equipment is Booked

    MatthieuBelanger

      Title

      Equipment Rental: Check to See if Equipment is Booked

      Post

           Hey, really need some help with an issue.

           I am making a equipment rental database for a local film co-op. I have a rental form set up, and everything is working except one thing. If someone goes to reserve an item on dates that overlap with another item, I would like message to come up saying the item is not available on those dates. I tried following the solutions in the forum below, but couldn't get them to work.

           http://forums.filemaker.com/posts/ade213beb3

           The forum is a few years old, so I thought I'd start a new topic.

           A few details. You can book equipment out in advance.

           I have a table for the equipment, for the rental form, and then for the list items.

           Any posible solutions? 

            

        • 1. Re: Equipment Rental: Check to See if Equipment is Booked
          philmodjunk

               The method I described in that forum should work for you, but I've since learned of a simpler method for handling the date ranges.

               You can specify a date range in two fields where you specify first and last date, then generate a list of dates in a text field separated by returns. If startdate = 8/1/2013 and enddate = 8/3/2013, the list in the text field would be:

               8/1/2013
               8/2/2013
               8/3/2013

               In a relationship, return separated lists of values like this match if any one of the listed values match the value in the related table's match field. Thus, you can check for overlapping date ranges with a single relationship instead of the 3 specified in that other forum thread:

               Table1::DateList = LineItems::DateList and
               Table1::_fkItemID = LineItems::__pkItemID

               If any date in Table1::DateLIst matches any date in LineItems::DateLIst AND the items ID's also match, then the date ranges overlap and that item is not available. In many cases, the fields shown on the Table1 side of this example relationship are given global storage. You specify a date range, then select an itemID and the system immediately tells you if the item is available. If it is, add it to the lineitems for your rental record, if not, select a different item or change the date range specified.

               I believe that if you google "nightwing" and "FileMaker" you can find a site where there is a demo file of this concept, but I am going by imperfect memory here so I could be wrong.

               PS. to get the list of dates for a daterange, you can use either a custom function (requires FileMaker Advanced to create and install in the file) or a script.

          • 2. Re: Equipment Rental: Check to See if Equipment is Booked
            MatthieuBelanger

                 I'm new with the program, so I appologize if I am not using the right terminology.

                 I managed to get my DateList fields set up in my two tables, but I don't know how to proceed with the second part.

                 

            In a relationship, return separated lists of values like this match if any one of the listed values match the value in the related table's match field. Thus, you can check for overlapping date ranges with a single relationship instead of the 3 specified in that other forum thread:

                 

            Table1::DateList = LineItems::DateList and
                      Table1::_fkItemID = LineItems::__pkItemID

                 

            If any date in Table1::DateLIst matches any date in LineItems::DateLIst AND the items ID's also match, then the date ranges overlap and that item is not available. In many cases, the fields shown on the Table1 side of this example relationship are given global storage. You specify a date range, then select an itemID and the system immediately tells you if the item is available. If it is, add it to the lineitems for your rental record, if not, select a different item or change the date range specified.

                 As soon as I add the second relationship between my RentalForm table my LineItem table, my drop down box for my line items stops working. How do I get Filemaker to check that there is no overlap between the two DateList fields? Do I run a script trigger everytime the box is modified? If so, what would that script be.

            • 3. Re: Equipment Rental: Check to See if Equipment is Booked
              philmodjunk

                   It's not entirely clear how you have designed your database thus far--what tables and relationships exist nor how your layouts are designed. And the method I sketched out in general terms can be implemented in more than one way.

                   A script trigger should not be needed unless you are using a script to build the return separated lists of dates.

                   But you'll need to describe things in much more detail.

                   I can share this possibly useful hint: The boxes in Manage | Database | Relationships that you use to link tables in relationships are NOT tables. They are Table Occurrences. You can create multiple table occurrences that all refer to the same data source table. This then allows you to create and use multiple relationships between the same two tables. You may need more than one relationship here so that the relationship used to check an item's availability can be kept separate from others.

                   To learn more about Table Occurrences: Tutorial: What are Table Occurrences?

              • 4. Re: Equipment Rental: Check to See if Equipment is Booked
                MatthieuBelanger

                     Ok, I have three table set up.

                     Assets: in which each piece of rental equipment is listed with a unique AssetId.

                     RentalHeader: Where the information for the equipment rental is stored (name, start date, end date). Each has a unique RentalId.

                     RentalLineItems: Where the information for each item rented is stored.

                     I then have a portal on the Rental header that leads back to the Rental_Line_Items, with a button that let's me create a new line items. There is then a drop down box with a listing of the items that links back to the assets table. 
                      

                     The relationships I have set are as follows (AssetId being unique to each item, RentalId being unique to each individuals rental):

                     Assets::AssetId = RentalLineItems::AssetId

                RentalHeader::RentalId = RentalLineItems::RentalID

                     I have created a DateList field in both RentalHeader and RentalLineItems, but simply creating another relationship between RentalHeader::DateList = RentalLineItems::DateList doens't seem to effect anything.

                     Can you spot what might be the issue.

                • 5. Re: Equipment Rental: Check to See if Equipment is Booked
                  philmodjunk

                       And what is it that you want to do for checking for availability?

                       Do you want to set up RentalLineItems::AssetId with a conditional value list that only lists available assets?

                  Or do you want to use some other method of checking for available assets? (The value list of AssetID's may not be very user friendly for large numbers of Assets, but it's a good starting point for working up more sophisticated alternatives.)

                  • 6. Re: Equipment Rental: Check to See if Equipment is Booked
                    MatthieuBelanger

                         My number one choice would be for a message to pop up on screen saying that the item is unavailable for those set of dates, but at this point I would probably settle for for the the value list only showing items that are available.

                    • 7. Re: Equipment Rental: Check to See if Equipment is Booked
                      philmodjunk

                           Popping up a message would use a script and would require a relationship such as:

                      RentalLineItems::AssetId = RentalLineItems|Availability::AssetID AND
                           RentalLineItems::DateList =
                      RentalLineItems|Availability::DateList

                      This is a single relationship with two pairs of match fields. It also requires that RentalLineItems::DateList be a stored, indexed field (It can't be an unstored calculation field) and RentalLineItems|Availability would be a second table occurrence of RentalLineItems. (See the link I posted previously to learn more about table occurrences.)

                      The script would look like this:

                      If [ Count ( RentalLineItems|Availability::AssetID ) > 1 ]
                              Show Custom Dialog ["Item is not available." ]
                           End IF

                      You have to check for a count greater than 1 as this relationship will match the record to itself so you need a count that shows that it matches to more records than just itself to reveal a conflict in the dates.

                           Personally, I would think that a value list that only lists available items would be more user friendly than selecting different asset ID's only to be told by the script that the asset isn't available.

                           A search portal of available Assets might be even more user friendly as it could be filtered to bring up all Available Assets of a particular type or matching a partial description so that you can select one from those that are available.
                            

                      • 8. Re: Equipment Rental: Check to See if Equipment is Booked
                        MatthieuBelanger

                             Thanks so much for your help so far. I feel like I am so close, but one more issue.

                             The warning is only being triggered if start dates happen to be the same, and not if any days overlap.

                             For example, if equipment A is booked Aug. 4th - 10th,  a second booking from Aug. 4th to 7th will trigger the script, but a booking from Aug. 5th - 9th won't.

                             I checked DateList and it seems to be generating return sperated lists. DateList is also indexed. Here is the custom function I am using to create the DateList:

                             
                                  Let([Fn = GetAsDate(From); Tn = GetAsDate(To)];
                             
                                  If(Tn > Fn; Fn & ¶ & DateRange(Fn + 1; Tn); Tn)
                             
                                  )
                             
                                   
                             Then the DateList calculation (this is set to output to text). 
                             
                                  DateRange ( Date Booked ; Return Date )
                             Do I need to add something for the DateList to return in the proper format?
                              
                        • 9. Re: Equipment Rental: Check to See if Equipment is Booked
                          philmodjunk
                               

                                    The warning is only being triggered if start dates happen to be the same, and not if any days overlap.

                               Then your date list fields do not have the correct list of dates. You may recall that I indicated that if the start date was 8/1/2013 and the end date was 8/3/2013, you'd need to have three dates in the field:

                               8/1/2013
                               8/2/2013
                               8/3/2013

                               and you'd need to populate this field with the list of dates via a script or a custom funciton.

                          • 10. Re: Equipment Rental: Check to See if Equipment is Booked
                            MatthieuBelanger

                                 The DateList field is being populated with all the dates.

                                 If I select August 22 to the 23, it is populated with:

                                 2013-08-22

                                 2013-08-23

                                 I included the custom function I am using is :

                                 Let([Fn = GetAsDate(From); Tn = GetAsDate(To)];
                                 If(Tn > Fn; Fn & ¶ & DateRange(Fn + 1; Tn); Tn)
                                 )
                                  
                            • 11. Re: Equipment Rental: Check to See if Equipment is Booked
                              philmodjunk

                                   Then it should work. Make sure that your data types are correct and that you don't have an unstored calculation.

                                   I use this custom function:

                                   /DateRangeList ( DateStart ; DateEnd )
                                   //
                                   //Returns a list of return separated dates from DateStart to DateEnd
                                   //
                                   //DateStart  :   Date--first date of list
                                   //DateEnd    :   Date--last date of list
                                   //
                                   Case ( IsEmpty (DateStart ) ; "" ;
                                              IsEmpty ( DateEnd ) ; DateStart ;
                                              DateStart > DateEnd ; DateStart ;
                                              DateStart = DateEnd ; DateEnd ;
                                              List ( DateStart ; DateRangeList ( DateStart + 1 ; DateEnd ) )
                                            )

                              • 12. Re: Equipment Rental: Check to See if Equipment is Booked
                                MatthieuBelanger

                                     Got it to work!

                                     The issue was the > 1 in the script.

                                     

                                If [ Count ( RentalLineItems|Availability::AssetID ) > 1 ]
                                             Show Custom Dialog ["Item is not available." ]
                                          End IF

                                      

                                     This was allowing for 1 record to have overlaping dates. The starting dates thing was just a coincidence. I changed the > to a greater than or equal, and everything is working great.

                                     Thank you so much for all your help.

                                • 13. Re: Equipment Rental: Check to See if Equipment is Booked
                                  philmodjunk

                                       Good catch!

                                       But I'm a bit surprised. I would have expected a given record to match to itself and thus a conflicting item would produce a count greater than 1. I suggest very carefully checking to see if you are doing this check before or after committing the record as that could explain the result that you are getting. (You want to avoida situation where this works part of the time and not in other cases simply due to whether or not the record has been committed--say by clicking on a blank area of the layout.

                                  • 14. Re: Equipment Rental: Check to See if Equipment is Booked
                                    Corné

                                         Hi Matthieu, Phil,

                                         Don't mean to hi-jack your post, but I would like to extend the question as follows:

                                         When the asset list is shown in a layout with the corresponding table,  I would like to hi-lite the items that are currently not available with a Conditional Formatting function. This is when a return date is not entered in the corresponding field. I try to make a relationship where RentalLineItems::AssetID = Assets::AssetID AND RentalLineItems = "" but ofcourse the latter equations is not possible. Hope you can help me out?

                                         Best, Corné.

                                    1 2 Previous Next