Scripting: a new record in a related file

[Landphil]

I am using FMPro 5.5 on a PC in case that matters.

I have a relational DB that has many files but my question regard only 2 of them. The two files are connected by a serial number. I already have a button that executes a script that takes the user viewing a particular record to the related record in the related file. That much I have down.

What I need is a script that would do this:

--If there is already a related record in the related file, will take the user to view that record in the related file........

--If there is not yet a related record in the related file, will recognize that fact and go ahead and create a new record (with all of the accompanying info) in the file that previously had no related record.

Sorry if that is confusing.

Using the ScriptMaker, I see things like "Create new record," "Go to related record" but nothing that says "go to related file and check for existence of related record." I think I know how to check for the existence of a record (if fieldname=""): but I do not know how to jump to the related file!

Any help would be great, thanks.

[BobWeaver]

You can check for the existence of related records using the Count() function

If [Count(RelationshipName::KeyField)>0]

[Landphil]

This would create a new record in the database file I am currently in, I need the new record created in the related file and do not know how to get over to that file before creating the new record if one does not already exist.

Thanks for your help though. Any other thoughts?

[Mike D.]

I think you missed what Bob meant in the Else portion of the script. You should use the Perform Script step to execute an external script located in the related file that creates the new record.

If [Count(RelationshipName::KeyField)>0]

# Record exists so go to it

Go to Related record [RelationshipName]

Else

Perform Script [sub-scripts, External:RelatedFile] <-- This steps calls a script that creates a new record in the related file.

End If

HTH, Mike

[John Caballero]

Landphil said:

This would create a new record in the database file I am currently in, I need the new record created in the related file and do not know how to get over to that file before creating the new record if one does not already exist.

Thanks for your help though. Any other thoughts?

There doesn't have to by a related record in your related file, heck there don't have to be any files in the related file in order to execute a script. As others have suggested, you want to use the count function to see if there are related records, and if not, then call up a script in your related file that creates a new record. Scripts don't need to have records present to execute...unless you specifically tell them not to.

Make sure your parent file and the related file both have a calculation field that returns a value of 1 - this means every record in both files will always have a field in common. In your related file, create a relationship back to the parent file that uses the 1 calculation as the join key - this is commonly referred to a constant-to-constant relationship. You'll see why you need this in a minute.

If there are field values in your main file that need to be carried over to the related record, create an additional relationship from the related file to the parent file that is based on the shared serial # field.

In your parent file, create a global text field.

So...your script has determined you need a new related record. Before you call up the script in the related file to create a new record, set the serial # of the current record (use the Set Field script step) into the global text field. This field acts as a temporary holding field.

Then call up the script in the related file that will create a new record. The next script step is to set the serial # field value (in the related file) with the value that is being held in the global text field of the parent file. You acquire that value by "looking through" the constant-to-constant relationship that you have with the parent file. Again, use the Set Field script step for this.

Now the relationship from your related file back to the parent file is valid because they both have the same value in their respective serial # fields. If you need to capture additional information from the parent file, you can acquire those values by "looking through" the serial #-to-serial # relationship.

[BobWeaver]

Actually, if the relationship to the child file is configured to allow creation of related records, then all you have to do to create a record in the related file is to perform a set field command in the parent file. You don't need to perform any external subscripts.

If [Count(RelationshipName::KeyField)>0]

[John Caballero]

Yeah...I realized that a few minutes after I posted. I hardly ever (never, mabye?) use the auto-create related records option in my relationship definitions. Mostly that's just an aesthetic thing for me when it comes to portals.

[BobWeaver]

The only thing you have to be careful of is that there aren't any existing related records. Otherwise, data in an existing record would be overwritten. However, since the script checks for the existence of related records first, there should be no problem.

As for the esthetics of showing a blank portal row, you can have two copies of your relationship, one with creation of related records allowed, and one without. Then use whichever one is appropriate for the circumstance.

[Oldfogey]

Just a minor postscript.

You don't need a Count function.

If(RelationshipName::LinkField)

will do the trick. It returns True if the record exists and False if it doesn't.

[Landphil]

Aha, that is what I was missing, executing a script in the related file instead of a script in the current file. Didn't even know I could do that. Thanks to all, you guys rule.