Group by month

[adambern]

My first post on FM Forums so be gentle and don't flame...

I'm desperate to ditch MS Access and a virtualised window on a Mac. I've created a new DB from data exported from Access - that's all good. I've got lookup working and summary fields working. I can also generate month and year fields via a calculation from a date field.

But for the life of me, I cannot get the report to group *all* records from a given month in one block, no matter what I try. Instead, the report insists on grouping by day.

The attachment 'Table' shows the table I've created. 'Report' (excuse the redactions) shows the problem - column 1 is the invoice date, 2 is the client (redacted), 3 is the item sold, 4 is the invoice number, 5 is the gross (redacted), 6 the VAT (redacted), 7 the net (redacted), and 8 the date the invoice was settled.

I saw one post where a value list was set up for each month (see Mr Vodka in the link below) but when I look at the example I cannot see how to deploy the value list in the example he gives.

So... quite simply, how do I tell the report to lump, say, all invoices for April in one chunk to subtotal, and so on for other months?

 

 ) 

 

Report_Date.fmp12

[comment]

23 minutes ago, adambern said:

I cannot get the report to group *all* records from a given month in one block, no matter what I try. Instead, the report insists on grouping by day.

I clicked the [Month Report] button in your file and got a report grouped by month. The report is flawed in that it groups together all records from the same month in any year, and it does not display a sub-summary value for each month - but before getting to that, I would like to understand what is the main issue here.

 

[adambern]

Thank you for the quick response but the file Report_Date is not mine, it's the file I downloaded to see how the value fields work. Unless I'm being particularly dim - it happens - I cannot see how the value field has been deployed.

As you say, the button on the sample table works... I just can't see how!

The main issue... on the report *page* that I uploaded (with the redactions), is that I can extract month from the date field. But when I try to group (via inserting a part) to get a subtotal per month, I get subtotals per day.

Thank you for your attention.

[comment]

It works by sorting the records by a field that returns the same value for all records in the same month, then showing them in layout that has a sub-summary part associated with the same field. As I said, there are flaws - but let's start with that.

 

[adambern]

But isn't that what I've done? Month is the only field in the part and it returns (say) a load of 'April's. The problem is that FM is still picking out the individual days.

See attached.

[comment]

What do you mean by " picking out the individual days"? It displays individual days because your layout has a body part. Does it not group correctly the individual days by the month?

[adambern]

It's grouping by 1/4/2020, 2/4/2020, 3/4/2020 and so on and subtotalling each. I'm trying to get the report to put all of the results of (say) April in one block, ditto for May...

So, dare I ask, how would you configure the report in my last post?

[comment]

I don't know how to answer that, because you already have a working (sort of) demo, yet you are unable to reproduce it. Why don't you post your file (preferably a copy stripped down to the bare minimum necessary to show the problem)?

 

[adambern]

Got it! I just needed a second sort order field in the layout... Month followed by date.

Thank you for your help.

 

[comment]

I am glad you got it sorted (pun intended, sorry). Just note that if you sort by month name (or month number), you must also sort by the year. Otherwise you'll get the same problem I pointed out earlier.

My preference is to calculate the date of the first day of the month and sort/group by that. Then it doesn't matter if you also sort by year. And you don't need a value list to get the correct order.

 

[adambern]

Can I ask how you'd construct a calculation for the date of the first day of the month?

[comment]

11 minutes ago, adambern said:

how you'd construct a calculation for the date of the first day of the month?

Quite simply:

InvoiceDate - Day ( InvoiceDate ) + 1

Set the result type to Date, so that you can easily format it to display as "September 2020" or any other format you prefer.

 

[adambern]

Thank you - I'll try that later!