What isn't working for you?
i don't see any need for c_monthSort as you can sort by d_month_name to group transactions by month though the order won't be chronological. I use the following calculation to group records by month in chronological order:
d_date - day ( d_date ) + 1
select a date result type.
Sort by this field, then by category to get totals for each month broken down by category.
FileMaker is not designed to summarize all the categories at the end of a report but it can be done. Start with the following calculations:
Case(category = "Food_Groceries"; transaction)
Case(category = "Food_Meals Alone"; transaction)
Case(category = "Food_Meals w/ Friends"; transaction)
Case(category = "Spirits"; transaction)
Then create a "total of" summary field for each of the calculations above. Place the summary fields in the grand summary.
Also, I would personally use a calculation like this so you can sort by the month number:
You can easily display a date field as just the month name using date formatting from the Inspector so no need for another calculation for month name.
I put a Year_Month (auto-enter) into every place I need a "sort" (& summarize by) report by month (and within a year). I used to make this TEXT:
Year(myDate) & "_" & Right("00" & Month(myDate) ; 2 )
Then I saw this:
Year (myDate) * 100 + Month (myDate)
(number result), by the absolutely brilliant user19752
Yes, as long as the field is still a text field, as a number field it would fail
Correct! I stated two different calcs, one for TEXT result and one for NUMBER result. Both sort: numerically (number result) or alphabetically (text result) AND both sort chronologically.
Thanks for the help. I'm successful in getting the proper data in the Trailing Grand Summary per Jaymo but cant get a Sub-Summary report to provide the monthly sums for each of the 4 Food Categories per Jaymo's recommendation.
For Field names I use the prefixes: c_calculation; cs_calc. sub-summary; d_ date; t_text; n_number; s_sum
Here is a shot of my layout:
I've used Beverly's field for a Text result:
d_month_name Text Auto-enter Calculation
Calculated value: Year ( d_date ) & "_" & Right ("00" & Month ( d_date ) ; 2)
In Browse mode I then Find records with the Category 'Food'; then I sort by the Field d_month_name.
I don't get a Sub-Summary result, only the Trailing Grand Summary at the bottom of the List.
What I want is: After the list of Transactions for each month, a Sub-summary for each of the 4 Categories, then the Grand Summary
What am I doing wrong?
In my above text, I see that the screenshot of my layout didn't show. How do I insert a screenshot of my layout?
I've Googled for an answer w/ no result.
no need to use the "Right" in your calculation, Month (Date) will take the month directly, but not with a leading zero for Jan-Sep.
Use the following calculation:
Year (d_date) &
Month (d_date) < 10 ; 0 & Month (d_date) ;
and change it to a number field, with a stored calculation (to allow sorting)
You calculation will fail when it is a Number field:
20170012 20170010 20160012 2017009 2017001 2016009
sorted as number
psijmons: I have created a field per your suggestion above:
d_month_name Number Auto-enter Calculation
Calculated value: Year ( d_date ) & Case (Month ( d_date ) <10; 0 & Month ( d_date) ; Month ( d_date ) )
Then sorted on this field after Find and still get no Sub-Summary data by month in the list, even in the Preview mode.
Also made it a Text field w/ same result.
By the way: Can you tell me how to insert a screenshot of my layout for y'all to critique? It may help...I may have an incorrect layout.
The first calc was NOT a number result. I use TEXT and include the underscore to separate. The "00" works just fine as text.
The second calc as number also works.
Sent from miPhone
Beverly: I understood your 1st msg; that the 1st. equation was Text and the 2nd Number. Of course I tried both w/ no success. Thx for your attention to my problem...still unsolved on my part.
Where can I find out how to insert an image of my layout?
Use Advanced Editor when you reply. You can insert (inline) images and/or attach files and pdfs.
Sent from miPhone
aah, you're right, I made a classic mistake, mixing & with +
it was getting late...