Global variables in find criteria in script don't return expected results
I have a script which should load a layout, present a dialog to the user to ask for a numerical value, and run a find to limit the resulting records. However, when I try to use a global variable in the find criteria, it doesn't work - all records are returned. Here's the script. Dashboard is my "main menu" table, currentIssue and issueSearch are set as global fields. $$renewNew and $$renewOld pick up the correct values and can be displayed in "My Layout" so I know they have the right values.
Show Custom Dialog ["Ads","Please enter a value"; Dashboard::issueSearch]
Set Variable [$$renewOld; Value:Dashboard::currentIssue - Dashboard::issueSearch]
Set Variable [$$renewNew; Dashboard::currentIssue]
Go to Layout ["My Layout" (Ads)]
Show All Records
Perform Find [Restore]
Sort Records [Restore; No dialog]
The Find looks like this:
Find Records - Ads::lastIssue: [$$renewOld ... $$renewNew]
This is the bit that doesn't return what I expect to see. For example, if $$renewOld is 61 and $$renewNew is 69, I should only see records where lastIssue fits within those values. But I am shown ALL records. If I change this find request to say
Find Records - Ads::lastIssue: [61 ... 69]
it works as expected - just the records in that range are shown. So the problem is in my use of the global variables. What am I doing wrong?
You can't use and expression inside stored criteria--only a single variable.
Instead of stored criteria, do it like this:
Enter Find Mode  ---> clear the pause check box
Set Field [Ads::lastIssue ; $$renewOld & "..." & $$RenewNew ]
Perform Find 
For more examples of scripted finds of this format, see: Scripted Find Examples