Complex Portal Relationships: Writing and Editing with a Script
I've got a sort of complex relationship between three tables:
In a layout for COB_Detail, I've got a portal that filters for related Vendors and another that presents Vendees. The portal returns, though, their first and last names from the Persons table: I can enter new Persons records that automatically create related Vendor / Vendee entries. Here's the rough-out with some test-data:
Here's what I want to create: the right-hand button in the portal labeled W/O/A (Wife of and") should run a script that writes to a field in the current portal record, but in Vendors | Vendees, NOT in the Persons table that's being displayed; and then it should create a new record in, well, two related tables at once (Persons and Vendors | Vendees), as I'm usually doing when I write in this portal.
The Object of the Script: Each COB_Detail entry contains multiple Vendors and Vendees; People might be Vendors in some transactions and Vendees in others. What I want to accomplish is establishing marriage relationships between entries in the COB_Detail table (not the Person's table, since a divorce would scramble other COB_Detail entries using the same Persons Table entries... marriage status is reflected in COB entries, not Persons entries).
Since ordinarily I enter names of Vendors and Vendees in this portal, the best solution I've come up with is entering the wife's Vendor | Vendee and Person record first, then clicking a button that marks the wife as "Wife" in the W/O/A field, creating the husband's record, and then entering his information. Because I'm working with paper data that's in that form (Wife's name, Marriage declaration, Husband's name), this is best for the user.
So here's what the script should do if I press that button next to Testily Girlfriend Winsome Veranda's name:
(1) It writes "Wife" in the current Vendor | Vendee field "W/O/A." So far, no problem. I can script that.
(2) It saves the current record's COB_ID_FK as a variable, creates a new record (for the husband) in the Vendor | Vendee table, and writes that variable into the new record's COB_ID_FK record so that husband and wife are part of the same COB transaction; so far, no problem. I can script that.
(3) It then returns to Testily's Vendor | Vendee record, writing the NEW (husband's) record's Vendor | Vendee ID in as the field "Spouse_Record";
(4) It then delivers the user safe and sound in the portal, ready to enter the first name of Testily's husband (in line 4 of the portal pictured above).
These last two steps are the trouble, as what seems to keep happening is that I create a new Vendor | Vendee record just fine, writing some data in it, and then ending back in my portal but creating a _third_ record, unrelated to Testily's record.
I've tried on about six different solutions, here, but I confess myself flummoxed.