Calculation to search for last year and this year current month

[Matthew R White]

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?

[eos]

 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 )
)

[David Jondreau]

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, 2014 by David Jondreau

[comment]

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).

[Matthew R White]

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?

 

[eos]

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 )
)

[Matthew R White]

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!

[Matthew R White]

Any thoughts on this?

[eos]

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 …

[Matthew R White]

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?

[Lee Smith]

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