AnsweredAssumed Answered

Help with Script to Add Records Through a Join Table

Question asked by NaturSalus on Nov 24, 2011
Latest reply on Nov 25, 2011 by NaturSalus


Help with Script to Add Records Through a Join Table



I am writing a script that from an open record in Table A will create a new record in Table C, and after doing that will return me to the original record in Table A.  


Table A and Table C are related through a join Table B


Table A --< Table B >-- Table C


Usually when one moves from an open record from Table A to another Table C, one gets listed all the records of Table C.  But this is not what I want the user to see. I want the user to get the "feeling" that there are no records in Table C when directed form an open record in Table A.

Let's assume that Table A has 250 records and that Table C has 500 records. If we  were in the process of creating Table A record 251 and once created we wanedt to create 5 related Table C records. The moment we move, through a Button attached to the Add Records Script, from Table A record 251 to Table C layout, the user has in sight the 500 records of Table C. THIS IS NOT what I want the Add Records Script to do.Once the Add Records script is triggered the user should be presented with a new record created by the Script in Table C layout. So, all the records previously created in Table C won't be on sight for the user.



  • Deviation

  • DeviationEvidence

  • Evidence

Table Occurrences:

  • deviation_deviationEvidence_EVIDENCE


DEVIATION::__kp_Deviation = deviation_DEVIATIONEVIDENCE::_kf_Deviation

deviation_DEVIATIONEVIDENCE::_kf_Evidence = deviation_deviationEvidence_EVIDENCE::__kp_Evidence

On a layout based on the DEVIATION TO there is a Portal to the deviation_DEVIATIONEVIDENCE TO of the DeviationEvidence table with two fields:

  • deviation_deviationEvidence_EVIDENCE::NameDocumentEvidence

  • deviation_deviationEvidence_EVIDENCE::DocumentEvidence

And there is the + Button that triggers the Add Records Script that will create a new record in the Evidence Table from the Portal to the Evidence Table in a layout based on the Deviation Table.


I have come up with the following Add Records Script that I don't know if it is right.

As mentioned above the script is triggered from a layout based on the Deviation_Detail TO of the DEVIATION Table.



Add Records Script

# Error Handling
Allow User Abort [ Off ]
Set Error Capture [ On ]
# Get DEVIATION::__kp_Deviation
Set Variable [ $DeviationID; Value:Get(RecordID) ]
# Create EVIDENCE::__kp_Evidence
Go to Layout [ “Evidence” (Evidence) ]
Show All Records
Show Omitted Only
New Record/Request
Set Variable [ $EvidenceID; Value:Get(RecordID) ]
Pause/Resume Script [ Indefinitely ]
Commit Records/Requests
# Assign DEVIATION::__kp_Deviation = deviation_DEVIATIONEVIDENCE::_kf_Deviation
Go to Layout [ “dev_deviation_DEVIATIONEVIDENCE” (deviation_DEVIATIONEVIDENCE) ]
New Record/Request
Set Field [ deviation_DEVIATIONEVIDENCE::_kf_Deviation; $DeviationID ]
# Assign EVIDENCE::__kp_Evidence = deviation_DEVIATIONEVIDENCE::_kf_Evidence
Set Field [ deviation_DEVIATIONEVIDENCE::_kf_Evidence; $EvidenceID ]
# Go back to the original layout
Go to Layout [ “Deviation_Detail” (DEVIATION) ]


The Add Records script seems to be wrong because doesn't assign correctly the __kp_Evidence = _kf_Evidence

For example, before starting I make sure that tehre are no records in any table and that the EVIDENCE::__kp_Evidence as well as the DEVIATION::__kp_Deviation start from 1 in increaments of 1. After creating the first record for the Deviation Table and after triggering the Add Records Script I get the following values:

Deviation::__kp_Deviaation =1

deviation_DEVIATIONEVIDENCE::__kp_DeviationEvidence = 1

deviation_DEVIATIONEVIDENCE::_kf_Deviation = 161 instead of 1 (I guess this is what it is supposed to be)

deviation_DEVIATIONEVIDENCE::_kf_Evidence = 9 instead of 1 (I guess this is what it is supposed to be)

Evidence::__kp_Evidence = 1

Obviously the Add Records Script is wrongly establishing the relationships. So, what is wrong with the script?


Once the Add Records script has been triggered and has finished, the new record created in the Deviation Table is not shown in the  Portal to the deviation_DEVIATIONEVIDENCE TO of the DeviationEvidence table. So, I must have set up the Portal erroneously. Where is my mistake?

After the Add Records script has been triggered the user ends up in the Evidence table where a new record has been created and the user has to fill in the data for the two fields on the layout based on the Evidence table. Once the user has finished entering the values in the fields, he/she  sees FM11 message "Script paused". So he/she has to select the Continue button. What should be the setting like so that the user does not see the message "Script paused" and the script continues after he selects for example a Continue button?