Trying to search for records with Effective Dates of May 2013 through May 2014.

Heres the function that I have so far that isn't working.

 

Let ( d = Get ( CurrentDate ) ; Date ( Month ( d )  ; 1 ; Year ( d ) ) & "..." & Date ( Month ( d )  + 3 ; 0 ; Year ( d ))) 

 

Any help greatly appreciated. I'm thinking a function that specifies the year first as being this year (current date) through last year (current date - 1) and then specifies get current date for the month (May)

 

Thoughts?

 I'm thinking a function that specifies the year first as being this year (current date) through last year (current date - 1) and then specifies get current date for the month (May)

 

Last year is Year ( Get ( CurrentDate ) ) - 1.

 

Try

Let ( [
  cd = Get ( CurrentDate ) ; 
  y = Year ( cd ) ;
  m = Month ( cd )
  ] ;
  Date ( m  ; 1 ; y - 1 ) & ".." & Date ( m + 1 ; 0 ; y )
)

May 2013 to May 2014 is 13 months...Do you want just 12 months? May 2013 through April 2014?

 

If it's the former....

 

Let( [

d = Get ( CurrentDate ) ;

this.month = Month ( d ) ;

this.year = Year ( d ) ;

 

//change this 12 if you want 12 months

number.of.months = 13 ;

 

start.date = Date ( this.month ; 1 ;  y ) ;

end.date = Date ( this.month + number.of.months + 1 ; 0 ; y ) ;

 

result = start.date & "..." & end.date

];

result

 

)

 

*Oops. Misread the question...you're going backwards, eos's looks like it will work.

Edited May 13, 201411 yr by David Jondreau

Let ( d = Get ( CurrentDate ) ; Date ( Month ( d )  ; 1 ; Year ( d ) ) & "..." & Date ( Month ( d )  + 3 ; 0 ; Year ( d )))

 

That will find dates in the current month and the following two months. To calculate the date of May (or the current month) 1st of last year, try =

Let ( d = Get ( CurrentDate ) ; Date ( Month ( d )  ; 1 ; Year ( d ) - 1 ) )

 Then, assuming you don't have any future dates that you need to omit, you could simply search for:

"≥" &  Let ( d = Get ( CurrentDate ) ; Date ( Month ( d )  ; 1 ; Year ( d ) - 1 ) )

to find dates equal to or later than May 1, 2013 (if the calculation is performed today).

Thanks Guys. 

I tried eos's calculations - filemaker says (y) the specified table cannot be found.

I also tried Let ( d = Get ( CurrentDate ) ; Date ( Month ( d )  ; 1 ; Year ( d ) - 1 ) ) which just brings up last years may.

 

I've attached a picture of my table, Its separated first by month then by year showing this year 2014 and last year 2013.

Specifically I'm trying to find the following records - Current Month (May) 2014 records and 2013 records, plus Next month June 2014 records and 2013 records, finally July 2014 records and 2013 records.

 

does this make any sense?

 

Thanks Guys. 

I tried eos's calculations - filemaker says (y) the specified table cannot be found.

 

 

Syntax error (missing opening bracket ) – sorry.

 

Try again.  

Let ( [
  cd = Get ( CurrentDate ) ; 
  y = Year ( cd ) ;
  m = Month ( cd )
  ] ;
  Date ( m  ; 1 ; y - 1 ) & ".." & Date ( m + 1 ; 0 ; y )
)

It worked! Is there any way to limit that to just this month, next month, and the month after for 2013 and 2014...

Right now I'm getting the entire year.

 

Thanks again!

Any thoughts on this?

It worked!

What a surprise!

Unless there is a trick I don't know, you need to define two search requests in your script to find non-contiguous date ranges; e.g.

Set Variable [ $ranges ;  
  Let ( [    
    cd = Get ( CurrentDate ) ; 
    y = Year ( cd ) ; 
    m = Month ( cd ) 
   $range1 = Date ( m  ; 1 ; y ) & ".." & Date ( m + 3 ; 0 ; y ) ; // "5/1/2014 .. 7/31/2014" on the date of this post
   $range2 = Date ( m  ; 1 ; y - 1 ) & ".." & Date ( m + 3 ; 0 ; y - 1 ) // "5/1/2013 .. 7/31/2013" ditto
   ] ;  "" )
] // closing bracket of Set Variable syntax
Enter Find Mode
Set Field [ YourTable::yourDateField ; $range1 ]
New Record/Request
Set Field [ YourTable::yourDateField ; $range2 ]
Set Error Capture [ On ]
Perform Find

It may be worthwhile to take a closer look at these functions to understand how they work, so in the future you'll be able to tweak them yourself …

Thanks I'm a bit behind on the on the calculation/functions side of filemaker. Which is why I very much appreciate these forums and everyones insight!

 

The above code didn't work for me as there were some errors when I tried to implement it.

 

Is there a way to either constrain the found set to omit all other dates except May 14, June 14, July 14 and May 13, June 13, July 14?

Or... is there a way to specify the initial find calculation to separate out the month and year digits of the date?

Ex: Get current month digit = 5, get next month, 6, get month after 7, find all dates with that first digit and constrain them to years 2013 and 2014?

See if this helps, take a look at this CF MagicDates  at Brian Denning’s site. I don’t think it is recursive so you can use it in a regular calculation field.

 

I made this demo file back when the tip was posted. 

 

hth

 

Lee

Magicdates.fp7.zip