Here is my problem and I cannot wrap my head around it... I have about 3000 sites in 6 States that need to be visited in certain intervals (About 10 times a year each). I have a sites table with all the information and a visits table, both are related by site number. The sites table actually has a field that counts how often a site has been seen.

What I want now is a one page summary, per state, giving me a count of how many sites have been seen once, twice, three times, etc. I have something along those lines where it list every site in that fashion, but that turns into a 200 page report, where all I need is one page. Once I remove the body of the layout - it no longer works.

I tried creating a table with one record for each state and the fields visit1, visit2, visit3, etc., but I could not get that working because, I cannot figure out how to do a conditional count (e.g. count all records in the sites table that have 2 visits). I am sure it can be done... I just think I have developed a tunnel vision.

.......0 1 2 3 [...]

OR 23 156 12 0

CA 1 199 7 2

NV 0 12 158 63

WA 99 10 0 0

NM 6 255 3 0

AZ 9 7 5 5

Any pointers?

thx

It keeps saying FMP7, but I am using 8.5 if that makes things any easier.

Edited June 6, 200718 yr by Guest

I think this can be done in a columnar report (list view) layout based on the Visit table. You'd need a Summary Count of RecordID field defined in that table, and a Sub-Summary by State part followed by a Sub-Summary by Site part defined in the layout (no Body part needed). Add a page-break option to the Sub-Summary by State part to get a new page with each change in State. Stick the Site field on the Sub-Summary by Site part, along with the Count, and stick the State field on the Sub-Summary by State part.

After Finding the correct record set, Sort them by State and Site. If it's also important to have the Sites in order of the number of visits they've had, use the "Reorder by Summary field" option in the Sort dialog, choosing the Count summary field.

When the records are sorted this way, and viewed in Preview Mode or printed, the sub-summary parts will show.

It keeps saying FMP7, but I am using 8.5 if that makes things any easier.

The FMP version information can be changed in your Control Panel under the My Profile tab at the top. It's in the FileMaker Questions section.

You can define a repeating calculation in your City table. It must count the related visits and then evaluate to 1 in the corresponding repetition. I.e.

Let( number of visits = Count( Extend( Visit::ID ) );



  Case( number of visits = Get( CalculationRepetitionNumber ) - 1, 1 ) )

E.g. if a city had 5 visits, the formula output 1 in the 6th repetition. We count from 0 visits, so this is what we need.

Next define a summary field that counts or sums this repeating field by repetition. There's a radiobutton at the bottom of the summary dialog, it says together or individually. You need individually. The summary field will turn to 11 repetitions automatically.

Now make a report with Subsummary by State part and place this field in the part. Position the repetitions horizontally. Sort and preview, it should work.

You'll probably need to summarize over some period; to do this you'll have to make a relationship based on global fields that define the period.

If you didn't need the first zero column, it would be possible to compose the report in the Visit table for a given found set.