Trouble with New Record Creation

[PerryL7]

Hey there,

I am having problems creating a new record in a related file. I have a database of recruits and all of their information that is related to a high school database table containing addresses and other contact information. I do a lookup of information and am able to update the HS DB from the Recruit DB using the "Go to Related Record" script.

The problem I am running into is that I cannot seem to create a new record in HS DB file. I want to do this from the Recruit DB if I enter the information and the HS isn't found in teh HS DB. I have tried many different scripts. They usually start with "Open File". When I try the "New Record/Request" but it creates a new record in the Recruit DB.

Is there anyway to create a new record in a related table from the Recruit DB using a script? Preferably without using a portal. Thanks for your help.

Edited March 10, 2006 by Guest

[Ender]

To create records in a second file, you should execute a subscript in that file that actually creates and modifies the record. If you're tying it to the record in the original file, then you should store the primary ID of the parent record in a global or a script variable, so it can be set as the foreign key in the new record.

[PerryL7]

That worked great. Thanks Ender.

[Llyndonium]

Hi

That sounds like something that will help my problem too but I don't know how to go about actually setting it up. I also need to create a new record in a related file. Can you show me step by step please?

Lyndon

[PerryL7]

Ok, here is it:

Note:

Source Table = Table you are working in

Destination Table = Table in destionation file to create record in

1: In the Destination File, create a relationship between the Destination Table and the Source Table

(My realtionship was based on an ID field that was unique)

2: In your script in your Source File, choose Perform Script function to call the Create New Record script in your Destination File

3: In the source script pass a parameter that conatins the ID of the record in the Source Table for reference

4: In your Destination Script, create a new record request and get the passed in parameter

5: Set the ID field in your Destination table to the Parameter. (We now have a relationship between the two tables)

6: Use the SetField function to set the Destination Fields equal to whatever information you want from the Source Table

7: Commit your new record

Hope this helps.

-PerryL7

[Llyndonium]

Thanks for that! But how do I 'pass the parameter'? Ie what is the script command - I'm choosing from the drop down list in scriptmaker. I think everything else is fine

Cheers, Lyndon

[PerryL7]

I don't know what FM6 is like but in FM8 when I choose the Perform Script function, I can choose the script from the other file and add an Optional Parameter at the bottom of the screen. Look for ScriptParameter in your help.

-PerryL7

[Ender]

In FM6, you will need a global field in the source file. Set the primary ID of the source record to that global field, then once the record is created, set the foreign key to the value of that global (you will need a relationship back to the source file so that the global will be accessible.)

[Llyndonium]

In FM6, you will need a global field in the source file. Set the primary ID of the source record to that global field, then once the record is created, set the foreign key to the value of that global (you will need a relationship back to the source file so that the global will be accessible.)

Sorry, just me being a bit thick..! When you say 'set the primary ID' - how do I do that? In the relationship or in a script - I tried the relationship but it says it cant index the global field

Lyndon

[Ender]

Something like this:

//Add Related Record script in parent file

Set Field [ gTempID ; RecordID ]

Perform Script [ external (Child): Create New ]

//Create New script in child file

Enter Browse Mode

New Record/Request

Set Field [ ParentID ; ParentFile::gTempID ]

...

[Llyndonium]

OK, I now have info coming into the fields but for some reason it's pulling in data from other records in the parent file, not the one that I came from! Any ideas?

[Ender]

Is that gTempID a global??

[Llyndonium]

Thaks for all your help guys!

OK, I think I sorted that one - I assume you need a unique ref in the global to identify the right record? I'm now using the job number (unique) and it seems to be OK now.

However, the next problem is that I can't pull in the customer name and address from the job layout (parent) because they themselves are related refs from another file called customers. Should this be a separate post do you think?

Lyndon