Jump to content

How to break down a date range

This topic is 5678 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have a 3 fields: StartDate (user input), EndDate (user input) and Dates (calc?). What formula or script do I need in Dates field to break down the range into dates separated by carriage return. For expl if StartDate is March 19, EndDate is March 23, I want in Dates field:

March 19

March 20

March 21

March 22

March 23

Thanks in advance

Link to comment
Share on other sites

Actually Ed, with vs. 7/8, multikey dates aren't as necessary (for all cases). For example:

StartDate (user input) should be global. EndDate (user input) should be global.

Relationship from this main table to your related (or self table). You would then use something like:

Maintable::gStartDate :less: Related::dateField


Maintable::gEndDate :great: Related::dateField

The relationship doesn't have to be User input ... it can be global calculation or unstored calculation as well. Does this help? If you have something more specific, we can help you through it.

LaRetta :wink2:

Link to comment
Share on other sites

Thanks LaRetta for trying to help.

Here is the situation: I have a Technicians table with TechID, Name, etc..., and Assignment table. The Assignment table (linked to Tech table) has TechID (drop down list to choose a technician), StartDate, EndDate, Dates, etc... My main concerne is to avoid double booking. I figured that by listing the dates a technician is booked in a Dates field and "subtracting" the next 30 days from a technician's booked days would leave the days he is available. I don't know how else to approach the problem. Your help will be highly appeciated.

Link to comment
Share on other sites

Ed, the relationship suggested should work then - just switch it, ie, your bookings would contain a FromDate and ToDate. This range would relate to a calculation with the span as you've indicated. There are three ways (that I'm aware of) to produce the span: a Custom Function (but you don't have FMA (Advanced); a static key calc (in which the User still selects their desired 'range') and the calculation produces the multikey; or via script to set a multikey.

Multikey would look something like:

gStartDate & ¶ &

gStartDate + 1 & ¶ &

gStartDate + 2 & ¶ &

... etc...

I am not qualified to suggest the best approach here. There is an excellent demo of a booking system on Ray Cologon's website at NightWing Enterprises. However, it uses a custom function.

vs. 8 offers more options, ie, GetNthRecord. But a non-equijoin might also work well for the parent key, ie, the User inputs a StartDate into global date field. EndDate could be global (date) calculation = Date ( Month ( gStartDate ) + 1 ; 0 ; Year (gStartDate) ) ... giving you a 'month' span. You would also include the TechID of course in the join (on =). Well, take a look at the demo. And others may have suggestions as well. Some even recommend a record for each date, ie, you enter the Start and End booking dates, and script creates the records. But I shy from needless records.

Do you have a sense which might work best for you? If so, we can help you structure it. There are many great people on FM Forum and ideas abound. :wink2:


Link to comment
Share on other sites

There's no need for a multi-key on either side. The relationship can be from range to range. In this case, it would be a self-join of the Assignments table. IIRC, the relationship criteria need to be:

Assignment::TechID = ConflictAssignment::TechID


Assignment::StartDate ≤ ConflictAssignment::EndDate


Assignment::EndDate ≥ ConflictAssignment::StartDate


Assignment::AssignmentID ≠ ConflictAssignment::AssignmentID

The last criterion exludes an assignment from relating to itself, so if there are any related records, there's a conflict.

Link to comment
Share on other sites

Actually Ed, with vs. 7/8, multikey dates aren't as necessary (for all cases).

However - did LeCates show us at devcon, that FM7 uses a lame algorithm for theta joins, compared to the later FM8 ...this is why this statement chimed in back in 2004:

I've found using range calcs like these to be quite a bit faster than using theta joins within FileMaker- especially when there are multiple relationship predicates. I imagine this kind of thing will be in our tool kit for a while.

...snipped from:


The hashing algorithm this thread deals with, is this:



Link to comment
Share on other sites

This topic is 5678 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.