We have reset all users FileMaker related profile fields. Please take the opportunity to update your information,  this will provide background to members whom read your posts. Click here.

Jump to content
aguest

ExecuteSQL calculation

Recommended Posts

aguest    1

Hi,

It's my first time using executesql and I'm struggling with getting the results to show in my field. I basically want to get the latest record, based on the "obsdate" field for a given staff member from a table where they have numerous observations. I then want to show information from this record on a portal which will show the latest obs date for each member of staff which can then get sorted by date.

the code I've written is below but would return the key and date. How do I then loop through this to return the information I need?

 

Thanks in Advance.

Let ( [SQLString =
               ExecuteSQL ( "Select [__fk_Staffteacher], max(ObsDate) from tbl_TeacherEvaluationForm group by [__fk_staffteacher]"; ""; "" )
         ] ;

Evaluate(SQLString)
)

 

Share this post


Link to post
Share on other sites
Kris M    33

Let ( [

SQLString = ExecuteSQL ( "Select \"__fk_Staffteacher\", max(ObsDate) from \"tbl_TeacherEvaluationForm\" group by \"__fk_staffteacher\""; ""; "" )
         ] ;

Evaluate(SQLString)
)

escape quote tables and fields lead by underscores instead of brackets

http://www.mightydata.com/blog/filemaker-pro-12-adds-native-sql-queries/

 

also __fk_Staffteacher is not equal to __fk_staffteacher

Edited by Kris M

Share this post


Link to post
Share on other sites
comment    1,357

What is the point of using the Evaluate() function here? One would expect something more along the lines of:
 

Let (
SQLCommand = ... 
;
ExecuteSQL ( SQLCommand )
)

It would also be advantageous to calculate the field names using the GetFieldName() function, to protect the calculation against future field name changes.

Share this post


Link to post
Share on other sites
bcooney    99

I would like to confirm something. It seems that GetFieldName ( ) is context independent. I would like to write robust sql using getfieldname( ) but was stuck thinking I'd need a relationship to the field. Can someone confirm that is not necessary.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Guidelines.