Hi. I was wondering if anyone could offer me some help on a layout/report I am trying to make. Well, it's a report I guess.

I have attached both my database and a generic layout sample on a word document for what I'm looking for as it's easier when you see things rather than read them sometimes.

Basically what I want it to do is generate a report from the Service Hours layout that lists all of the clients, not just the one particular record you happen to be looking at.

I would like it to put all service hours for providers grouped together, and sorted by month. But to put a header for each.

It's probably best to just look at my word document as an example.

Also, while I'm here...I have created a database with access in the past, and was able to create loads of query's and generate reports off of those query's. How do you do that on FM? I'm just not sure where you enter criteria, or get it to prompt you for particular criteria your looking for.

Thanks so much in advance!

Respite_Clone.zip

Report_Example.zip

Produce the report from the LineItems table. Find the records you want to include in the report (by date range) and sort them by provider and by month (you need a sub-summary part for both).

To group records by month, use a calculation field (result is Date) =

Date - Day ( Date ) + 1

Thanks Comment.

I see where/how to create the layout from line items. And how to get all the fields I want. It brings me to the sections of how I want to sort them, which I presume is where you mean to select the provider and month. But where am I putting that calculation? I'm sorry if I'm asking really stupid questions.

The calculation goes into a new calculation field in the line items table, let's name it cMonth. Next, the layout must have two sub-summary parts - by provider and by cMonth. Place the cMonth field in the sub-summary by cMonth part, and custom-format it to show only the month and the year. Finally, have your script sort the records by provider and by cMonth.