Efficient script

[sved]

Hi,

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.

Thank you!

sv

[IT_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);

Hi,

Are you doing a find? Doing Finds can slow process down considerably. Go to Related Record is a good alternative if you know how to use it effectively. It does usually require an addition of a global field and an other table occurrence.

For your database I would make a new field that combines the two screeners names into a delimited list (each screener on its own line). Then make a global field that deals with the screeners. In the relationship graph create a new table occurrence of the same table the screener fields just made. Connect the global field to the delimited screener field and make sure they do not equal.

In the script Go to Related Record (only show related records) based on the new table occurrence you made but using the same layout that you already have. This will give you the found set, but in a different manner.

As for finding if there has been at least two people that might be done with another global variable and the calculation of ValueCount on the delimited list, but I'm not sure. That might be easier with a Constrain Found Set step.

Hopefully this may provide some help. If you need clarification let me know.

[David Jondreau]

Of the set of abstracts with less than 2 responses, how do you determine which abstract gets screened next? Is that what RandOrder does? Is RandOrder a stored field? How is that field set?

Make "null layouts" for each table. Those are layouts used exclusively by scripts. I make mine with a header part only and no fields.

Your Screen layout should be based on the join table showing the related Abstract table rather than the other way around.

Allow User Abort [ Off ]

Freeze Window

1

Go to Layout [screeners Null]

Enter Find Mode

Set Field [ AccountName ; Get ( AccountName ) ]

Perform Find

Set Variable[$ScreenerID ; ScreenerID]

2 & 3

Go to Layout [ Abstracts Null ]

Enter Find Mode

Set Field [Join::ScreenerID ; $ScreenerID]

Omit Record

Perform Find

Constrain Found Set [ omit: Number of Screen Responses >1]

Error trapping if necessary (is there always an abstract to be had?)

Sort [ RandOrder?]

Go to record [ First]

Set Variable [ $AbstractID ; AbstractID ]

4

Go to Layout [ Abstracts Screeners Join Null]

Enter Browse Mode

Show All Records

Show Omitted Only

New Record

Set Field [ ScreenerID ; $ScreenerID ]

Set Field [ AbstractID ; $AbstractID ]

5

Go to Layout [ Abstract Screeners Entry ]

Exit Script[]

[sved]

DJ,

Thank you for your helpful ideas. The null layouts is a great idea. Will change the screen layout to be based on the join table.

Appreciate your time.

IT_User

Yes, I'm doing a find. I realize that a find can slow things down, with more than 16000 records in the Abstracts table. Still hesitant to try out things like new table occurrences in relationships and multiple global fields. Will try the Constrain Found Set and see how it works.

Thank you for your suggestions.

[David Jondreau]

Finds aren't necessarily slower than other methods.

To increase speed you should to consider storing the number of Joins related to an Abstract locally in the Abstract table. That requires a little bit of scripting, but depending on your business logic, it could just be a tiny bit.

After a user is assigned a Join table record, go back to the Abstract table and set a new number field to the unstored Number of Screenings field. You'll need to update all the existing records (which shouldn't take more than half a minute if you make the field an indexable calculation with Evaluate() first, then change it to a Number field ).

Then you won't have to search on an unstored calc field anymore and performance will skyrocket.