Adding Records via a Script

[natursalus]

Hello,

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.

Tables:

• Deviation
  
• DeviationEvidence
  
• Evidence
  

Table Occurrences:

• DEVIATION
  
• deviation_DEVIATIONEVIDENCE
  
• deviation_deviationEvidence_EVIDENCE
  

Relationships

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?

Thanks

NaturSalus

[Matthew F]

... all the records previously created in Table C won't be on sight for the user.

If you don't want to the user to see all the records then you could add two steps, "Show All Records", and "Show Omitted Only".

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?

You should split this into two scripts. End the first script instead of having a pause step. The second script would be triggered by the Continue button.

[natursalus]

Hello mfero,

If you don't want to the user to see all the records then you could add two steps, "Show All Records", and "Show Omitted Only".

I already have that part in the script.

You should split this into two scripts. End the first script instead of having a pause step. The second script would be triggered by the Continue button.

Good point. Thanks for the tip.

natursalus

[Matthew F]

I already have that part in the script.

Sorry, I missed that. The found set should just be the new record that you created. Something appears to be wrong. Since you are running FM Pro Advance, I'd suggest using the Script Debugger and step through the script to see if it is throwing an error at some point or doing something unexpected.

[David Jondreau]

Get ( RecordID ) is not what you want to be setting your $ID variables to. You should be using the appropriate _kp field for the table.

What is the Pause[] for in the script?

[natursalus]

Hello David,

Get ( RecordID ) is not what you want to be setting your $ID variables to. You should be using the appropriate _kp field for the table.

You are absolutely right, and that was the cause of all my reported problems.

What is the Pause[] for in the script?

The purpose of the Pause/Resume Script [ Indefinitely ] is to allow the user to enter the values in the fields:

EVIDENCE::DocumentEvidenceName (text field)

and

EVIDENCE::DocumentEvidence (container field)

I have a CONTINUE Button attached to the Resume Script step that the user has to click in order to land in the original layout based on the Deviatio_Detail TO.

If you can think of a more elegant way to achieve the same objective I would appreciate your sharing it.

Thanks

natursalus