Is ExecuteSQL returning a "?" on an unexpected result?
Maybe you have to include an "GROUP BY Item_ID" statement.
The SUM, Count, Max, Min, Average, and other sumarize functions, works in conjunction to the Group By Statement.
yes including GROUP BY it works. now the problem is formatting the text. How could i just retrieve the values between the "," and the ¶ ?
if ExecuteSQL returns a list =
GetValue ( Substitute ( GetValue ( list ; 3 ) ; "," ; "¶" ) ; 1 ) returns 12
GetValue ( Substitute ( GetValue ( list ; 1 ) ; "," ; "¶" ) ; 2 ) returns 2592
the problem is that i dont know how many items in the list it´s going to be. it seems a tail function is required to format the list.
i´ve found this link: http://www.seedcode.com/sql-subqueries-in-filemaker/
where the author post a similar case to mine and work it out just with one ExecuteSQL function, but something´s wrong on my side:
SELECT SUM (stock_value)
WHERE Item_ID IN (SELECT DISTINCT Item_ID FROM T03_Warehouse_movements) and Date_field < ?
" ; "" ; "" ; g_date)
i got the sum for all the values of the table that match the date_field filter, but the DISTINCT Item_ID doesnt seem to apply......
item ID stock value Date_field
1 3 1-2-2014
1 3 1-2-2014
2 4 2-1-2013
2 56 12-25-2012
5 0 5-2-2000
4 34 8-4-2016
and executing the sub query only ...SELECT DISTINCT Item_ID FROM T03_Warehouse_movements
Will return 1,2,4,5
Then All records in T03_Warehouse_movements will be returned by your WHERE IN of the parent query
Basically the where in does nothing and the only discriminator is the date criteria
Check out this method of pushing the SQL results to a table:
"Lesson 39: ExecuteSQL and Virtual Lists"
In the FileMaker Training Series, Advanced.
If you are a subscriber TechNet member the FTS is an included benefit.
"FileMaker Developer Subscription"
2. another method is to make the results an HTML table and view in Web Viewer
3. It depends on what you mean by "formatting". There are other ways to clean up the results as viewed in the field.
I suspect you meant pulling the data from the results.
Stock_value is the same for each item_ID ( item_ID 2 in your table should then be = 4 or 56)
For your above example using DISTINCT or not
SELECT DISTINCT Articulo_ID
" ; "" ; "" )
--> list of 4 values
and same sentence without DISTINCT
--> list of 6 values
I dont understand why including SUM (value_stock) to the Select changes the list of values for the SUM to 6....
How could i get then the SUM for the stock_value for each distinct item_ID ? (3 + 4 + 0 + 34) in your table using ExecuteSQL ?
Execuite SQL ( "SELECT SUM ( stock_value ) FROM T03_Warehouse_movements WHERE Date_field = ? GROUP BY Item_ID" ; "" ; "" ; g_date )