How should script create new records in related file?

[PB@FBC Palo Alto]

Apologies if this is obvious to everyone else but me...

I have two files, A and B. (Each is an .fp7 file.)

In file A, each record has a unique record number.

For each record in A, there may be one or more corresponding records in B.

B has a field "RecordNumberFromA"

I want a script in A to do the following:

For the current record in A, create 1 or more new records in B.

In each new record thus created in B, I need the field "RecordNumberFromA" to contain the number of the current record in A.

My script in A successfully creates a new record in B,

BUT the new record's field "RecordNumberFromA" is blank.

(Since the field by which A and B are linked doesn't yet have a value, the script doesn't know what record in B I'm trying to set.)

How is my script supposed to set that field?

How can my script in A say "I'm talking about the new B record that was just created"??

[Josh Ormond]

In the script add the following steps (Substitute your Table::FieldNames)...

//While still in file A

Set Variable [$ID; Value: TableA::ID]

//After new record in File B is created

Set Field [TableB::RecordNumberFromA; $ID]

[bruceR]

First of all, there is no need to call a script in file B at all.

In file A you can create a file reference to B and create a table occurrence in A that shows the records from B and create a layout in A that shows these records.

Then all your scripts happen in A.

However, you can use script parameters to pass values from one script to another. So the New Record script in B can be called, with a script parameter.

[PB@FBC Palo Alto]

I'm grateful for suggestions from BruceR and jmormond. Each of you provided steps that seem simple and easy to do. Nevertheless, I'm still failing. Somewhere I must be missing a key point.

I readily get file A to create a new blank record in file B. (Or a Table defined in A so that it contains a view of B.)

But I still haven't managed to create the value that links the newly created record in B to the current record in file A.

While A is linked to B by way of the field "RecordNumberFromA"

. [color:blue]Set Field [TableB::RecordNumberFromA; $ID]

doesn't produce an error message, but nevertheless leaves the field blank (even though $ID has been set correctly).

I thought "Well, if my new record starts with a blank in the match field, how can FM know in what record it's supposed to write $ID ??"

So I created (within A) a tables that is a view of file A and another that is a view of file B, but did NOT link them. Then, trying to write into B's field RecordNumberFromA fails, I get the message "This operation could not be completed because the target is not part of a related table." OK, when they're not related, I can't write.

But when they _are_ related, how do I set the value in B's new record that will relate it to he current record in A? Is it possible some other issue is preventing writing into the new record?

If you'd care to suggest a text in which I could look for more discussion, I'd appreciate that too.

[bruceR]

I'm grateful for suggestions from BruceR and jmormond. Each of you provided steps that seem simple and easy to do. Nevertheless, I'm still failing. Somewhere I must be missing a key point.

And so are we.

Post an example of what you are doing please.

[Josh Ormond]

I could be wrong, but it sounds more like you are missing some of the basics behind relational databases. Although, that could just be me miss reading your post.

Check out this from David Kachel... White Paper For FMP Novices.

The link between the 2 records from table A and Table B happens with the one field they have in common.

TableA::ID = TableB::RecordNumberFromA

Couple of things to check:

ID Field - is this a unique number, or are you trying to use the actual record number in FM?

Script - do you have a set that sets the variable before you try to set the field value? It should look something like this:

Set Variable [$ID; Value: TableA::ID]

Go to layout ["TableB" (TableB)]

New Record/Request

Set Field [TableB; $ID]

If that doesn't get you where you want, then post a sample file.

I thought "Well, if my new record starts with a blank in the match field, how can FM know in what record it's supposed to write $ID ??"

Just to clarify, this is the purpose of setting the field "RecordNumberFromA" with the same value as the TableA::ID field.

[Merit Solutions]

You may need to set your variable to a global [$$RecordNo] if you are calling a second script.. otherwise could you create a related record in a portal of File B on a File A layout?

Btw are you dealing with files or tables?

If you have Table A and Table B:

[color:blue]Set Variable $FK [TableA UniqueField]

Go To Layout [TableB]

New Record/Request

Set Field [Table B - Table A's Number field;$FK]

Edited September 16, 2008 by Guest

[Josh Ormond]

If you use Bruce's suggestion, you can avoid the use of the global variable...by creating a TO in the file where TableA resides.

[Merit Solutions]

Agreed - that makes sense - as does passing the ID as a parameter into the New Record script

[PB@FBC Palo Alto]

I'm grateful for numerous helpful suggestions. I was indeed trying to follow advice on page 18 of Kachel's "White Paper for FM Novices."

I now have a version that works (correctly!). I don't understand why my first way failed and what seemed to me a logically equivalent substitute works. Apparently what made the difference was whether the script that contained the statement was in file A or in file B.

I have two files, A and B. I wanted a script in A to write several new records in B, each linked to a single originating record in A.

People suggested several different ways of passing the key value for the link, all of which seem valid. My initial attempts to use any of them all failed, apparently over a different issue. I used a script in file A which used the statement:

[color:blue]Set Field (B::RecNumFromA; A::GlobalRecNum) [color:red]Target field of the new record remained blank

But when instead my script in A invoked the same action by way of a script in B, things worked. Thus:

Statement in A's script:

[color:blue]Perform Script ["WriteTheData" from file: "B"]

Statement in B's script "WriteTheData":

[color:blue]Set Field (B::RecNumFromA; A::GlobalRecNum)

-- then everything worked as desired.

In short, in seemed to depend on whether that statement was part of a script in file A or of a script in file B.

[Fitch]

It failed when run from file A because to affect a record in file B using a script from file A, the record in B would have to be related to A, and at that point there was no relationship because you hadn't yet set the key field in B.

By the way, it's kind of old school to use global fields to pass data around. Where you specify Perform Script, look at that "Optional Script Parameter" down at the bottom.

Pass the ID from file A as a script parameter:

Statement in A's script:

Perform Script( "WriteTheData" from file: "B" ; Parameter: ID )

Statement in B's script "WriteTheData":

Set Field( B::RecNumFromA; Get ( ScriptParameter ) )

[bruceR]

I'm grateful for numerous helpful suggestions. I was indeed trying to follow advice on page 18 of Kachel's "White Paper for FM Novices."

I now have a version that works (correctly!). I don't understand why my first way failed and what seemed to me a logically equivalent substitute works.

And that is why we asked you to POST AN EXAMPLE.

Don't give us half the story, don't ask us to play guessing games.