I'm guessing that you have these relationships:
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.
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:
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.
This EnhancedValueSelection concept is very good, this seems like a practical approach.
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.
It currently uses barcodes to scan items in / out! Works well, have you ever incorporated a barcode scan search into a search portal before?
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.