A little help about dates...please?

[FMAsker]

Hello i am a new user of FileMaker and i want to ask you something about dates.

Firstly i try to make a script with ScriptMaker but without success. I try to make a script that when i complete two fields of date tell me that found or is available. For example if i searching for 15/8/2003 - 20/8/2003 and i have a date in record from 15/8/2003 - 19/8/2003 tell me that is not or available in contrast if i have a record in 15/8/2003 - 20/8/2003 and search for 15/8/2003 -21/8/2003 is available not for this date but later. Is it difficult that ?

Thank you very much in advance.

FMAsker

[RussBaker]

If you are searching for records which have a date that lies within a range, the best way to do it is to create 2 global date fields, g_StartDate and g_FinishDate.

Then either include these on your existing layout, or (better) create a new layout that you will use for finding records that lie within this date range. This layout will need the 2 globals fields plus your date field. You will also need an "abort" button to take the user back to the normal view in case they decide they don't want to do the find.

Then, the basic script you will need (make sure you un-check the restore/pause options on both find steps) is:

Enter Find Mode[]

Insert Calculated Result [Date,DateToText(g_StartDate) & "..." & Date To Text(g_FinishDate)]

Perform Find[]

Go To Layout [Whatever layout you choose to display the found records]

Then you can get a bit fancier by dealing with situations where one or both of the global fields is empty - like, making it easier for the user by designing it so that if there is a start date but no finish date, then the script will search for all dates greater or equal to the start date etc. You could then end up with:

Set Error Capture [On]

If [isEmpty(g_StartDate) and IsEmpty(g_FinishDate)]

. Show Message[You have not entered any find criteria][OK]

. Go To Layout [your normal form layout]

Exit Script

End If

Enter Find Mode []

If [isEmpty(g_StartDate)]

. Insert Calculated Result [Date,"=<" & Date To Text(g_FinishDate)]

End If

If [isEmpty(g_FinishDate)]

. Insert Calculated Result [Date,">=" & Date To Text(g_StartDate)]

Else

. Insert Calculated Result [Date,DateToText(g_StartDate) & "..." & Date To Text(g_FinishDate)]

End If

Perform Find []

If [status(CurrentFoundCount) = 0]

. Show Message [There are no records in that date range][OK]

. Show All Records

. Exit Script

End If

If [status(CurrentFoundCount) = 1]

. Go To Layout [a single form layout of the records designed to show one record]

Else

. Go To Layout [a list layout of the records designed to show multiple records]

End If

[FMAsker]

Can i ask you something ? I am very newbie in FileMaker and i don't know very good about scripts and scriptmaker so can you explain me what did you mean in this code "Insert Calculated Result [Date,DateToText(g_StartDate) & "..." & Date To Text(g_FinishDate)]" especially g_StartDate and g_FinishDate ? The first(g_startdate) will be the first global field which i made and the second field(g_finishDate) the second field ? But i tried to make your script and FileMaker find "Date," like a wrong field. What must i do ? In addition "..." what is mean ? Finally this code used when we have two fields ? If i need in the future to make a pop up list which has relationship with two others how i make it ?

Sorry for all these questions, i am very newbie.

Thank you in advance.

[Ugo DI LUCA]

Hi,

Insert Calculated Result [Date,DateToText(g_StartDate) & "..." & Date To Text(g_FinishDate)]"

Date would be your field name for the date in which you insert the calculation for the find.

[BobWeaver]

Just to add to what Ugo has said, the script steps that are normally shown in this forum are more or less how they look when printed out or displayed in the scriptmaker window. When you create the script step, it is all point and click. So, in this case, you would find the "Insert Calculated Result" in the list on the left side of the script window, and double click on it. It then appears in the script listing on the right side of the window. At this point, all of the available options for this script step are shown at the bottom of the window. That is where you find the button to select the destination field, and the button to bring up the calculation formula entry dialog.

[FMAsker]

Thanks guys but I have three little problems about this code who wrote Russbaker. For example if I have book from 15/5/2003 to 20/5/2003 and search from 14/5/2003 to 19/5/2003 or from 15/5/2003 to 20/5/2003 or from 1/5/2003 to 19/5/2003 I find them. But the problem is if I search from 16/5/2003 to 21/5/2003 or from 16/5/2003 to 19/5/2003 or from 16/5/2003 to 20/5/2003 I do not find anything. Do you know how I will sell all these problems?

I would appreciate if you can tell me what I must include in my script that performs all these with success?

[danjacoby]

Let's start over.

It seems you have, in a record, a range of dates, and you want to find all records in which any part of the date range overlaps with the date range for which you are searching.

First, you need two date fields, a "StartDate" field and an "EndDate" field.

Now, when searching, you want to enter "<=" (greater than or equal to) and then the End Date into the StartDate field, and then enter ">=" and the Start Date into the EndDate field. For instance, if you are searching for 15/5/2003 - 19/5/2003, then while in Find mode enter <=19/5/2003 into the StartDate field and >=15/5/2003 into the EndDate field.

[FMAsker]

No, i am sorry it does not work. I think it should be better if in the code who wrought RussBaker, i put it on some more code like :

If [isEmpty(g_StartDate)]

. Insert Calculated Result [Date,"=<" & Date To Text(g_FinishDate)]

End If

If [isEmpty(g_FinishDate)]

. Insert Calculated Result [Date,">=" & Date To Text(g_StartDate)]

But now i want to find the right dates and don't tell that there are no records in that data range because with this way i will have a lot of problems in my job.

Can you tell another solution ?

Thank everyone

[Ugo DI LUCA]

Hi,

If what you are looking for is a way to find rooms availables during a given period of time from your reservation db, here is another method :

Create a new file called Check Available (For quick checkings, involving globals, I always use these separate files) or use your current Main file.

Put in it a :

- GLOBALCheckAvailable_StartDate

- GLOBALCheckAvailable_EndDate.

and add

- g_Hook (global num = 1)

- c_Hook = (indexed calc) = 1

In your Reservation File (that should be used as a line item with fields Room#, StartDate and EndDate)

add

- c_Hook = calculation indexed = 1

- g_Hook = global number = 1

Set a relationship called "OnetoOne" in both file using g_Hook at left and c_Hook at right.

Set a selfjoin in Reservation using g_Hook at left and c_Hook at right

Still in your Reservation File, set a calc :

- CALCCheckAvailable_DoesOverlap = ReservationStartDate < ::GLOBALCheckAvailable_EndDate and ReservationEndDate > ::GLOBALCheckAvailable_StartDate (where "::" stands for your OnetoOne relation). This still will lead to 0,1 results.

and add

c_MinCheck* = Min(selfjoin::CALCCheckAvailable_DoesOverlap)---> This calc will return for all records in the reservation a 0 if there is at least one room available, a 1 if not.

The you will have 2 options :

Option 1 :

Back in your Check Available file,

add :

n_match* (lookup from c_MinCheck through the OnetoOne relation)

c_checkavailables* (calc text) = Case (n_match = 0,"Room(s) available(s)","No room available")

and

create a script "Update"

Relookup ( g_Hook) without dialog.

Placing the 2 global date fields + the button for script + n_match (transparent) + the c_checkavailables in the layout would almost be sufficient to tell a client on the fly at the phone if there are rooms availables.

Another button would eventually lead to the portal, selecting the room for the new reservation.

This portal could use the OnetoOne relationship (records still sorted by CALCCheckAvailable_DoesOverlap)

* If you want to have the portal, keep these fields as they would allow to refresh the portal with the relookup, but you will need to keep n_match on the layout (even transparent).

Option 2 :

Make a search for any 0 in the c_MinCheck* in the Reservation file.

[danjacoby]

I dunno. I give an answer that works exactly, and am told it doesn't work. Maybe that's why I've gone from five stars to three. Not that I mind; Vaughan has three stars and deserves five, so I'm in good company.

If you really want to make the Find work for people who don't know what they're doing, create two global fields that return a date, gStartDate and gEndDate. Then create a DateFind script that goes to a layout with the global fields on it. Pause (so that they can enter the Start and End dates. Now go to a layout with the actual StartDate and EndDate fields. Set the StartDate field to "<=" & gEndDate, and the EndDate field to ">=" & gStartDate. Perform the find (uncheck "Restore find requests"). And clear the two global fields.

It works. Trust me, I've not only tested it, but I have clients who are using it.

[FMAsker]

Thank you guys, i will work the two very good solutions by dilucaugo68 and danjacoby and i will tell you my news.

Thank you very much. I appreciate that.

[FMAsker]

Hi, I would like to ask something about the code who wrote dilucaugo68. I am very newbie and I don

[Ugo DI LUCA]

Oh my god ! You want me to rewrite it again

1. These g_, n_,... aren't formulas, it's just the "fieldnames". As many in the forum, I use

g_ for a global field

c_ for a calculation

t_ for text

...

2. A selfjoin is a relationship from your file to your file. You know how to define relationship using the Menu, do you ?

3. CALCCheckAvailable_DoesOverlap is just the "fieldname" again. I just use it as it is more clear (I think) than calc1, calc2,...

4. same is for GLOBALCheckAvailable_StartDate which is a global, formated to date in which you will enter the startdate.

Let me know if this is still not clear.

[FMAsker]

I am not sure if i write correct the code but it doesn't find the dates. Can you take a look ?

test.zip

[Ugo DI LUCA]

Hi FMAsker,

I'll play with this sample and will drop you a workable solution soon.

There are a lot of errors.

Text fields instead of dates fields

Calc constant being a text

global = 0 at left and global = 1 at right....

Don't bother, you'll understand.

[FMAsker]

Hey Danjacoby,

I am sorry i have not seen your solution for so long but your message seems too strange for me because i don't understand what are you mean. If you see my message can you give me some more explanations or an attachment.

Thank you very much for your attention.

FMAsker

[Ugo DI LUCA]

Hi FMAsker,

before I go to the sample file I promised, could you be more specific?

Is the rooms example accurate

Are your rooms set by period of time (from sat to Friday for ex.) or can it be variable.

If there is a solution, using 2 rooms (1 for Mon to Thursday), 1 for Thursday to Friday), what would you like your search result to be ?

[FMAsker]

Here is an example Ugo DI LUCA:

- Hello I want a room from 15/5/2003 to 20/5/2003. Do you have any available?

- Give me a minute...

(Now I must make the search from 15/5/2003 to 20/5/2003)

- Yes we have. Do you want to book this room?

- Ok my name is FMAsker etc

(This room has booked)

If another person wants from:

14/5/2003 to 19/5/2003 or

From 15/5/2003 to 20/5/2003 or

From 16/5/2003 to 21/5/2003 or

From 16/5/2003 to 19/5/2003 or

From 16/5/2003 to 20/5/2003 or

From 15/5/2003 to 19/5/2003

My program must show me the previous booking that made FMAsker and show me a message who says that

[Ugo DI LUCA]

Hi FM Asker,

Thanks for clearing us. Yes it seems to me more complex than your first post as this depends on your database structure.

I have never worked on such databases, but it seems to me that there should be a file for each date and each rooms.

Therefore, the search would identify all rooms within the date range with no Customer Name attached to it...

I'll give it another eye....

[Ugo DI LUCA]

Hi FMAsker,

Maybe someone experienced with Reservations would go another way, but here are my thoughts :

IMHO, a Reservation/Booking system should be based on a day to day basis, as invoicing is usually based on the number of nights + services.

This means that if a customer wants to book Room#2 for 3 nights from April 23 to April 26, then you would have a file with 3 records (1 for each day).

This would be slighlty different for "packages" or "period of time (7 nights packages,...), but let consider that you are dealing with Option 1.

This file would be used as a line item for your solution, using relationships and counts based on the records from this file for every ulterior operation.

I did some tests using a clone file with 365 records * number of rooms that would be filled along with your reservations, but there may be other solutions....

- Hello I want a room from 15/5/2003 to 20/5/2003. Do you have any available?

You would need to use one of the tools or Plug-ins availables for Ranges of dates, or the SmartRanges tip. I ran a test using Bob Weaver DateRange3.fp5 (you can download it in the attachment section), and I was very impressed by its results.

1. With this tool, modifying the text fields in separate global fields, you'll get a Multi-key calculation that would be used for the left side of a relationship

c_bob'sDateList -----> Multkey calc

15/5/2003

16/5/2003

17/5/2003

18/5/2003

19/5/2003

20/5/2003 where each carriage return is used for an index.

2. A calculation returning the record date if the room is empty would be used at right side of a relationship

This calc could be MatchEmpty = Case(Is Empty (Customer_ID), date, " ")

3. Of course the relationship would be c_bob'sDateList::MatchEmpty.

In order to identify at the fly which room is available for the whole range given, one way would be to :

A- For Right Side :

Concanate the current date fields with the current Room_Id in a modified matching calculation :

The calc could be MatchEmptybis = Case(Is Empty (Customer_ID), date&" " Room_Id, " ")

B- For Left Side :

- use 5 global fields (for the 5 rooms numbers - Room1, Room2,...)

- Modify Bob's Weaver Calculation in 5 separate calcs using these globals in order to have lists :

c_Bob'sdateListforRoom1 :

15/5/2003 1

16/5/2003 1

17/5/2003 1

....

20/5/2003 1

c_Bob'sdateListforRoom2 :

15/5/2003 2

16/5/2003 2

17/5/2003 2

....

20/5/2003 2

Then, comparing the number of occurence from Bob's List (nbOccurence = Occurence(Bob'sList, " PP") + 1) to each counts for rooms(Count (Relationship c_Bob'sdateListforRoom1/2/3/4/5::MatchEmptybis) would lead to identify which room is available for the whole Range.

If no Room is available for the whole range, there could be other booking solution (1 room for the 3 first days, another for the rest of the journey).

This is where I'll use my prior solution with the Min(Relationship) returning a 0 if there are rooms available during the Range.

- MatchEmptywitha0= Case(Is Empty (Customer_ID), 0, 1)

- CheckFofAvailables(c_bob'sDateList::MatchEmpty:: MatchEmptywitha0)

Therefore, if your relationship is valid and CheckFofAvailables = 0, there are at least several rooms available for this range.

I hope this is not too confusing. Just let me know.

[FMAsker]

Yes it is complex but i will try to make it and i will tell you my news. Thank you very much....

[CobaltSky]

Hello FMAsker,

Well, you've given a few details of what it is you're trying to do, but that still leaves a lot of questions to be answered. As far as I understand, you want to be able to enter a start and end date, then see a list of those rooms which don't yet have any bookings during that time.

There are a number of ways to do that, however which method is best depends on a number of considerations such as how your database is set up, how you want the information presented, how many rooms are involved, what periods bookings may span etc etc. These are basic questions, but I don't see answers to any of them on this thread so far.

Notwithstanding that, I will offer you an example of a relatively simple type of bookings system which works on ranges (see attached files).

The example has some limits. It caters for booking periods of up to one year and is set up to deal with up to sixty rooms. Longer periods or a greater number of rooms would be possible, but might be better handled with a different technique.

The interface for the demo is point and click. As soon as you enter the 'From' and 'To' dates, a list of rooms which are available throughout the entered period is displayed. Clicking on a room in the list then places a booking for that room.

Although the technique shown in the attached example may not be exactly what you are looking for, perhaps it will give you some ideas to work with.

Reservations.zip

[Ugo DI LUCA]

Superb Ray,

So now I know that it is possible to work with "related" ranges. Even if your sample again is smart, I noticed that the nested replace (and others) functions would be up the limit of my understanding for the moment.

As I said, I was very curious of how to handle this kind of relationship, as I need quite same settings for my "shipping planning".

I've heard of plug-ins for Range.

Would they be useful in this situation and would this kind of stuff be easier with these plug-ins ?

Finally, I think you should throw this attachment in the sample section as the latest demonstration of how handy a relationship structure can be vs a Find.

Thanks again Ray for your constant "brillance"

[CobaltSky]

Hello Ugo,

I'm pleased that you found the example interesting.

The approach it uses has the advantage of being based mostly on fairly simple principles which are relatively transparent - which makes it not too difficult to implement or to trouble shoot. However the trade off is that it is a bit repetitious to code and is a finite solution (ie you have to work within 'ceilings' imposed by the code).

A variation of the same underlying principles incorporating scripted loops to generate the keys can be extended to cater for periods spanning up to 25 years (before reaching the 64k field limit in the date range key field) and to deal with sets of related values limited also only by FileMaker's specifications (eg value list size maxes at 32k).

This can then be made to operate seamlessly (ie without requiring the user to click on any buttons) by using a script triggering plug-in.

There are numerous other approaches to range-based relationships which can also be considered, howhever, and which could be adapted to provide a comparable solution. Three of them are:

1. Retrieval of related foreign keys by range (as per various demos including some of my own, and the very good example recently posted in the sample files section by Djukic in the sample files section of the forum).

2. Range based plug-in options available from various vendors.

3. The 'Smart ranges' technique promulgated by Mikhail Edoshin.

There are also plug-ins that can assist with the text manipulations and string functions associated with inverting key field values as demonstrated in the example file I posted (above). However although I'm all in favour of using plug-ins when appropriate (and use a number of them regularly) I generally consider ways of achieving a given outcome without a plug-in before deciding whether to use one.

In reality there are few range relationship challenges that can't be solved by any of the above techniques - so often the decision is one of the ease and efficiency of implementation, and perhaps also user interface implications of the different techniques. It's rare that a fully open ended (eg approaching infinity) range relationship is needed, then a smart ranges (or equivalent plug-in based approach may be preferable (It's worth noting that several of the plug-ins are in fact built on the same theoretical underpinnings as smart ranges).

If some people find the calcs associated with smart ranges and other compound range key formulae a bit daunting, they may perhaps (provided they have familiarity with the external function API) be more comfortable feeding parameters to a plug-in than to a compound formula. It's largely a question of style and preference, I think.

But for many implementations - where the range requirements fall within well defined parameters - the more familiar ground of the finite key calcs such as those in the demo I posted to this thread earlier, will most likely suffice.

[FMAsker]

Thank you all for the help you have provided to me!!

Especially thanks to you Ray, you gave me full working solution!!!

I will customise it, test it, and I will keep you informed.

[FMAsker]

Hello everyone,

I add more things in the reservation file that made Ray and thanks a lot for this but I want to ask something and I will use a little example. I suppose I have booked all my rooms from 1/5/03 to 4/5/2003. If I search from 1/5/03 to 6/5/03, the database can tell me that all the rooms are available for 2 nights, I mean the difference? This helps me to understand which day I have available rooms to booking? So I think the solution should be graphic... so here is an example:

Let

[CobaltSky]

See attached!

...Keep this up and I'll have to start charging you a fee!!

Reservations_v2.zip

[Ugo DI LUCA]

Ray,

I'd have a few of these for you if you don't mind....

Let start with the first little one. A Weather forecast db for the next 10 years. Is it something you would have a sample for...

FMAsker, don't ask to much (at a time) or Ray would begin to charge us all...

[FMAsker]

Ok, i am sorry guys for my questions but i asked because firstly i want to solve my problem and secondly KNOWLEDGE IS A POWER!!!

[BobWeaver]

Even though FMAsker found a good solution, I thought I would add a comment...

Ugo, I just happened to read further up in this thread that you had suggested using my daterange example to generate a multikey. While you can certainly use it for that, I would have recommended instead the simpler method that I posted here:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=files&Number=64912&page=0&view=collapsed&sb=5&o=31&fpart=1

Rather than a date multikey, It generates a numeric multi-key which will work perfectly well with dates or times since both date and time are stored as numbers anyway. The advantage is that while it is based on the same principle as the daterange calc, it is considerably simpler.

Ray, I totally agree with your comments on calculation formulae transparency, repetitive coding and the various tradeoffs. My goal with the numeric multi-key example is something that is reasonably adaptable to a number of applications without the ceilings imposed by a repetitively coded formula, while remaining (hopefully) somewhat less opaque than smart ranges.

[Ugo DI LUCA]

Bob,

At the moment of my post, I was struggling with Smart Ranges (and I'm still are...) and you hadn't added this sample file. If so, I surely would had recommanded it, as all other Ranges solution given in these last days.

Seems as a required feature for this spring

[BobWeaver]

Right you are. This thread has been going on for a while, hasn't it.

[BertBoye]

I have been following this thread for some time now and Ray thanks for the sample files they have really get me started, but not for rooms however, for managing staff!

However, I have a question for you, if I want to have more then 60 rooms how you would do that, let us say 400 rooms. Since we have a 64k limit on fields I think your calculation will be more then 64K in a case of 400 rooms or more rooms.

[CobaltSky]

I have been following this thread for some time now and Ray thanks for the sample files they have really get me started, but not for rooms however, for managing staff!

Glad to hear you've found the samples usful. As you've noticed, variations on the same basic technique have quite a few different applications.

However, I have a question for you, if I want to have more then 60 rooms how you would do that, let us say 400 rooms.

There are quite a few ways to cater for more than 60 rooms (or staff, or projects or equipment items or shipments or whatever is being allocated ). I used sixty because it is all that fits into one calc, and demos are generally best pared back to show the basic technique. Here are several examples of ways to extend the number of entities beyond 60:

1. Cascade a series of calcs. You can deal with a further 60 entities with each additional calc, so you'd need seven calcs to reach a max of 420. Possible, but not the most efficient method.

2. Create a reconciliation of data strings which operates within a loop and generate the cross-matched key sets via a script (as per the current example of the 'dynamic list' demo on my site).

3. Parse the relevant key strings through an external function (eg as provided by Troi Text) to generate the composite string (multi-key) for unused values.

Since we have a 64k limit on fields I think your calculation will be more then 64K in a case of 400 rooms or more rooms.

In the example file, the 'RoomID' values are less than ten characters, so you'd have to be approaching 7000 rooms before the multikey field would max out. Even if your unique keys (for rooms or staff or whatever) are the full 60 characters (the max that FM can index) you'd still be able to deal with over 1000 related entities, so 400 will breeze through.

[Ugo DI LUCA]

Hi Bertboye,

So you switched from Pucket to Sweden

The elefant's are gone ?

I really don't know what Ray could came up with, but I don't think you reached any limits providing certain workarounds. I made some test over my proper files (nothing to do with resa though). I won't say I made it totally work, but I think I'm on my way through this by somehow "filtering" the search by additional criterias.

You may even drop additional calc fields and merge them, but IMO, it is likely that you may not have to search for the entire availability when you're processing a reservation. You could use some additional keys based on the room category, or some additional services, or split the formulas to have you show each floor....

Using the value list "rooms" created by Ray, you'd have 60 rooms but you could reduce this list by certain criteria.

I'll stay tuned anyway just as you did...