Nested Find Requests

[dukudee]

FileMaker documentation seems to suggest that one cannot nest find requests. If this is true, how would I write a script that does class scheduling using the start and end dates, start and end time of the class, the meeting days of class with a limited number of rooms. Does any one have any ideas.

Thank you

[Chuck]

I'm afraid that what you've given us isn't much to go on, so I'll offer some advice on constructing the algorithm in general.

Do you know how you would do this manually? Write out the steps you would take, one step at a time. Then try to duplicate as close as possible the steps in ScriptMaker. Could you do it yourself if you had access to the data in FileMaker? Then you need only take what you would do and automate it.

Once you have a basic algorithm going, you'll need to go back and take a second look at it. What could go wrong? How should the script handle things when something does go wrong?

If you provide more information on exactly what it is that you're trying to do, we might be able to offer more concrete advice.

Chuck

[dukudee]

Thanks for your reply Chuck, I'll try and be clearer.

Here is what I want to do. I have a number of rooms that I want to use for classes and special events. When I receive a request for a special event on a given day, or for instance a class of a certain size to be scheduled on Mondays and Wednesdays from 9 AM to 11 AM from January to March , I choose a room that is suitable for the event or the class. Then I need to make sure that the room is available on those days at that time interval during that period.

What I wanted to do was find all the bookings in that room first, then find those that fall on Mondays and Wednesday, then those that fall between January and March and finally from 9 AM to 11 AM. If the find returns 0 then I know that it is OK to schedule the room for those days and times. If the find does return something, I know that there is a conflict and try different days or times.

This is the idea, but how best to do it in Filemaker so that it does not take a long time to process the records is my problem right now.

I am also not sure how to write script to detect conflicting periods and times.

Thank you for any helpful ideas.

[Vaughan]

There *is* a way to nest Find requests. After the first Find, use the "Modify Last Find" command from the Records menu. Edit or add to the search criteria, then click Find. Use the "Modify Last Find" command to edit add additional criteria as often as you like.

This can be scripted, too.

[Chuck]

It doesn't sound like you need to nest the finds, but can simply do the find all at once.

Enter find mode in the database that tracks the bookings for a room. In the room field, enter the room you want to search on. In the day field enter Monday. In the beginning time field enter >=9:00 am. In the end time field enter <=11:00 am. In the beginning date field enter >=1/1/02. In the end date field enter <=3/31/02.

Now duplicate the find request. In the new find request change the day field from Monday to Wednesday.

This sort of find should locate any existing records that you're looking for.

Writing a script to detect conflicting time periods is more complex. Go back to my original reply. How would you do this manually? Well, you'd take a record that had a booking in it and, one by one, compare that booking to the rest of the bookings and if there was a confict perform some action, mark the conflicting record or delete it, whichever you need.

So that's what you would do with a script.

Go to a record in the database. Record all the pertinent information into a set of global fields. Loop through the records and see if any of the other records have conflicts.

You can pare this down quickly by first searching for bookings with the room you're concentrating on.

Exactly how you test all of this will depend on how you're storing the information.

Chuck

[dukudee]

Thanks for your reply Chuck.

A slight problem with entering >= 9:00 AM and <= 11:00 AM is that if I have a class scheduled from 8:30 AM to 11:30 AM, it will not be caught. Which brings me to the conflicting time period again. I have found that if I do it manually,

Enter find mode

Enter < 11:00 AM in the start date field

Enter > 9:00 AM in the end date field

Perform find

I catch all the classes that conflict with this class. What this means is any class that begins before my scheduled class ends or any class that ends after my scheduled class begins is a potentially conflicting class if it is on the same day and in the same room with my scheduled class.

Now this works manually, but all my attempts to script it fail. Here are the things that I have tried.

1- Insert calculated result [select, gStartDate, "DateToText(today)"&"..."&DateToText(Classes::GlobalEndDate)]

Insert calculated result [select, gEndDate, "DateToText(Classes::GlobalStartDate)"&"..."&DateToText(gFutureDate)]

gFutureDate is a for instance "1/1/2050" to help me capture any class that ends from the scheduled class's start date onwards. This was done so I can get a range of dates because I was getting error messages when I attempt to use the symbols in a calculation. When I put a halt script step to see what was being put in the start date and end date fields, the start date came out find, but the end date did not work, nothing was inserted for future date. I wonder why.

I have also tried set field and set text, but neither of them worked for me.

My question now is how to use symbols like < and > in a script without error message?

I have searched the archives and found that some people suggested to use stuff like paste litteral for date range search, but I could not find "paste litteral as a script step", is it obsolete or am blind? I am using FM 5.0

As usual thanks for any help, I wonder what newbies like me would do without this forum.

[LiveOak]

We do scheduling based upon time slots using compound multi-keys. For instance, if you scheduled by hours the day would be broken into 24 time slots, half- hours 48, quarter hour 96, etc. The compound key would be:

1/1/2000-5

a date and a slot. Multiple days and slots would be done with a multi-key:

1/1/2000-5

1/1/2000-6

1/1/2000-7

1/2/2000-5

1/2/2000-6

1/2/2000-7

This would represent the same three time slots over two days. You wouldn't use finds. You would build a left side compound multi-key in a global field based upon the desired search criteria and do a "go to related record".

-bd

[Chuck]

LiveOak's suggestion for the multi-key solution might be best for you.

Paste Literal has been changed to Insert Calculated Result. FileMaker decided to change the name of it with version 5.

Chuck

[Fitch]

If you can stand Yet Another Idea:

Use a Dates file, related to a Bookings file that contains room and time info.

Make a portal in your Dates file, and when you search for a date, you'll see all the bookings in a portal. The portal could be sorted by room or time, or you could use a global field to "filter" it. I could elaborate more, but my posts have gotten long and boring lately. Here's a lame little example file.

[dukudee]

I want to thank everyone for the suggestions.

Here is a follow-up question for LiveOak's compound multi-key idea. First, I do not get the left side part of it.Please explain what that means. If I do what you suggest, does that mean if someone books a room for several days, we will need to enter all the corresponding time slots in one compound multi-key. If yes do you have any ideas on how I would handle someone who wants the room for Mondays and Wednesday for March and April 2002 from 9:00AM to 11:00AM? Would I need to get a calendar and figure out what dates are involved to construct the compound key? These are the things that are not clear in my mind on the approach that you suggest, but it seems very interesting.

Thank you

Duks