FInd records between two dates

[chizuoka]

Hi... trying to do something straight forward which is to find records between a start and end date.

I did this to filter of files that are deleted i.e. deleted = 1. But when i tried to add a value from a global field named start_date and sort_date which are both date fields, and tried to do Table::Created_date < Table::sort_date for example.. it tells me i need a date field. Can i not use a variable value in a find or find supports only absolute values... like the "0" above

[Vaughan]

Don't use the stored find requests, build them up "manually" the same way you;'d do them in FInd mode... do not retire the find requests when changing into Find mode or in the perform find step.

Enter Find Mode []

Set Field [ Table::Created_date ; "<" & getastext( Table::sort_date ) ]

Perform Find []

[chizuoka]

I have solved this myself, as below:

However i am puzzled by few things:

1) as can be seen in the highligted row.. my specified find param is "Sales_Management::sort_start & "..." & Sales_Management::sort_end"

what does the "..." stand for? Does it have some meaning?

2) i got this snippet of script to add a month to a date... however... i do not understand what is going on... if i wan to MINUS a month or even two, which one of the numbers do i change?

If(Day(Sales_Management::sort_start) > Day(Date(Month(Sales_Management::sort_start)+2;1;Year(Sales_Management::sort_start))-1);

Date(Month(Sales_Management::sort_start)+2;1;Year(Sales_Management::sort_start))-1;

Date(Month(Sales_Management::sort_start)+1;Day(Sales_Management::sort_start);Year(Sales_Management::sort_start)))

3) The example code came from a starter project in FM12.... i keep seeing this "$$SCRIPT_TRIGGER = "Off"" here and there... any idea what it is for? I disabled it and there does not seem to be any effect

Thanks!

By the way how do you copy and paste script code instead of me having to screenshot it?

[eos]

1. "..." is the range operator, e.g. enter 1/1/2012 ... 4/30/2012 in a date field to find all records with a date in between, including the values itself,

which in this case would be all records with a date from January until April; works also with numbers and text.

2. Even if this code snippet comes from the mothership, so to speak, it is still poorly written and seems to stem from pre-.fp7 days; first thing I would suggest is to

get savvy with the Let () function, and to use whitespace and comments in your calculations and script steps (maybe not as much as I did here for instructional purposes …).

This is the same calculation with Let () and whitespace, which not only IMHO is much better readable, but also speeds things up a bit, and even more so

for calculations which reference more actual fields.

Let (

[ sortStart = Sales_Management::sort_start ;

startMonth = Month ( sortStart ) ;

startDay = Day ( sortStart ) ;

startYear = Year ( sortStart ) ;

theCriticalDate = Date ( startMonth + 2 ; 0 ; startYear ) ] ; // the short form of Date ( startMonth + 2 ; 1 ; startYear ) - 1; FMP regards the “zeroeth” day of a month as the last day of the previous month

If ( startDay > Day ( theCriticalDate ) ; // if start day of start month is greater than the last day of the following month, e.g. 05/31/2012 vs 06/30/2012

theCriticalDate ; // then use the last day of the following month, i.e. 06/30/2012

Date ( startMonth + 1 ; startDay ; startYear ) ) // otherwise, use the same month day, but with the following month, i.e. 06/09/2012

)

It should now be clear(er) which values you have to change to adjust the calculation to your purposes.

3. $$SCRIPT_TRIGGER = "Off" is very probably used to to prevent a script trigger from firing, or more precisely, the associated script from running, in a context you don't want it to.

A script sets this global variable if it knows that in its course it will trigger another script. This variable will then be read by the triggered script. If set to "Off", the script resets

the variable to “On” (so next time it will work properly when intended) and exits back to the calling script, without performing its task.

[brian rich]

You might like to look at the SixFriedRice blog which includes several posts by Geoff Coffey on how Finds work.

Start here: http://sixfriedrice....-mode-requests/

The post on Dates and Times should help.

Brian

[Lee Smith]

3) The example code came from a starter project in FM12.... i keep seeing this "$$SCRIPT_TRIGGER = "Off"" here and there... any idea what it is for? I disabled it and there does not seem to be any effect

By the way how do you copy and paste script code instead of me having to screenshot it?

Which template is this from? The names for the date fields bother me.

On a Macintosh, you can print you scripts to preview, and then copy it to the clipboard, for pasting in the Code box (second row of icons, 12th icon).

Lee

[chizuoka]

Thanks guys! Really appreciate your time.. problems solved!

Which template is this from? The names for the date fields bother me.

On a Macintosh, you can print you scripts to preview, and then copy it to the clipboard, for pasting in the Code box (second row of icons, 12th icon).

Lee

The dates codes came from an article by filemaker on how to add a month to a current date. It was not in the code. Thanks about the tip

[chizuoka]

What happens if you want to add a year, less or more ONE DAY to a date? The formula is alot more complex isnt it? The above is adding and subtracting a month... when it comes to day? Like in insurance policies which expire one year less a day from the day you bought it?