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

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

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.

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