Check whether Date is within a Date range

[Tissot]

Hello guys.

Small question. How can I check whether a Date is within a date range from two other fields?

Thanks

Edited July 1, 2008 by Guest

[Fitch]

Example:

StartDate <= theDate and EndDate >= theDate

[Tissot]

Wow, very speedy! Thanks.

I forgot to say:

They are Timestamp fields and I would like to check if its in range:

1.7.08 7:00 till 1.7.08 12:00

and another field for the afternoon

1.7.08 12:00 till 1.7.08 17:00.

Is this possible?

[Fitch]

Basically same idea:

Timestamp(Date(7;1;2008);Time(7;0;0)) <= yourTimestamp

and

Timestamp(Date(7;1;2008);Time(12;0;0)) >= yourTimestamp

(The order of the parameters in the Date function is always Month, Day, Year)

[Tissot]

Thanks.

That’s for one field but I need to see whether it’s within the range of two dates.

eg:

Occurrence

1.7.08 7:00:00 - 5.7.08 12:00:00

Now every field between these timestamps must give me a "1".

I am building a Project planning graph to display mornings and afternoons of a day in a week view.

What I want to happen is to make the field a colour with conditional formatting to indicate there is an occurrence booked.

Also there can be more than one occurrence for the same Project on the graph (don’t know how to solve this but).

Your help is very much appreciated.

Regards

Edited July 2, 2008 by Guest

[Tissot]

Ok, so I managed it some how:

Case(

Timestamp(jp.TuDateCalc;Time(7;0;0)) ≤ Manhours::m.StartTime and Timestamp(jp.TuDateCalc;Time(12;0;0)) ≥ Manhours::m.StartTime;1;

Timestamp(jp.TuDateCalc;Time(7;0;0)) ≤ Manhours::m.EndTime and Timestamp(jp.TuDateCalc;Time(12;0;0)) ≥ Manhours::m.EndTime ;1;

Timestamp(jp.TuDateCalc;Time(7;0;0)) > Manhours::m.StartTime and Timestamp(jp.TuDateCalc;Time(12;0;0)) < Manhours::m.EndTime ;1;

)

This works fine for the first record but how do get all records to show on the view?



I tried the List() function:

Case(

Timestamp(jp.MoDateCalc;Time(7;0;0)) ≤ List(Manhours::m.StartTime) and Timestamp(jp.MoDateCalc;Time(12;0;0)) ≥ List(Manhours::m.StartTime);1;

Timestamp(jp.MoDateCalc;Time(7;0;0)) ≤ List(Manhours::m.EndTime) and Timestamp(jp.MoDateCalc;Time(12;0;0)) ≥ List(Manhours::m.EndTime) ;1;

Timestamp(jp.MoDateCalc;Time(7;0;0)) > List(Manhours::m.StartTime) and Timestamp(jp.MoDateCalc;Time(12;0;0)) < List(Manhours::m.EndTime) ;1;

)

Only problem is: It doesn’t work! I've got the feeling its a bit more complicated.

Can someone point me in the right direction?

Thanks!

Edited July 2, 2008 by Guest

[Fitch]

The List function doesn't make sense in the second calc.

I'm afraid you've lost me, I don't know what you're trying to do.

[Tissot]

Yea, I realize that the list function doesn’t make sense. The Calc should work on all related records (occurrences). So instead of only showing the first one like it does now, it should return “1” for all date ranges related.

EDIT: So you can see what I mean:

Case(

Timestamp(jp.MoDateCalc;Time(7;0;0)) ≤ Manhours::m.StartTime and Timestamp(jp.MoDateCalc;Time(12;0;0)) ≥ Manhours::m.StartTime;1;

Timestamp(jp.MoDateCalc;Time(7;0;0)) ≤ Manhours::m.EndTime and Timestamp(jp.MoDateCalc;Time(12;0;0)) ≥ Manhours::m.EndTime ;1;

Timestamp(jp.MoDateCalc;Time(7;0;0)) > Manhours::m.StartTime and Timestamp(jp.MoDateCalc;Time(12;0;0)) < Manhours::m.EndTime ;1;



Case(

Timestamp(jp.MoDateCalc;Time(7;0;0)) ≤ GetNthRecord (Manhours::m.StartTime ; 2 ) and Timestamp(jp.MoDateCalc;Time(12;0;0)) ≥ GetNthRecord (Manhours::m.StartTime ; 2 );1;

Timestamp(jp.MoDateCalc;Time(7;0;0)) ≤ GetNthRecord (Manhours::m.EndTime ; 2 ) and Timestamp(jp.MoDateCalc;Time(12;0;0)) ≥ GetNthRecord (Manhours::m.EndTime ; 2 ) ;1;

Timestamp(jp.MoDateCalc;Time(7;0;0)) > GetNthRecord (Manhours::m.StartTime ; 2 ) and Timestamp(jp.MoDateCalc;Time(12;0;0)) < GetNthRecord (Manhours::m.EndTime;2) ;1;



Case(

Timestamp(jp.MoDateCalc;Time(7;0;0)) ≤ GetNthRecord (Manhours::m.StartTime ; 3 ) and Timestamp(jp.MoDateCalc;Time(12;0;0)) ≥ GetNthRecord (Manhours::m.StartTime ; 3 );1;

Timestamp(jp.MoDateCalc;Time(7;0;0)) ≤ GetNthRecord (Manhours::m.EndTime ; 3 ) and Timestamp(jp.MoDateCalc;Time(12;0;0)) ≥ GetNthRecord (Manhours::m.EndTime ; 3 ) ;1;

Timestamp(jp.MoDateCalc;Time(7;0;0)) > GetNthRecord (Manhours::m.StartTime ; 3 ) and Timestamp(jp.MoDateCalc;Time(12;0;0)) < GetNthRecord (Manhours::m.EndTime; 3) ;1;



)))

I get to the next related record with GetNthRecord ().

This works fine up to 3 related records but what above? Is there another way?

Thanks.

Edited July 3, 2008 by Guest

[comment]

Why don't you make the comparison in the child (Manhours) table, then aggregate the result in the parent record, using the Sum() function?

BTW, it only takes two comparisons to find out if two ranges overlap:

StartA ≤ EndB and StartB ≤ EndA

[Tissot]

Thanks comment, I think you've helped me.

Here a Pic so you understand better:

And here the TO's:

Will your suggentsion work in this case?

Cheers

Edited July 3, 2008 by Guest

[comment]

I don't know, because I don't know what's the purpose of this calculation.

[Tissot]

Half a day slots in a week view with unlimited amount of manhours per Job.

So if I select a date range of Start Date: 1/7/08 7:00:00 - End Date: 3/7/08 17:00:00 for Job 100 the Bar should stretch from Start all the way through the End date only for the Job 100.

I solved the bar with a normal field and conditional formating. It works pretty well.

Thanks

[Tissot]

Yess! Thanks comment! Works absolutely awesome.

Funny thing is, there’s about 90% less calculation code, but still its slower than the calcs with 90% more code?!?

Cheers

EDIT: BTW, another small problem:

I've go a list from the Calc which looks like this:

Joe|Miller|12.7.08 7:00:00|12.7.08 10:00:00

Then I use the List() function across all Jobs and

I get this:

Joe|Miller|12.7.08 7:00:00|12.7.08 10:00:00

Brian|Smith|12.7.08 10:00:00|12.7.08 17:00:00

Now as a tooltip I would like to crate this output:

Joe, Miller - Start: 12.7.08 7:00:00 End: 12.7.08 10:00:00

Brian, Smith - Start: 12.7.08 10:00:00 End: 12.7.08 17:00:00

With this function:

GetValue(Substitute(Self;"|";¶);2)

But since I already got the "¶" trough the list it only shows the first record. Any way to solve this issue? or do I need separate Calc for each item.... :-(

Edited July 3, 2008 by Guest

[comment]

You'll have to either create the line as you want it at the source, or use a custom function to process the list one-by-one.

[Tissot]

Hi Michael!

Got the whole Project thingy up but now I'm on the Recourses Planning. I've managed to get everything working except something I forgot. I need to check whether two Man-hours are overlapping per Resource.

How would I accomplish something like this?

Just one of you're great pointers would be great.

PS: If you check out my Rel.jpg pic in an earlier post it might help to understand.

Thanks

[comment]

It depends somewhat on the chosen point-of-view. Basically, a conflict of manhours is not an attribute of the resource, but of the two (or more) conflicting manhours. So the most straight-forward solution is to have a self-join relationship such as:

Manhours::ResourceID = Manhours 2::ResourceID

AND

Manhours::Start ≤ Manhours 2::End

AND

Manhours::End ≥ Manhours 2::Start

AND

Manhours::ID ≠ Manhours 2::ID

and count the related records (there shouldn't be any). This can be passed up to the resource record by counting/summing the resource count field. But this only tells there is a conflict, it doesn't say when. If you want a calendar type of view, it can be much more complex - see:

http://www.fmforums.com/forum/showtopic.php?tid/176396

[Tissot]

That’s great! Thanks!

So how would I get it to show which two are overlapping on my resource layout in the day field? Maybe send some parameters with the calc? (Where I had no success yet.)

Thanks

[comment]

I'm not sure what you mean. As I said, it's not easy to look for conflicts among children from the parent record. A simple way would be to have the conflicting children appear as marked in a portal. Or you could do a calculation in the child along the lines of Case ( conflict ; some info ) and use the List() function in the parent to collect the info.

[Tissot]

Got it working beautifully. Thanks!!!

Back to this "Saving calculation field" problem:

I don't like the idea of so many calc field, I could solve the whole problem if I knew how to separate parameters from a List:

Custom Function input: cf(field; parameter)

cf(rMoCalc;2)

rMoCalc =

Joe|Miller|12.7.08 7:00:00|12.7.08 10:00:00

Brian|Smith|12.7.08 10:00:00|12.7.08 17:00:00

Function output:

Millder

Smith

Then I could do something like this:

cf(rMoCalc; 1) & ", " & cf(rMoCalc; 2) etc...

Do you know of such a CF? Or can you point me in the right direction. Sorry never worked with CFs.

Regards

[comment]

The first part doesn't seem too difficult - something like:

GetArrayColumn ( array ; columnNumber ; columnDelimiter )

Let ( [

row  = GetValue ( array ; 1 ) ;

cell = MiddleValues ( Substitute ( row ; columnDelimiter ; ¶ ) ; columnNumber ; 1 ) ;

countRows = ValueCount ( array )

] ; 

cell

&

Case ( 

countRows > 1 ; 

GetArrayColumn ( RightValues ( array ; countRows - 1 ) ; columnNumber ; columnDelimiter )

)

)

But I don't understand this part:

Then I could do something like this: cf(rMoCalc; 1) & ", " & cf(rMoCalc; 2) etc...

Wouldn't that return:

in your example?

Joe

Brian

,

Miller

Smith

,

etc.

[Tissot]

Hmm, yes.. so how would I get to the the Example results?

Would that need another CF?

Cheers

[comment]

I'm sorry, I don't quite follow. I didn't think that would be useful, but if you actually want the above result, you can get it by:

Let ( [

array = List ( Child ; cArrayRow ) ;

sep = "|"

] ;

GetArrayColumn ( array ; 1 ; sep ) & ", " & 

GetArrayColumn ( array ; 2 ; sep ) & ", " & 

...

GetArrayColumn ( array ; n ; sep )

)

Of course, instead of hard-coding this you could have another CF in the form of:

StackColumns ( array ; numberOfColumns )

that would simply call the previous function until it runs out of columns.

[Tissot]

That works awesome.

How can I do the same thing just seperated with a comma all on one line so: "Miller, Smith, ..."

Cheers

[comment]

I am sorry, your question is not clear.

[Alex Ndonga]

This gave me serious problems only to realize that the start date field and end date field were not global variables.

Here is my code:

• Go to search Layout
• show all records
• if not is empty start and end date
• Enter find mode
• set field [date] start date&"..."&end date
• perform find[]
• end if

please note that they have to be global variables or else it will not work

let me know how it turns out for you

[LaRetta]

Hi Alex!  Welcome to FMForums!  

A few things I would mention:

1. You don't need to show all records first if you plan to perform a find.
2. This thread is about calculations and not performing a find.
3. It is suggested that you error trap yourself.  By using Set Error Capture [ on ] it will stop the FM error message.
4. You might be mixing terms when you say 'global variables'.

On point #3, right before the Perform Find, use the step 'Set Error Capture [ on ]' and immediately after the Perform Find[], include these steps or similar which branches your script accordingly.  A very simple example would be:

If [ not Get ( FoundCount ) ]  ... or you can use FM Error Codes instead
# no records found so do what you want such as ...
Go To Layout [ original layout ]
Show Custom Dialog [ OK ; "No Records Found" ]
Else
# do what you wish with the found set, such as print, preview
Sort
End If

On point #4, variables did not exist back in 2008 when this thread was started so we had to use global fields instead.  Also, global fields are necessary if you want the User to enter the dates.  Script variables reside only within a script while both global fields and global variables are available to the user from anywhere within the solution without a relationship and are lost when the User closes the current session or explicitly clears their values.

There are many dedicated, knowledgeable people here willing to help any time you have questions.  This is the best forum for anything FileMaker.  And again, welcome.  :-)