ExecuteSQL Sum Breakdown

[WSaxton]

Ok Ladies and gentlemen, so I’ve dipped my toes into wanting to start using the ExcuteSQL Function, I have no idea what I’m doing with it. I downloaded Seed codes SQL Explore and I am more lost than ever. I don’t understand the function and I am hoping someone can break it down for me. So something simple to start with. If I wanted to get a total value of a field in a related recorded normally I would just use

 

Sum(TestRecord::Field)

 

This would give me a total

 

How the calculation above gets broke down into

 

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

 

Is beyond me.

 

Can someone explain?

[Wim Decorte]

sqlQuery is going to be ... well... your SQL query.  For that you need to brush up on that skill: http://www.w3schools.com/sql/

And ready everything here: http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

fieldSeparator and rowSeparator you can typically leave blank ("") so that FM will use the defaults: comma for the field delimiter and return for the row delimiter.

Argumenst works like this:

ExecuteSQL( "SELECT * FROM relatedTable WHERE foreign_key = ?" ; "" ; "" ; 12345)

(relatedTable and foreign_key are fictitious table names and field names obviously)

By having the " = ?" in the SQL query and then passing 12345 as the argument at the end, FM will automatically quote and format that 12345 value appropriately for whatever data type the foreign_key field is, very hand so that you don't have to remember what format SQL uses for dates, times, timestamps, numbers, strings,...

 

 

And to give you some hints what to look for to you get your SUM() equivalent going:

- you will not need a SQL JOIN

- look into the SQL GROUP BY clause if you want to do things that look like FM's subsummaries.

[WSaxton]

Wim Decorte

Thank you for the Help! ill start looking at that reference material.