Executesql to show the sum in one row.

[Raj Toofanny]

Hi all FM Genius,

I have  a table where I have as columns: Purchase_Month, p_SupplierName and c_Total from table::Purchases. I wrote an Executesql formula as follows:

ExecuteSQL ( 
"SELECT p_SupplierName , Purchase_Month,  SUM(c_Total)
FROM Purchases 

WHERE Purchase_Year = ?
GROUP BY p_SupplierName, Purchase_Month" ; 
":" ; 
"" ; Purchases::Purchase_Year; Purchases::Purchase_Month )

and am getting the result as

Results:

Acrux Ltd:November:39371.0976694915254236
All Goods Ltd:October:7328.3071186440677962
Anichem Pharmacy:February:724.08
Anichem Pharmacy:January:16678.2529661016949151
IBL HEALTHCARE:January:822.7627118644067796
Inicia Ltee:January:6424.576271186440678
Pharmacy A.E.Patel & Co.:February:2747
Unicorn (MSJ LTD):February:8547

What I want is that the SUM to appear in the same row for the p_SupplierName as follows: Anichem Pharmacy: January:16678.2529661016949151:February:724.08

and the same should apply to all the p_SupplierName if they are having records for more than one month.

Any help would be highly appreciated.

[Josh Ormond]

It's probably going to end up hurting you down the road to try and do this with ExecuteSQL. That's not really what the function was intended for, and can have some serious performance degradation under specific circumstances. 

There are several ways to accomplish what you want to do. Take a look at this and see if it helps. Also, take some time to run through the courses on the Claris Academy. They will help fill in some blanks about features available in FileMaker for this type of thing.

https://support.claris.com/s/article/Simple-example-of-a-subsummary-report-1503692935845?language=en_US

[Raj Toofanny]

Thanks Josh for your advise.

[comment]

I suppose it would be possible to post-process the result of ExecuteSQL() using a looping script or a recursive custom function to get the result you show. However I suspect there may be a simpler way.

Can you explain why you need such result? How will this be used?

 

[Raj Toofanny]

I want to create a virtual value list through executesql so that I can create a virtual list in filemaker just like a crosstab report. Or is there any way to create an array of values so as to execute the results in a virtual list as crosstab reports.

[comment]

3 hours ago, Raj Toofanny said:

I want to create a virtual value list through executesql so that I can create a virtual list in filemaker just like a crosstab report.

I am not sure that answers my question:

On 11/15/2020 at 12:10 AM, comment said:

How will this be used?

If your purpose is to display a table where each supplier is a row, and each month of the year is a column, then SQL is not a good starting point, IMHO -  at least not your SQL. Your SQL will not create zero data points. If a supplier has no records in a particular month, then the subsequent data will be shifted to the left and the display will be skewed.

My own preference would be to perform a find the data in the specified year and sort it by supplier and month. Then, for each supplier in the found set, loop from 1 to 12 to populate their row's cells. This could be done using a series of finds, or SQL queries, or - preferably, IMHO - using an adaption of a method known as "Fast Summaries" by Mikhail Edoshin.

Another option is to use a repeating calculation field to place each purchase's amount in a specific column corresponding to the month of purchase. Then display the results using a summary field defined to summarize repetitions individually.

 

Edited November 17, 2020 by comment