Auto Add New Records from one table to another table.

[cazador7907]

I have seen posts throughout the forums here related to the above topic but nothing that really fits my dilemma. I set up a very small database to track tests run on equipment by category.

For the most part everything has gone smoothly. People can enter data concerning the equipment, the manufacturer and the category of the equipment (i.e. scanner, mouse, phone, etc.).

My issues is that when a user sets the category for the equipment a script needs to run that will automatically insert a set number of test for each category. The tests and the categories are related to each other in a join table called TestCertCat. This table consists of two ID values: one for tests and one for certification categories.

The script that I have managed to produce a script will open a layout (TestCertCat) and loop through all of the records in the layout where the Certification Category ID matching the selected category ID.

What I need to have the script do now is loop through those records (already done) and insert a new record in another join table called Project Test along with the Project ID for each found record.

The final step would be to have the project layout be refreshed and the tests displayed.

I have posted the script below. Any thoughts on how I can complete this task?

Freeze Window

If [ Get (LastError) > 0 ]

Show Custom Dialog [ Title: "IQS Database"; Message: "An unexpected error has occurred attempting to load the tests related to this Certifcation Category."; Buttons: “OK” ]

Else

#This is where the magic will occur

Set Variable [ $CertCatID; Value:Project::Project_CertificationCategory ]

Go to Layout [ “TestCertCategory” (TestCertCategory) ]

Enter Find Mode [ ]

Set Field [ TestCertCategory::CertificationCategory_ID; $CertCatID ]

Perform Find [ ]

Go to Record/Request/Page [ First ]

Show Custom Dialog [ Title: "IQS Database"; Message: "Advanced to first record"; Buttons: “OK” ]

Loop

Exit Loop If [ Get( RecordNumber ) = Get(FoundCount) ]

Go to Record/Request/Page [ Next ]

Show Custom Dialog [ Title: "IQS Database"; Message: "Advanced to next record"; Buttons: “OK” ]

End Loop

Go to Layout [ original layout ]

End If

[dansmith65]

If you inserted these script steps between Loop and Exit Loop If in your example, I think it would do what you are wanting...

go to layout ProjectTest

create new record

set ID Fields from variable values

go to layout "TestCertCategory"

Do you only need to create one record in ProjectTest table per record in the found set in TestCertCategory table?