Creating Find Set based on date ranges

[Miss A!]

Hi there all,

 

Please i need help, i would like to create a script that returns a set of records that fall on a particular date range, there are two fields, startdate and enddate, i would like to view records whose start date falls on a particular week. The date fields(start and end date) already have values so my script must go to that layout and display only records whose start date is on the current week.

[eos]

display only records whose start date is on the current week.

 

In that case the end date seems to be immaterial; to get the Monday date for the week of the start date, try

Let (
  dow = DayOfWeek ( startDate ) ;
  startDate - Case ( dow = 1 ; 6 ; dow - 2 )
)

This one also works: 

startDate - Mod ( Mod ( DayOfWeek ( startDate ) ; 7 ) - 2 ; 7 ) 

but seems a bit on the esoteric side …

 

To use that in a script:

Set Variable [ $mondayDate ; calc from above ]
Go to Layout [...]
Enter Find Mode
Set Field [ YourTable::yourDateField ; $mondayDate & ".." & $mondayDate + 6 ]
Set Error Capture [ on ]
Perform Find
# etc

[Miss A!]

I am not sure this answers my problem, got me more confused - i not even sure my question of the problem was clear, i tried the above solution and it does not display any record......ok, again i try:

 

I have two fields, start and end date, for each record(that us 1328 records in my table) there is an existing start date and end date, i would like to display only those records whose start date is on the current week.........So, do i perhaps need to define another date field, unsure for what purpose - Sorry my filemaker knowledge is only six weeks old........

[eos]

only those records whose start date is on the current week

 

I had assumed you have some sort of global field to define search range.

 

If you need the week of the current date, use

Let ( [
  cd = Get ( CurrentDate ) ;
  dow = DayOfWeek ( cd ) 
  ] ;
  cd - Case ( dow = 1 ; 6 ; dow - 2 )
)

and

 

Set Variable [ $thisMonday ; calc from above ]
Go to Layout [...]
Enter Find Mode
Set Field [ YourTable::dateStart ; $thisMonday & ".." & $thisMonday + 6 ]
Set Error Capture [ on ]
Perform Find
# etc

[LaRetta]

Weeks usually are known to start on Sunday.  Here's another way.  Script will be:

Enter Find Mode [ uncheck pause ] <-- this is much more efficient than waiting until you land on the layout itself
Go To Layout [ layout where the records to search exist ]
Set Field [ yourTable::StartDate ; Let ( sun = Get ( CurrentDate ) - Mod ( Get ( CurrentDate ) ; 7 ) ; sun & ".." & sun + 6 )]
Set Error Capture [ on ]
Perform Find
If [ not Get ( FoundCount ) ]
Show Custom Message [ OK ; "No records found" ]
End If

To explain, entering find mode before going to a layout saves loading records (which happens when you switch to a layout) that you will only ditch when you then perform your find.  Going into find mode FIRST eliminates this needless loading of records at the start.  BTW, as Comment once said, "Know thy Mod() function, when thou cometh to date calculations."  Also note that ".." or "..." both works in FileMaker EXCEPT if you are searching for a decimal number range.

Edited October 7, 2014 by LaRetta

[comment]

as Comment once said, "Know thy Mod() function, when thou cometh to date calculations." 

 

That's certainly true, although in this case you could also use the DayOfWeek () function - which does practically the same thing, but the process may be easier to understand. For example:

Let ( [
today = Get ( CurrentDate ) ;
sat = today - DayOfWeek ( today )
] ;
sat + 1 & ".." & sat + 7
)

or, for a week that starts on Monday (as it does in many parts of the world):

Let ( [
today = Get ( CurrentDate ) ;
sun = today - DayOfWeek ( today - 1 )
] ;
sun + 1 & ".." & sun + 7
)

[LaRetta]

These types of calculations are such fun!!   :-)

[LaRetta]

Being in US, I have always thought starting on Sunday was nonsense but I did not know that weeks start on Monday in different parts of the world!  Language setting on Mac shows first day as Monday for Namibia.  I have been unable to find any reference which lists countries with their default start day.  I can see how, if a solution is multi-country, it might be worthwhile to adjust according to default first day.

I suppose I can create my own list by going through the Language settings myself.

[Miss A!]

My problem is bigger - i can use this calculations without understanding what is going on or the result i am getting or even what i am to expect of the result, i am taking all this calculations on by myself, i hope i get there........programming is a hustle really for me ((

 

 

I appreciate your all input guys......

[Miss A!]

Also i do not want the records for which the start date is empty to be returned - at the moment, that is the case and would like to get rid of that situation......

[MikeKD]

Thanks so much Laretta!

 

 

Enter Find Mode [ uncheck pause ] <-- this is much more efficient than waiting until you land on the layout itself
Go To Layout [ layout where the records to search exist ]

Just used your tip on three of my scripts and it's transformed the speed of them.

Yay!!

Mike

[LaRetta]

Also i do not want the records for which the start date is empty to be returned - at the moment, that is the case and would like to get rid of that situation......

 

Hello Miss A,

 

If you use the script provided and the calculation Comment provided, you will not return records with empty start dates (in fact none of them will return records with empty start dates).  At this point, it would be good if you can zip and attach your file or at minimum, attach a copy of your script. :-)

Edited October 8, 2014 by LaRetta

[Miss A!]

Ohh'haaa, i see my mess, needed fresh day for this.......i was returning a different date field(TG_Start) when my found set was based on another field(Tour_Start) - it does merit my expectation, thanx