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.
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?
1 of 1 people found this helpful
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.
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.
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.
In this context, if you want to determine the StudentId of the current account, you could use the following ExecuteSQL function:
" SELECT __pkStudentId
WHERE EmailId = ?" ;
Get ( AccountName )
However, this will only look for the currently signed-in account.
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.
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 ) ) ]
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.
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))
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))
Yes, please use the format I indicated. tblUsers::__pkStudentId is FileMaker formatting. The SQL equivalent is SELECT __pkStudentId FROM tblUsers