Hoping you might be able to help with ideas for writing efficient script for a specific task. The current script for the task described below takes 8 seconds when there is not much server traffic but up to 35 seconds when there is heavier server traffic.
Three of the tables in my database and their fields (FM Pro Advanced 11), forming a many to many relationship are as follows:
Abstracts: AbstractID (primary key), Title, Text, RandOrder, Number of Screen Responses (unstored, calculation field, may be empty for as yet unscreened abstracts). Records in this table are ordered by the RandOrder variable. Number of Screen Responses is an unstored, calculation field because the database has multiple users.
Screeners: ScreenerID, AccountName, Last abstract screened, Current abstract
Abstracts_Screeners Join table: AbstractScreenerID, AbstractID, ScreenerID, ScreenResponse, ScreenStartTime, ScreenEndTime
Screen Layout: based on Abstracts, with a portal showing the related record from the Abstracts_Screeners Join table.
Each abstract needs to be screened by exactly two different screeners. When a screener requests to screen an abstract, the script needs to do the following:
1. Get AccountName for current user;
2. Identify the RandOrder for the first abstract that has not been screened by at least 2 people;
3. If the identified abstract has been previously screened by the current screener, identify the next abstract that has not been screened by at least 2 people (using the number of screen responses);
4. Request a new record in the Abstracts_Screeners Join table, set the AbstractID for the abstract identified for screening, set the ScreenerID, set the ScreenStartTime;
5. Display the abstract on the Screen Layout and the corresponding record from the Abstracts_Screeners Join table for the user to enter their screen response.
Any ideas on the pseudocode and tips to make it efficient will be very much appreciated. Spent more than 2 days trying to make this work but still working to figure it out.