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]

  On 3/11/2019 at 8:55 PM, john9210 said:

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

Expand  

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

[OlgerDiekstra]

  On 3/11/2019 at 6:13 PM, john9210 said:

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

Expand  

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.

  On 3/11/2019 at 9:17 PM, comment said:

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

Expand  

Yeah you can. 😛

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

Works perfectlly fine. 😛

 

[comment]

  On 3/12/2019 at 3:37 AM, OlgerDiekstra said:

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

Works perfectlly fine. 😛

Expand  

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 ]