11 Replies Latest reply on Mar 6, 2012 7:24 AM by LyndsayHowarth

    Script creates record in wrong table

    sneff228

      Script creates new record in wrong table

       

      I have a database set up with multiple related tables for parents to rate their youth's strengths. Parents have many youth, so I have a relationship set up where one parent has many youth. Each youth will have a set of strengths ratings. The strengths are then broken up into 9 categories, each with their own table (i.e. communication). The Youth table and the communication table are joined by the strengths table, where the relationship between the youth and the strengths is matched by the youth key, and allow creations of records is checked under the strengths table only; and the relationship between the strengths and communications table is matched by the youth key and allow creation of records is checked only under the communication table. I also have a main family table, because at some point I will add multiple parents to the same family. The family table joins the parent and youth tables.

       

      My problem is this: I have a script set up for the parent to choose the youth they want to rate, then find an existing communication record that matches that youth, or create a new record if one does not already exist.

       

      As it stands, my script will find an existing record, but does not properly create a new communication record in the event that one does not already exist. Instead, it creates a new record in the youth table, but with a new youth key. Strange right? It gets stranger, if I go back to that same layout and click on the script button again, it creates a related record in the communication table. Why does this not work the first time I click on the script button? Here is my scrip, and the scripts within the script. Please advise.

       

      Go to Main Strengths Record (this script looks for an existing related record or creates a new record)

      Perform Script [ “Set Youth ID in Strengths” ]
If [ communication::kfYouth = MainFamily::kfYouth ]

      Go to Related Record [ From table: “communication”; Using layout: “Comm. Identify” (communication) ] [ Show only related records ]

      Else
Perform Script [ “Create New Communication Strengths Record” ]

      End If

      --------------

      Set Youth ID in Strengths (this script defines which youth the parent has chosen from a radio button field)

      Set Field [ MainFamily::youthID; MainFamily::allyouthnames ]

      Set Field [ MainFamily::kfYouth; MainFamily::youthID ]

      Set Field [ MainStrengths::kfYouth; MainFamily::youthID ]

      -------------

      Create New Communication Strengths Record (this script should create a new record in the communication table)

      Set Variable [ $UID; Value:MainFamily::kfYouth ] Go to Layout [ “Comm. Identify” (communication) ] New Record/Request
Commit Records/Requests

      [ No dialog ]
Set Field [ communication::kfYouth; $UID ]

      Set Field [ communication::kfYouth; MainFamily::kfYouth ]

        • 1. Re: Script creates record in wrong table
          Mike_Mitchell

          Hello, sneff.

           

          I can't tell for certain from what you've listed here, but when a script creates a record in the wrong table, it's almost always a context problem - you're on the wrong layout - or you're setting a field through a related field to a nonexistent record with "Allow creation" checked (which is also often a context problem).

           

          In this case, I suspect your problem is coming from the latter. Here's why:

           

          1) You state you're creating a new Youth record when it fails, with a new Youth ID.

          2) Your first script is messing with the Youth ID field.

           

          What I would do is check to make sure you're not accidentally creating the record via relationship. My first guess.

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Script creates record in wrong table
            sneff228

            Thanks for answering so quickly Mike, that helped part of the problem.

             

            I unchecked the allow creation of records via this relationship and it no longer creates an extra youth record. But, now it doesn't create a related record in the communicaiton table, it just makes a blank communicaiton record. The script was creating a record like this in the communicaiton table before at the same time it created the new youth record.

             

            Thoughts?

            • 3. Re: Script creates record in wrong table
              sneff228

              Sorry, just realized my scripts were jumbled in my first post, here is a cleaner version

               

              Go to Main Strengths Record (this script looks for an existing related record or creates a new record)

              Perform Script [ “Set Youth ID in Strengths” ]

              If [ communication::kfYouth = MainFamily::kfYouth ]

                    Go to Related Record [ From table: “communication”; Using layout: “Comm. Identify” (communication) ] [ Show only related records ]

              Else

                    Perform Script [ “Create New Communication Strengths Record” ]

              End If

              --------------

              Set Youth ID in Strengths (this script defines which youth the parent has chosen from a radio button field)

              Set Field [ MainFamily::youthID; MainFamily::allyouthnames ]

              Set Field [ MainFamily::kfYouth; MainFamily::youthID ]

              Set Field [ MainStrengths::kfYouth; MainFamily::youthID ]

              -------------

              Create New Communication Strengths Record (this script should create a new record in the communication table)

              Set Variable [ $UID; Value:MainFamily::kfYouth ]

              Go to Layout [ “Comm. Identify” (communication) ]

              New Record/Request

              Commit Records/Requests [ No dialog ]

              Set Field [ communication::kfYouth; $UID ]

              Set Field [ communication::kfYouth; MainFamily::kfYouth ]

              Go to Layout["comm. Identify" (communication]

              • 4. Re: Script creates record in wrong table
                Mike_Mitchell

                sneff -

                 

                Is it creating a new, empty record, or do you just have a dead record in there?

                 

                What table are you starting the script on? communication, or MainFamily?

                 

                I do see where you're testing for the presence of a related record using:

                 

                If [ communication::kfYouth = MainFamily::kfYouth ]

                 

                That may not work; I usually use

                 

                If [ not IsEmpty ( relatedTable::keyField ) ]

                 

                One other thing to try would be to move the Commit Records / Requests step after the two Set Field steps (or eliminate it completely; it's not needed because the record will be committed when you switch layouts).

                 

                Try those things and let's see what we get.

                 

                Mike

                • 5. Re: Script creates record in wrong table
                  sneff228

                  It is a new empty record. It has the serial number that auto-generates int he communicaiton table, but not a related youth key.

                   

                  I am starting the script on the MainFamily table.

                   

                  I tried changing that step to

                   

                  If [ not IsEmpty ( relatedTable::keyField ) ]

                   

                  but the script still creates a blank record.

                   

                  I disabled the commit record step.

                   

                  Still have the same problem.

                  • 6. Re: Script creates record in wrong table
                    Stephen Huston

                    Before the script step to go to the related records, you need to do an IF testing for a count of the reelated records, and go to them IF they exist, ELSE go to the layout for the related record and process the new record there.

                     

                    Your script appears to rely on the GTRR step to get to the correct layout, but it won't go there via GTRR if there are not RR to GT.

                     

                    So: (pseudo script language)

                          If Count (related::foreignKeyField)

                              GTRR

                         Else

                              Go to Layout (GTRR target layout)

                              Create and process new related record

                     

                    The problem when you get a new record in the wrong table is almost always going to be a matter of being in the wrong place at the wrong time (layout).

                    • 7. Re: Script creates record in wrong table
                      DaveRawcliffe

                      Hi Sneff;

                       

                      I hope I am reading what you said correctly.

                       

                      It might help you if you merge the 3 scripts to see the flow:

                       

                      You have your setup start:

                       

                      # Set Youth ID in Strengths (this script defines which youth the parent has chosen from a radio button field)

                      # to which i would move to here:

                      Set Variable [ $UID; Value:MainFamily::kfYouth

                      #

                      # After that you have a standard:

                      #

                      If [ not IsEmpty ( related::foreignKeyField ) // Trap for exists ]

                        the GtRR portion

                      Else

                      #MERGED Create New Communication Strengths Record (this script should create a new record in the communication table)

                        Go to Layout [ “Comm. Identify” (communication) ]

                      #

                        New Record/Request

                        // Commit Records/Requests [ No dialog ]

                        Set Field [ communication::kfYouth; $UID ]

                      #

                        Commit Records/Requests [ No dialog ]

                      #

                        Set Field [ communication::kfYouth; MainFamily::kfYouth ] <- aren't these =  Set Field [ communication::kfYouth; $UID ?

                      #

                      End If

                      Select Window ( Current Window ) // Establish focus

                      #

                       

                      Dave

                      • 8. Re: Script creates record in wrong table
                        Malcolm

                        I have two solutions:

                         

                        1. Create all your child records via portals. You may want to create a layout solely for this purpose

                        or

                        2. Obtain all the data you need then go directly to the child tables and create new records adding the linking IDs and other data

                         

                        Malcolm

                        • 9. Re: Script creates record in wrong table
                          LyndsayHowarth

                          I agree with Malcolm on #1 Create all your child records via portals.... not the second statement.

                          This is always the better methodology to use. You don't have to do it via the communication:: relationship. I would create another relationship for "Communication NEW" which adds an extra criteria for matching that is impossible (I like to make a calculated "New" field which matches to a status field... for which the status gets changed during a second edit) and all you need then on the layout is to add that field somewhere hidden which is set by script to the ID and committed and not enterable in browse mode.

                           

                          Malcolm wrote:

                           

                          1. Create all your child records via portals. You may want to create a layout solely for this purpose

                          • 10. Re: Script creates record in wrong table
                            sneff228

                            Lyndsay,

                             

                            I tried to recreate what you said, but I keep getting a message that says

                             

                            This operation cannot be performed because one or more required related records are not available and cannot be created.

                            • 11. Re: Script creates record in wrong table
                              LyndsayHowarth

                              Not sure what you have done here to get that kind of error. perhaps you were trying to create some sort of circular reference...

                               

                              If you want to perhaps take some screenshots of the relevant relationships and portal placement and post them.... or better still a copy of what you have done... that would help.

                               

                              - Lyndsay