1 2 Previous Next 18 Replies Latest reply on Sep 2, 2016 12:18 PM by goutam

    Set field by name script not working

    goutam

      I have a layout displaying a form. I want to automatically update the field to a particular id based on who has logged in.

       

      The layout contains the field __pkStudentId in tblHours

       

      I have this script on enter of the layout

       

      Set Field By Name [tblHours::__pkStudentId; Get ( AccountName )]

       

      This does not work. Am I doing anything wrong? How to rectify it?

        • 1. Re: Set field by name script not working
          philipHPG

          Are you sure Set Field By Name is the script step you really want to use?

           

          Probably you intended:

           

          Set Field [ tblHours::__pkStudentId ; Get ( AccountName ) ]

           

          Set Field By Name expects that the first parameter will identify a field name. So, if the tblHours::__pkStudentId field contained the name of a field, it would work, but I believe you want to set the tblHours::__pkStudentId field, not use it to set another field.

           

          In general, there are very few situations where you would actually want to use the Set Field By Name script step.

           

          Also, I'm a little concerned that you are apparently changing a primary key. Normally a primary key value should not be changed.

          • 2. Re: Set field by name script not working
            goutam

            I will disable edits to that field once the value id populated.

             

            I also was wondering, I obtained the value of student username using the above function Get ( AccountName) and now I have to query the Student table based on the username value and assign the student id to that field. I am not able to use the ExecuteSql function and assign it.

             

            How do I go about doing that?

            • 3. Re: Set field by name script not working
              David Moyer

              sorry to butt in ...

              you would need the Execute SQL script step instead (to do anything but SELECT).

              Let me rephrase that ...

               

              Use the script step if you want to UPDATE, INSERT or DELETE with an ODBC data source.

               

              Use Import from ODBC data source to SELECT from an ODBC data source.

              Use the function for SELECT-ing from a FM table occurrence.

              1 of 1 people found this helpful
              • 4. Re: Set field by name script not working
                philipHPG

                Can you explain a little more about what you are trying to do?

                 

                When you say that you "have to query the Student table based on the username value and assign the student id to that field", to what field are you referring? What field are you querying in the Student table and what field are you trying to set?

                 

                It also seems very likely then that tblHours::__pkStudentId is not a primary key in tblHours. It may be a primary key in the Student table, but is most likely a foreign key in tblHours (contrary to how it is named). Changing a foreign key in tblHours could break your relationships with the Student table.

                 

                Please explain further what you are trying to do and how your database is currently structured so we can better give you advice.

                • 5. Re: Set field by name script not working
                  goutam

                  You are right!

                   

                  In the code, to try out, I tried using the Set Field [ tblHours::__fkStudentId ; Get ( AccountName ) ]. I mistyped it as __pkStudentId, it is __fkStudentId.

                   

                  Now that I have got the username (emailid in the tblUsers) using Get ( AccountName). I want to query the tblUsers using the username I obtained using Get ( AccountName) and get the __pkStudentId and assign the value to __fkStudentId and disable modifications to that field.

                  • 6. Re: Set field by name script not working
                    philipHPG

                    In this context, if you want to determine the StudentId of the current account, you could use the following ExecuteSQL function:

                     

                    ExecuteSQL (

                    " SELECT __pkStudentId

                      FROM tblUsers

                      WHERE EmailId = ?" ;

                      "" ;

                      "" ;

                      Get ( AccountName )

                    )

                     

                    However, this will only look for the currently signed-in account.

                    • 7. Re: Set field by name script not working
                      goutam

                      Thank you for the reply. This is an SQL function that has to be used in the calculation. How do I set the field value in the script using this result? I thought I had to use an ExecuteSql script step to do this.

                       

                      Correct me if I am wrong. I am a very basic FileMaker user.

                      • 8. Re: Set field by name script not working
                        philipHPG

                        Use a Set Field script step. It is not necessary to use ExecuteSQL.

                         

                        Set Field [ tblHours::__fkStudentId ; Value:  ExecuteSQL ( " SELECT __pkStudentId  FROM tblUsers  WHERE EmailId = ?" ;  "" ;  "" ;  Get ( AccountName ) ) ]

                        • 9. Re: Set field by name script not working
                          goutam

                          Sorry for replying late. I tried using your query and I am not able to use the "Value: " before the ExecuteSql part of the statement.

                          • 10. Re: Set field by name script not working
                            philipHPG

                            Sorry, that was my fault. With a Set Variable script step, there is a Value: option, but that doesn't occur with the Set Field script step. I confused those two script steps. So for the Set Field script step just ignore the Value: portion of what I indicated and use the rest.

                            • 11. Re: Set field by name script not working
                              David Moyer

                              Hi,

                              ExecuteSQL requires three (or more - four for you) parameters - I only see two in your screen shot.  If that's it, change the bottom line to:

                              ;""; ""; (AccountName))

                              • 12. Re: Set field by name script not working
                                goutam

                                I am just getting a ? value set to the field, this is the query I am using

                                ExecuteSQL ( "SELECT tblUsers::__pkStudentId FROM tblUsers  WHERE EmailId = ?"; "";""; Get (AccountName))

                                 

                                or Should I use this

                                 

                                ExecuteSQL ( "SELECT __pkStudentId FROM tblUsers  WHERE EmailId = ?"; "";""; Get (AccountName))

                                • 13. Re: Set field by name script not working
                                  philipHPG

                                  Yes, please use the format I indicated. tblUsers::__pkStudentId is FileMaker formatting. The SQL equivalent is SELECT __pkStudentId FROM tblUsers

                                  • 14. Re: Set field by name script not working
                                    David Moyer

                                    Um, definitely try replacing :: with a period.

                                    Here's the official reference:

                                    https://fmhelp.filemaker.com/docs/15/en/fm15_sql_reference.pdf

                                    1 2 Previous Next