SQL is executed in FM, not back end SQL server if you use ExecuteSQL() function.
You would need "Import Records" or "Execute SQL" script step to use syntax of external SQL server.
user19752 answered your basic question. But you might instead try using FileMaker's ESS with that table in the relationship graph as a table occurrence and get the same results in a regular FileMaker layout instead of an array. At least that is what I would do. It would be a simple find and sort according to your SQL code. And your report would probably look better in a nice FileMaker themed layout.
You can connect via MBS Plugin to the MySQL server and run the stored procedure directly there.
I have the relevant tables in my relationship graph but unfortunately the original function callas another two internal functions to calculate the Current Account Balance.
I have been able to run the internal function from the SQL Server Management Studio and create a text file (not delimited!)
I think I may have to try and get the SQL Server Management Studio to run the function and workout how to export the results to a delimited file. I can then write an Import Records script to run on the server and update the Account Balance field in FM.
I could also try the MB Plugin but the server version costs £400 per year which is a bit expensive.
Thanks for your support
You can use SQL in Import Records, selecting ODBC as source.
I'm sure Christian will speak up, but the MBS plugin is a perpetual license. You pay a large amount up front and you don't have to pay anymore. However, if you want upgrades, you have to pay annual upgrade prices which are a lot less than the initial cost. It is not going to cost you £400 pounds per year.
That aside, I still don't understand why you don't do a SQL Import with ODBC or ESS. You say there are multiple function calls, which I assume means multiple SQL calls, which is not that big of a deal. To me it could also be done more easily with ESS doing it with normal FileMaker Finds and summary fields.
Well, you seem resistant to what are the normal ways to do it in FileMaker. I'm not sure why, but you can do it the other opposite direction to. Run it in SQL Server Management Studio and have it write the results to a results table in FileMaker via ODBC. That is an option too.
I might mention that your custom function looks like it is really just a group by function with a sum. In other words you are just grouping by the quarter and summing based on a search fields in the where statement. Would you possibly need assistance in the SQL Group By function and would that take care of the custom function?
Thank you so much for your replies.
I 'm sorry if I seem resistant to the normal ways, I may just be ignorant to what is possible, I have very little SQL experience but many years of Filemaker. I would love to be able to do this from Filemaker.
The reason that I don't think that an SQL import will work is that the values that I need to import are not held in a field (as far as I know and this may be where I am ignorant) I have the Turc06.Custsupp table (and many others from Turc06.###) in my Filemaker relationship graph and use lots of the values in my database, so I am comfortable with pulling values from the SQL database tables.
The result of running the Turc06.EntCustBalance SQL function is not stored in a field that I am aware of.
I wrote a script which runs the Execute SQL script step (it appears to run OK) the next logical step would be to Import Records but I have no idea which table to import records from as I don't know where the results of the function would be.
When I looked at the functions in the SQL management studio they looked very complex and I couldn't follow what they were doing.
I have copied the SQL functions to text files and loaded them into mediafire as some are many pages long the links are below.
Turc06.EntCustBalance calls ifn_GetCustValue which calls ifn_TotalProfitToDateRange
If anyone could look at these SQL functions and give me an idiots guide to what to do I would be eternally greatful.