I use FM Pro 13, have a NewJobs table that includes a start date field and a finish date field and I have a Jobs table that includes a date field and a resource number (values from 1 to 10) field.
I want to be able to search the Jobs table for a resource value that has no current records between the start and finish dates shown in the NewJobs table. This will enable me to automatically add new records to the Jobs table in the knowledge allocating the identified resource for all dates between between (and including) the start and finish dates.
I have created a script (being triggered by a button on the NewJobs layout):-
Set Field [NewJobs::gSD; NewJobs::Start_date] ( I understand that Global Fields are required in the Find Mode)
Set Field [NewJobs::gFD; NewJobs::Finish_date]
Set Field [NewJobs::gRN; 1] (Sets gRN - the resource number - to start at 1)
Go to Layout [Jobs]
Show All Records
Enter Find Mode
Set Field [Jobs::Date; NewJobs::gSD&"..."&NewJobs::gFD] (Sets date range for Find between start and finish dates)
Set Field [Jobs::Resource_number; NewJobs::gRN] (Sets Resource_number for Find = gRN)
Exit Loop If[Get(FoundCount)=0] (When no records are found, the script has found a Resource_number value that's available between start and finish dates)
Set Field [NewJobs::gRN;NewJobs::gRN+1] (Increments gRN by 1 for each Find)
Exit Script If [Result;NewJobs::gRN>10] (The script terminates if resources 1 to 10 are not available between the start and finish dates)
Show All Records
Show Custom Dialogue["Available Resource Number is:-";NewJobs::gRN]
The script only carries out a single Find implying that the loop isn't working. In "the missing manual" and FM help pages, the examples show the loop looking at one record in each cycle then passing on to the next record. In my case, I need to be able to instruct the next Find cycle somehow.
Is Loop the right way to do this? If so, what's missing from my script?