6 Replies Latest reply on Jul 19, 2015 1:26 PM by RaimieHedman

    Script Help: can't navigate to related JOIN table

    RaimieHedman

      Title

      Script Help: can't navigate to related JOIN table

      Post

       

      Okay I need some help because I can't figure out what's wrong here.

       

      I created a script to delete a join table record, but for some reason the "Go to related record" command doesn't take me to what I think is the related join table record in a portal to the 3rd table I'm joined to.

       

      This command is being executed from a button in a portal that brings up the data from the joined table (rather than the join table, itself.)

       

      I thought I created a workaround by going to the related table record, then going into the join table from that side (with a 2nd "go to related record" command) but it turns out that's deleting seemingly random join records as well. So now I'm at a loss.

       

      Long question short: when I have tables A and B joined by JOINC, how do I navigate to the related JOINC record via script to execute a command to delete it?

       

      Just for troubleshooting's sake, I included a pic of the relationships in question. I want to delete a record from "JOINWritingProjectsResearchNotes" when I'm executing it from a script on a button in a form from "WritingProjects"

       

      Script reads (I'm truncating the dialog box line here!)

       

      If [ Get ( LastMessageChoice )=1//OK was clicked] ]

          Go to Related Record [ From table: "JOINWritingProjectResearchNotes" ; Using layout: "JOINSWritingProjectsResearchNotes" (JOINWritingProjectsResearchNotes) ]

      End If

       

      relate.jpg

        • 1. Re: Script Help: can't navigate to related JOIN table
          RaimieHedman

          Just to clarify (I'm looking at my own uploaded pic):

          This script is being executed from a portal in "Writing Projects" that shows related results from "Research Notes." I'm assuming (probably falsely) that by saying "Go to Related Record [From table: "JOINWritingProjectsResearchNotes"....] I'm going to get the JOIN record that's connecting Writing Projects to Research Notes...but whenever I execute the script, it seems to delete an unrelated JOIN record...

          • 2. Re: Script Help: can't navigate to related JOIN table
            philmodjunk

            What you describe would work if you were using a button in a portal to the join table.

            What you describe won't work and here's why. Your GTRR step brings up the first related record in your join table, not one linked to the record clicked in the portal as it's based on a different table occurrence. Please note that the current portal record in Research Notes might be linked to any number of different Join table records as this is a one to many relationship so using GTRR to bring up a Research Notes table and a second GTRR to get to the join table won't work either.

            Unless you change your layout design to use a portal based on the Join table--which is often the better option, you'd need to set a variable to your Writing Projects::projectID field value. Set a different variable to your Research Notes::Research Note ID field, switch layouts and then use the variables to perform a find for the join table record.

            • 3. Re: Script Help: can't navigate to related JOIN table
              SteveMartino

              I'm curious about your relationships.  Adding to what Phil said, If the ResearchNotes is a one to many to the join table, that doesn't seem right to me.  If one note is connected to many in the join table, that seems to me like the classic:

              Invoice---->LIneItems<-------Products

              set up when you wouldn't want to delete a product from an Invoice record.

              Plus, on the other side of ResearchNotes you seem to be connected to another Table/TO.  Wouldn't deleting a record cause some cascading problems or orphan records?

              • 4. Re: Script Help: can't navigate to related JOIN table
                philmodjunk

                Steve, he isn't trying to delete a record from Research Notes, but rather a record from JoinProjectsResearchNotes.

                • 5. Re: Script Help: can't navigate to related JOIN table
                  SteveMartino

                  Ah, I thought he was jumping to Research Notes

                  I'm going to get the JOIN record that's connecting Writing Projects to Research Notes.

                  Read it wrong, thanks Phil 

                  • 6. Re: Script Help: can't navigate to related JOIN table
                    RaimieHedman

                    Wow, thanks a ton. Such a simple fix (changing the portal's table so it points to the JOIN table), and it seems all my fields still work, too!