Calculation date based on get current date and limited to prior 2015

[Matthew R White]

Hey everyone! Happy Monday! 

I'm trying to enter a calculation into a date field - then perform find for certain months but I don't quite have the calculation correct yet.

I'm trying to get the current month and year (say August, 2015 for example) and then insert a calculated result of August - October for all years prior to 2015.

Here is the current calculation I'm using, it works somewhat in that its not getting the 2015 dates but in turn its getting ALL 2014 and 2013 months not just limiting it to the current month August - October  (next 2 months)

Let ( d = Get ( CurrentDate ) ; Date ( Month ( d ) - 27 ; 1 ; Year ( d ) ) & "..." & Date ( Month ( d )  - 9 ; 0 ; Year ( d )))

[comment]

Before you ask about the calculation formula, ask yourself what the result of the calculation should be. In order to find records in the range of August - October in any year except the current one, you must enter this as your search criteria:

{08..10}/*/{0001..2014}

This is assuming your file is using M/D/Y as its date format.

It is also assuming that the current month is earlier than November, because in November and December you must look for two distinct ranges: one from the start of the current month until the end of the year, and one from the start of the year until the end of January of February, respectively.

Are you scripting this? The easy solution here would be to have a script create three requests in the form of:

8/*/{0001..2014}
9/*/{0001..2014}
10/*/{0001..2014}

 

[Matthew R White]

Comment, thank you for the reply! I had to re-read it about 4 times to understand as I'm still a novice with filemaker.

I understand that the result should look like if month (get (current date)) is August. and I understand about november and december.

so my calculated result should look something like this:

"{" & Month (get(current date) & ".." & Month (get(current date) + 3) & "/*/{0001.." & (Year (get(current date) - 1) & "}"

[dwdata]

Weird - I tried to post to this thread earlier, but it died.

Another route would be to delineate your targeted date field for easier search capabilities:

cMonth = (Calculation, Number Result) = Month(theDateField)
cYear = (Calculation, Number Result) = Year(theDateField)

then you can script your find like this:

Enter Find Mode []
Set Field["cMonth", "8...10"]
Set Field["cYear", "<" & Year(Get(CurrentDate)) ]
Perform Find[]

Several ways to skin a cat - I guess ;oP

Hope this helps.

[comment]

so my calculated result should look something like this:

"{" & Month (get(current date) & ".." & Month (get(current date) + 3) & "/*/{0001.." & (Year (get(current date) - 1) & "}"

More or less (you have several syntax issues). But why go there at all, if it won't work in November and December?

[Matthew R White]

dwdata, thanks again for the reply! 

when you say delineate the field are you saying create two new number fields with calculations Month(thedatefield) etc

and also would that work for Nov and Dec?

comment - correct wouldn't work for November and December the way I wrote it. I guess I could put an If - month(get (current date) = November then perform a different script... I think id rather use dwdata's calc!

 

[comment]

comment - correct wouldn't work for November and December the way I wrote it. I guess I could put an If - month(get (current date) = November then perform a different script... I think id rather use dwdata's calc!...

IMHO, if you're going to script this anyway (and how else would it work if not scripted), then put all your logic into the script and save yourself the overhead of extra calculations fields (which won't work anyway, because of the same year span issue).

I have already indicated the easy way to script this.

[Matthew R White]

Thanks comment. I'm just a bit lost with the term Scripting. Yes I am creating a script to accomplish this task, I can see you recommend creating 3 separate scripts to accommodate the 3 months I'm looking for but then how do I combine those into a find field? 

[dwdata]

dwdata, thanks again for the reply! 

when you say delineate the field are you saying create two new number fields with calculations Month(thedatefield) etc

and also would that work for Nov and Dec?

I believe so: just modify the range from "8..10" to "11...12". 

COMMENT brings up a concern where he feels that there could be a case scenario where this will not work ( same year span?). On the surface (besides the two extra calc. fields - overhead), I cannot see where this would be an issue. I am happy to be enlightened. Please do share.

Thanks guys ;-)

[comment]

I can see you recommend creating 3 separate scripts

Where exactly can you see this? I said "have a script create three requests" - that's one script and three find requests, not three scripts. The script itself could be something like this:

Go to Layout [ “YourTable” ]
#
Set Variable [ $m; Value:Month ( Get ( CurrentDate ) ) ]
Set Variable [ $dy; Value:"/*/{0001.." & Year ( Get ( CurrentDate ) ) - 1 & "}" ]
#
Enter Find Mode [  ]
Set Field [ YourTable::Datefield; $m & $dy ]
New Record/Request
Set Field [ YourTable::Datefield; Mod ( $m ; 12 ) + 1 & $dy ]
New Record/Request
Set Field [ YourTable::Datefield; Mod ( $m + 1 ; 12 ) + 1 & $dy ]
Perform Find [  ] 

Note again that this assumes M/D/Y is the date format in use.

 

 

just modify the range from "8..10" to "11...12".

"8..10" is three months. "11...12" is two months.  If you want to find a range of three months starting from November, you must also find the records in January. Likewise, in December you must find December, January and February.

 

 

Edited August 24, 2015 by comment

[dwdata]

"8..10" is three months. "11...12" is two months.  If you want to find a range of three months starting from November, you must also find the records in January. Likewise, in December you must find December, January and February.

Ahh okay - so you need a 3 month span, starting with this current month, but one year ago - correct?

If so, then I would side with COMMENT and extra fields are not necessary in this case. Another way to get that date span is this way:
 

Enter Find Mode []
Set Field [theTargetField,
Let([
sd = Date ( Month(Get ( CurrentDate )) ; 1 ; Year(Get ( CurrentDate )) - 1 );
ed = Date ( Month(sd) + 4 ; 0 ; Year(sd))
];
sd & "…" & ed
)]
Perform Find[]

Anyway, you should enough feedback to get your end result. Thanks Comment and Good luck Matthew!

[comment]

Another way to get that date span is this way:
 

Enter Find Mode []
Set Field [theTargetField,
Let([
sd = Date ( Month(Get ( CurrentDate )) ; 1 ; Year(Get ( CurrentDate )) - 1 );
ed = Date ( Month(sd) + 4 ; 0 ; Year(sd))
];
sd & "…" & ed
)]
Perform Find[]

That would find records within the 3 months one year ago.  Matthew wants to find records within the range of those 3 months in any year, except the current one.

[dwdata]

Thx Comment - one thing I noticed and really a matter of what Matthew needs. If the current date is 11/15/2014 would he want to include in dates in JAN 2014 because it is part of the 3 month span on the prior year. Matthew? ;o)

I did a little mod on your code to accommodate this. Does this logic make sense or would you handle if differently:
 

Set Variable [ $end_date; Value:Date ( Month ( Dates::gSelectDate ) + 4 ; 0 ; Year ( Dates::gSelectDate ) - 1) ]
Set Variable [ $m; Value:Month ( Dates::gSelectDate ) ]
Enter Find Mode [ ]
Set Field [ Dates::tDate; $m & "/*/*" ]
New Record/Request
Set Field [ Dates::tDate; Mod ( $m ; 12 ) + 1 & "/*/*" ]}
New Record/Request
Set Field [ Dates::tDate; Mod ( $m + 1 ; 12 ) + 1 & "/*/*" ]
Perform Find [ ]
Enter Find Mode [ ]
Set Field [ Dates::tDate; "<" & $end_date ]
Constrain Found Set [ ]

FYI - I used a GLOBAL date field to test various search results on my set of sample data. This would be replaced with Get(CurrentDate). Thanks!

Edited August 25, 2015 by dwdata

[comment]

If the current date is 11/15/2014 would he want to include in dates in JAN 2014 because it is part of the 3 month span on the prior year. Matthew?

That's a good question. If the answer is no, I would do:

Go to Layout [ “YourTable” ]
#
Set Variable [ $m; Value:Month ( Get ( CurrentDate ) ) ]
Set Variable [ $y; Value:Year ( Get ( CurrentDate ) ) - 1 ]
#
Enter Find Mode [  ]
Loop
  Set Variable [ $i; Value:Get ( RecordNumber ) ]
  Set Field [ YourTable::Datefield; Let ( [
        request = "m/*/{0001..y}" ;
        m = Mod ( $m + $i - 2 ; 12 ) + 1 ;
        y = $y - ( $m > m )
        ] ;
        Substitute ( request ; [ "m" ; m ] ; [ "y" ;  y ] )
        ) ]
  Exit Loop If [ $i ≥ 3 ]
  New Record/Request
End Loop
#
Perform Find [  ]