9 Replies Latest reply on May 24, 2013 7:00 AM by James_L

    Complex Portal Relationships: Writing and Editing with a Script

    James_L

      Title

      Complex Portal Relationships: Writing and Editing with a Script

      Post

           I've got a sort of complex relationship between three tables: 

      (Relationship Image Here)

           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.

           Any ideas? 

        • 1. Re: Complex Portal Relationships: Writing and Editing with a Script
          philmodjunk

               Is the portal shown, a portal to Vendor | Vendee or a portal to Persons?

          • 2. Re: Complex Portal Relationships: Writing and Editing with a Script
            James_L

                 Portal to Persons.

            • 3. Re: Complex Portal Relationships: Writing and Editing with a Script
              philmodjunk

                   Which explains how entering data in one of these fields automatically creates a new record in both the Persons and the Vendor | Vendee tables. Presumably, "Allow Creation..." has been enabled for both table occurrences of these tables in the relationships you posted.

                   That makes adding a new Person record easy, but how would you link in an existing person record?

                   That generally requires a portal to Vendor | Vendee instead of Persons.

                   See this demo file and not the + buttons used to add new records into the table on the far side of the join table:

              https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

              • 4. Re: Complex Portal Relationships: Writing and Editing with a Script
                James_L
                     
                          Which explains how entering data in one of these fields automatically creates a new record in both the Persons and the Vendor | Vendee tables. Presumably, "Allow Creation..." has been enabled for both table occurrences of these tables in the relationships you posted.
                      
                     Correct: that's a feature, not a bug, for this particular screen.  
                      
                          
                               That makes adding a new Person record easy, but how would you link in an existing person record?
                          
                                
                          
                               That generally requires a portal to Vendor | Vendee instead of Persons.
                     

                     Well, for my needs (tracking legal records of property sales), it's typical that I'll need to take a group of sorted Vendors and copy those records, simply changing them from Vendees to Vendors (buyers to sellers) as they buy and sell a piece of property. 

                     But it's not really necessary that I link in an existing person, since each legal record is unique.  That is, it's essential for each record that I record in "Persons" table to appear exactly as it appears in the legal document, even if it's misspelled or a typo.  So while I allow myself a script to convert Vendees in one record to Vendors in the next record, it's just a convenience.  If there were even a slight change in spelling of a name or a dropped middle initial, I would instead manually create a new unique Persons record along with a new unique Vendor record. 

                     If I wanted to, I could use a drop down or autocomplete in those fields using the Persons records: I might in the future, but since there are so many Persons records, a dropdown is impractical and autocomplete can lead to major errors in transcription for a careless user.

                     My real problem is just scripting the loop described above: writing to a field in Vendors linked to the current Persons portal row, creating a new portal record, getting its Key field / Serial number, and writing that back into a field in the first record.  It's not a difficult concept, but for some reason it's giving me huge fits as I try to script it. 

                • 5. Re: Complex Portal Relationships: Writing and Editing with a Script
                  philmodjunk

                       And changing a group of vendors to Vendees is linking an existing set of Person records to a different COB record is it not?

                       Otherwise you do not have a many to many relaitonship and you can merge the Vendors|Vendees into a single table.

                  • 6. Re: Complex Portal Relationships: Writing and Editing with a Script
                    James_L
                         

                              And changing a group of vendors to Vendees is linking an existing set of Person records to a different COB record is it not?

                         Yes, though to be clear, I wouldn't "change" those records so much as duplicate them with two fields differing: one field "Vendor | Vendee" changes, as does the COB_ID_FK field. So Vendor | Vendee record 1 links John Smith in Persons to COB Record 1 as a Vendee, but record 2 links him to COB Record 2 as a Vendor.

                         

                               

                    • 8. Re: Complex Portal Relationships: Writing and Editing with a Script
                      James_L

                           Okay, I think I must be dense. I went over your demo (which is very cool), and I'm fixating on the "+" button you pointed out.  

                           I'm not sure I understand that script's use of the Get (ScriptParameter) step.  I've read the handbook on it (not terribly helpful) and your comment in the script itself about why you use it.  But I'm not sure what it returns precisely or why.  Can you explain that? 

                           Edited to add:  Never mind: I can see it now.  Script parameters: next big learning project.  Looks like powerful stuff, and I really wish I'd understood them before I started doing scripts!  

                      • 9. Re: Complex Portal Relationships: Writing and Editing with a Script
                        James_L

                             So I gather that the major difference between your solution in your demo and my arrangement is that your portal opens to the join table (Contact Events in yours,  Vendor | Vendees in mine), not to the target table, while mine opens to the target (Contacts in yours, Persons in mine). 

                             So if I hear you rightly, you're suggesting I use your method or similar to make the join table the target table.   Is that right?   And you're suggesting that that would allow me greater editing flexibility in the join table through the portal? 

                             (I think I tried that a few versions ago, but I didn't know nearly enough to make it work.)

                             Sorry so dense, Phil: it's genetic, not intentional.