Good afternoon!

I have an SQL calculation that I am trying to deploy within the y-axis of a bar chart and I am encountering the not so desired result of a "?." If someone could help me troubleshoot this I would be jumping out of my seat with glee.

Here is the troublesome function:

ExecuteSQL(

"SELECT SUM(Total)

FROM Donations

WHERE DateCreation >= StartDate AND DateCreation <= EndDate

GROUP BY Month

ORDER BY Month DESC";

""; ¶)

Here are the fields:

DateCreation: unstored calculation, date type

StartDate: unstored calculation, date type

EndDate: unstored calculation, date type

Month: unstored, calculation, number type: Month(DateCreation)

After some quick experimentation I realized that if I change the Month field to a text type field and rejig the calculation from Month(DateCreation) to MonthName(DateCreation), the sql function results with the desired solution but it orders the months alphabetically rather than by the number of the month. Is there a way to order the sql function by a calculation field that is a number type rather than a text? Essentially I want the months to show in the order (1, 2, 3, 4, 5, 6, 7, 8 9, 10, 11, 12) on my chart. I am not familiar with SQL, so I apologize if my question is fairly simple.

Hey there,

The problem with your first statement is about something unique in FileMaker. When you're using Group By, you can only Order by or Group by fields that are part of the Select statement. If you change your statement to something like this, it should work:

ExecuteSQL(

"SELECT SUM(Total), Month

FROM Donations

WHERE DateCreation >= StartDate AND DateCreation <= EndDate

GROUP BY Month

ORDER BY Month DESC";

""; ¶)

Then the problem is that you have to pull Months out of the result. I have a recursive custom function that allows you to pull a specific column, so let me know if you'd like that.