setting related field with a script?

[hank tank]

Hi folks,

I have two files (tables), A & B. Defined a relation on A:JobID and B:JobID (int). A is the main table where I insert new records manually. Sometimes (but not always) I need to generate a related record in B but I don't know that beforehand (depends on the data filled out later)

My question is: how can I make sure that a later generated record in B will have the right JobID to keep the relation valid?

Or better: how can I generate a new record in B and set B:JobID from A?

I cannot set the related field (in with a script from the other file (in this case A)...

tnx Hank

[LaRetta]

Hi Hank,

Well, the easiest way would be to use your relationship itself.

Modify your File > Define Relationships > A to B (based upon JobID) by clicking 'Allow Creation of Related Records.'

Then simply place any fields (except JobID) from database B onto a form (or report) in database A (anywhere you need them). When the User adds any information into a 'B' field and the related record in B does not exist, it will be automatically created (and it will insert the JobID for you).

This technique of cross-placing non-Key field(s) between related dBs, using 'Allow Creation of Related' is the simplest way to ensure that 'child' records are automatically created for you.

LaRetta

[danjacoby]

For creation of new records, use a portal. This automatically enters the JobID in the new record in B.

To make sure a particular record in B has the correct JobID, create a global field in A.

If you always create records in B through a portal in A, then create a script that:

Sets the contents of the global field to the JobID you want

Opens B

Finds the record(s) you want to reset (you may have to use the two-step manual find, i.e. Enter Find Mode [Pause] and Perform Find).

Sets the JobID field to the contents of the global field in A.

If you sometimes create records in B directly, because you don't know the JobID yet, then you'll probably have to copy the global field in A and then paste it into the JobID field in B. I know there's a more elegant way, but it's late and I'm tired and someone else will add it below.

[LaRetta]

Hi,

Yes, cross-placing fields between related dBs includes portals. I should have mentioned that It was my impression that this was a 1:1 relationship which may not have a related record at all. In which case, a portal wouldn't be necessary.

Regardless where the fields from B are placed in A, be sure to click 'Allow Creation of Related Records' and a new record in B will be created automatically (inserting the correct ID).

BTW Hank, you can use a script in dB A!! If you Set Field [related dB::"any non-key field", "value"] it will create a new record in B (and insert the ID also). I just love the simplicity of this principle

LaRetta