9 Replies Latest reply on Apr 15, 2015 10:04 PM by Fred(CH)

    Set Field doesn't copy to other table

    lkeyes

      I know I'm missing something somewhere.

       

      I'm building a "SQL workbench" layout, which allows placing a SQL statement in one field of a two-field layout, and which displays the results in the second field. Works like a charm. But, now I'm trying to copy the SQL statement into another table (called the Buffer), which will eventually allow me to go back and retrieve previous SQL statements that I've put in. (I'm basically trying to duplicate the functionality of the FoxPro or MySQL SQL interface at the command line...)

       

       

       

      I've tried using SET Field for this, (note that it works in the Execute SQL statement, just fine (in the last statement of the script). But nothing gets copied in.


      I've tried moving to the layout for the buffer table, even though SET Field supposedly doesn't require it.

       

      Ideas?

       

       

       

      TIA -- Larry


       

       

       

       

      ExecuteSQL

      # Write the contents of the current query into the buffer.

      Go to Layout [ “Buffer” (Buffer) ]

      New Record/Request

      Go to Field [ Buffer::SQLString ]

      Set Field [ Buffer::SQLString; SQL_Experiment::Query_Field ]

      Commit Records/Requests

      Go to Layout [ original layout ]

      # Execute the current query in the buffer

      Set Field [ SQL_Experiment::Answer_Field; ExecuteSQL ( SQL_Experiment::Query_Field; "" ; "") ]

        • 1. Re: Set Field doesn't copy to other table
          BruceRobertson

          The field SQL_Experiment::Query_Field is not "in context" when you are in the Buffer layout.


          So it is time to learn about script variables.

           

          Also - set field does not require you to go to the field.


          Set Variable[ $query ; SQL_Experiment::Query_Field ]

          Go to Layout [ “Buffer” (Buffer) ]

          New Record/Request

          Set Field [ Buffer::SQLString;$query ]

          Commit Records/Requests

          Go to Layout [ original layout ]

          # Execute the current query in the buffer

          Set Field [ SQL_Experiment::Answer_Field; ExecuteSQL ( SQL_Experiment::Query_Field; "" ; "") ]

          • 2. Re: Set Field doesn't copy to other table
            lkeyes

            >>>>time to learn about script variables

             

            So,  yes...thanks, I stuffed the contents of the field into a variable,  and now it copies in just fine. 

            Too bad there isn't a Get(Context) function that would tell a bloke whether they've departed from the current context.

             

            >>> Set field does not require you to go to the field

            Yes, so I did take out the line that goes to the actual field,  however, since I need to create a  new record i'm assuming I had to

            go to the layout to do the New Record Request. 

             

            Code follows:

             

            ExecuteSQL

            #--- Write the contents of the current query into the buffer. ---

            #--- Then execute the query ---

            #
            # First store the query text in a variable.

            If [ Length (SQL_Experiment::Query_Field) > 0 ]
            Set Variable [ $txtQuery; Value:SQL_Experiment::Query_Field ]

            Else Beep

            Show Custom Dialog [ Title: "Error"; Message: "You're SQL query has no text. Please enter a SQL query in the Query box... "; Default Button: “OK”, Commit: “No”; Button 2: “Cancel”, Commit: “No” ]

            End If

            #
            # Move to the Buffer Context
            Go to Layout [ “Buffer” (Buffer) ]
            New Record/Request
            Set Field
            [ Buffer::SQLString; $txtQuery ] Commit Records/Requests
            #
            # Go back to the original layout

            Go to Layout [ original layout ]
            #
            # Execute the current query in the buffer
            Set Field [ SQL_Experiment::Answer_Field; ExecuteSQL ( SQL_Experiment::Query_Field; "" ; "") ]

            • 3. Re: Set Field doesn't copy to other table
              BruceRobertson

              FileMaker cannot read your mind or your design intent.

               

              It is your job to understand layouts and context. The information is there.

               

              For instance your command:

               

              Go to Layout [ “Buffer” (Buffer) ]

               

              This states that the target layout is based on the Buffer table occurrence.

               

              When writing your script, you knew that you started on a layout based on a different table occurrence.

               

              And you knew whether you had designed a relationship in the graph that connects these table occurrences.

              • 4. Re: Set Field doesn't copy to other table

                If I understand correclty, you have a text field with an SQL querry in it and you want to copy this data to another table.

                 

                You could just use an import using the file and import all of those fields into the second table in effect

                 

                Import from File A:table A into File A:table B

                 

                Hardline coders might find this TOO EASY... 

                 

                Next would be to treat it just like a text field and forget its SQL, that word triggers a frenzy of coding these days.

                 

                Go to layout A

                set variable $SQL to fieldSQL

                Go to layout B

                New Record

                set field SQLTEXT to $SQL

                commit record

                 

                That should be all you need

                 

                You can make that into a loop if you like if you have many records

                 

                IF I understood correctly, this will transfer the contents of the field into the new record. The fact that it is an SQL statement is irrelevant and SQL is not needed for this.

                 

                Why do you want to duplicate a FoxPro interface when FileMaker is so much more versatile and powerful (I abandoned FoxPro long before Microsoft discarded it.)

                • 5. Re: Set Field doesn't copy to other table
                  wimdecorte

                  lkeyes wrote:

                   

                   

                  Too bad there isn't a Get(Context) function that would tell a bloke whether they've departed from the current context.

                   

                  get( LayoutTablename ) is there for that reason.  But obviously since you write the script you KNOW that you are changing context... unless you forget what tables your layouts are based on

                  • 6. Re: Set Field doesn't copy to other table
                    Fred(CH)

                    I may be off-topic but... it is too important to miss it :

                     

                    I think what is actually missing on FM is a standard way to transfer a found set to one OT to another OT from the same table.

                     

                    I feel this need since i use the *ancre et bouée* model.

                     

                    Do you have a way to provide ?

                    • 7. Re: Set Field doesn't copy to other table
                      wimdecorte

                      The technique is called TOG Hopping: use Go To Related and just specify the other TO

                       

                      Caveat: the reason it is called TO Group Hopping because it is meant to be used between two TOs of the same basetable that have NO established relationship between them.  Not sure what the result will be if there is a relationship.

                      • 8. Re: Set Field doesn't copy to other table
                        Fred(CH)

                        Thank you very much !

                         

                        What is funny is i tried this with gtrr and it id didn't work, by misconfiguration of my part. i was too fastly discouraged . In particular i didn't realized the key was to specify Actual TO on the first menu instead of the destination TO.

                         

                        And now, i have one more reason to see actual TO on this local menu. I used yet for "File Hooping" (not sure it is the correct term). So, shame on me for didn't investigate more far previously because now it seem so obvious !

                         

                        A good day is beginning here, and is it thanks to you.

                        • 9. Re: Set Field doesn't copy to other table
                          Fred(CH)

                          Oh, and yes, after test it, placing the actual TO on first GTRR menu ignore the relationship with layout destination's TO, if any exist. Thus, it works exactly the same with or without relationship.