6 Replies Latest reply on Aug 14, 2013 10:28 AM by philmodjunk

    Asset bookings

    MusicAndPerformingArtsDept

      Title

      Asset bookings

      Post

           Hi peoples. 

           I have a basic database with 3 tables: Assets, Contacts and History. Currently, a contact can borrow multiple assets, creating a history record with contact ID, time / date in / out and asset ID. To borrow equipment contacts scan their ID card, then multiple asset bar codes. This seems to work quite well. 

           I would really like to add the ability to book equipment in advance but I am unsure of the best way of doing this, as one contact will often borrow up to 25 assets in one instance. I have looked at the way the invoice templates work, but a value list does not seem to be viable when there are well over 500 asset records. I am open to any suggested methods of adding the ability to book equipment. 

        • 1. Re: Asset bookings
          philmodjunk

               I'm guessing that you have these relationships:

               Assets-----<History>----Contacts

               Booking equipment in advance for intervals of more than a Day can be a challenge. One person might reserve an asset for 8/20/2013 to 8/22/2013 and then, if another person attempts to reserve the same asset for 8/21/2013, your system has to detect the fact the the asset is unavailable.

               You can set up 3 different relationships to History, each testing a different way that you date interval might overlap, but as you can imagine, that get's pretty complex. A better approach might be to represent date ranges as a return separated list of dates from Date Checked out to Date Returned. A relationship can match a field with a user's requested list of such dates against such a field in History to see if the asset is free over that entire date interval.

               

                    but a value list does not seem to be viable when there are well over 500 asset records.

               Well that would depend on how you design your value list. There are a number of ways to trim large value lists down to a more manageable size. If you can group your assets into categories, you can set up a conditional value list where you select the category and then the list of assets updates to only list assets from that category.

               If you are unfamiliar with conditional value lists and want to try that method, let me know and I'll respond with some links on the subject.

               You can also set up a name based value list that can then auto-complete and a relationship looks up the assetID value to auto-enter it. A script can manage the process to handle situations where you have two items in your value list with the same name.

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

               And you can also set up a "search portal" that uses partial text matching to produce a list of assets in a portal where each keystroke reduces the list of assets in the portal and where you click a portal row to select that item.

          https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

          • 2. Re: Asset bookings
            MusicAndPerformingArtsDept

                 Hey Phil, I did consider a conditional value list as the assets are in categories, however we have many instances of the same Item name (EG. Over 30 pairs of identical headphones), hence even with a conditional value list it is only practical to refer to assetID. 

                 Yes these are the current relationships: 

                 Assets-----<History>----Contacts

                 Also, the equipment would ideally be reserved for specific time slots, just to make it even more challenging. I like the idea of a search portal, but I have never set one up (I'll do a bit of research on this). 

                 I don't know if this would be a good or a bad idea but I considered setting up another table 'Bookings' with date in/out time in/out, then just adding a calculation to the existing checkout script to compare the current date/time vs the asset's booking schedule. 

                 This is jumping ahead a little, but in the instance of creating a 'Bookings' table I would also like to be able to scan the BookingID barcode to checkout the associated list of assets, that would be really cool. 

            • 3. Re: Asset bookings
              MusicAndPerformingArtsDept

                   This EnhancedValueSelection concept is very good, this seems like a practical approach. 

              • 4. Re: Asset bookings
                philmodjunk

                     however we have many instances of the same Item name (EG. Over 30 pairs of identical headphones), hence even with a conditional value list it is only practical to refer to assetID.

                     Sounds like any selection option you use will run up against that issue--even the selection portal. One solution is to put bar code stickers on all your assets and then you can set up a bar code reader with FileMaker to scan items in and out.

                • 5. Re: Asset bookings
                  MusicAndPerformingArtsDept

                       It currently uses barcodes to scan items in / out! Works well, have you ever incorporated a barcode scan search into a search portal before? 

                  • 6. Re: Asset bookings
                    philmodjunk

                         There should be no need to do a barcode scan search through a search portal as the barcode should be all you need to uniquely identify the item. But for non barcode searches/checking out and in, then I see no reason why you can't also put a search portal on the same layout. But scanning barcode data into the search field of such a portal makes no sense.