You have a "can't get there from here" situation. Set field can't enter data from Donations until _fkPartnerID gets the correct value to link the new record to the correct record in Donations. And it can't enter data from Correspondence until _fkCorrespondenceID gets the correct value to link this same new record to the correct record in Correspondence.
So it can't directly enter data from either related table until another method is used to enter the needed ID values.
Assuming that this script starts on the Donations layout, you need to add this as the very first script step:
Set Variable [$DonationID ; value: Donations::_fkPartnerID ]
Then add this step immediately after the new record step that creates a new record in the Correspondence table:
Set Variable [$CorrespondenceID ; value: Correspondence::__pkCorrespondenceID ]
These are the steps that take the place of "copy" in your original copy and paste script.
then your set field steps become:
Set Field [PartnerCorrespondence::_fkPartnerID ; $PartnerID ]
Set Field [PartnerCorrespondence::_fkCorrespondenceID ; $CorrespondenceID ]
and you will not need the commit records steps after each set field step.
PS, I'd also replace the steps in your script that start with the word "insert" with set field steps as well. Like copy and paste, insert steps will not work unless the specified field is present on the current layout and enabled for Browse mode access.
Set Field [ Correspondence::Date ; Get ( CurrentDate ) ]
Set Field [Correspondence::Type ; "Thank You Note"]
Set Field [PartnerCorrespondence::Notes; "For Donation ID " & Donations::DonationID ]
Thanks, works brilliantly! Just so I understand correctly for future reference: Set Field cannot display related records by itself, only records from the current table, correct?
That is not correct. Note that one of the set field steps that I recommended you use refers to a field from a related table.
Take a look at your PartnerCorrespondence layout immediately after using New Records from the records menu to create a new record.
Note that all your fields except any with auto-enter field options are empty. There's no data in either _fkPartnerID nor _fkCorrespondenceID. WIthout a value in _fkPartnerID, the record is not linked to ANY record in donations. Without that link any calculation in any script step that refers to field in Donations will evaluate as though the field from donations is empty. The same is true for accessing data in the correspondence table from this record.
Once you have the links established by using set field to enter values into these _fk fields, then your calculations and this layout can access data from related records in those two tables.
Ah ok, that makes sense. Thanks so much for the help and explanation!
Actually, on further review, the script has a little problem. It creates the exact number of PartnerCorrespondence records as the found set in donations, however, they all have the same value for the _fkPartnerID field (of the record that is selected when the script is run). So say I have 10 different donors for last month, I'm getting 10 PartnerCorrespondence records but all with the same _fkPartnerID. And also the calculated result of "Donations::DonationID" in the "PartnerCorrespondence::Notes" field is the same in all as well. What did I do wrong?
Well I'd say it was what did I do wrong as I should have spotted that issue.
Move the first set variable step down until it is the first step after Loop. That way we "copy" a new ID into the variable each time the loop moves to a new record in the donations table.
I was just about to try that before you responded. That solved it!