fmp17 Find range of dates with script variables

[john9210]

I can't get find a range of dates with a script. The following script is supposed to find the last 12 records on the layout. The Perform Find results in the error that the provided find criteria is not correct. Even if I use Enter Find Mode and insert the variables, I get the same error. What gives?

 

Layout [ “Log” ]

Show All Records

Sort Records [ Keep records in sorted order; Specified Sort Order: Log::Date; ascending ] [ Restore; No dialog ]

Go to Record/Request/Page [ Last ]

Set Variable [ $End; Value:Log::Date ]

Set Variable [ $start; Value:$end-12 ]

Perform Find [ Specified Find Requests: Find Records; Criteria: Log::Date: “$start...$end” ] [ Restore ]

//Enter Find Mode [ ]

//Set Field [ Log::Date; $start...$end ]

//Perform Find

[Steve Martino]

Could it be a simple typo?  You are setting $End (capital 'E") and performing a find on $end (lowercase 'e').

However, if you're looking for the last 12 records

Show All Records

Sort Records...

Go to Record/Request/Page [First]

Omit Multiple Records [With dialog:Off; Get(TotalRecordCount)-12]

 

[john9210]

It makes not difference. However, I like your simpler approach. But I would sure like to know why the script doesn't work.

[comment]

21 minutes ago, john9210 said:

But I would sure like to know why the script doesn't work.

You cannot use more than one variable in a stored find request.

[OlgerDiekstra]

9 hours ago, john9210 said:

Perform Find [ Specified Find Requests: Find Records; Criteria: Log::Date: “$start...$end” ] [ Restore ] 

Because the $start and $end variables are not used correctly.

What you need is: $start & "..." & $end

Notice the quotation marks. When you do “$start...$end” the variables aren't converted to their values, but instead are used literally.

6 hours ago, comment said:

You cannot use more than one variable in a stored find request.

Yeah you can. 😛

Perform Find [ Specified Find Requests: Find Records; Criteria: Log::Date: $start &"..." & $end ] [ Restore ] 

Works perfectlly fine. 😛

 

[comment]

1 hour ago, OlgerDiekstra said:

Perform Find [ Specified Find Requests: Find Records; Criteria: Log::Date: $start &"..." & $end ] [ Restore ] 

Works perfectlly fine. 😛

I don't think so. You cannot perform calculations in a find request. 

[OlgerDiekstra]

Ah, you're right. I usually use enter find mode and set fields. With perform find you'd have to create a single variable that holds from and to date, and use that single var in the perform find.

Like so:

set variable[ $daterange; $start & "..." & $end ]

Perform Find [ Specified Find Requests: Find Records; Criteria: Log::Date: $daterange ] [ Restore ]