Going Loopy!

[John Chamberlain]

I have a file that contains records of attendance at various support meetings for cancer patients. Each record contains a date, an event ID, and a classification number (0

[Riley Waugh]

John,

A looping script starts in a record (usually record one)... then comes the script step LOOP... then an action... now go to next record [exit after last].... then END LOOP.

Go To Record/Request/Page [First]

Loop

set field {whatever, whatever]

Go To Record/Request/Page [Exit after Last, Next]

End Loop

However, if you want a report to count categories, then you probably want the following....

Make a summary field, we'll call it Cat_Count, and make it a COUNT of field "Catagory".

Make a new layout. In the BODY put the field Category.

Make a SUB-SUMMARY Part. Set when sorted by "Category".

In the sub-summary part put you new summary field Cat_Count.

Enter browse mode. View as a list.

Sort by "Category".

Enter Preview mode.... see your report. Print the report.

You can only see the results in preview mode or when printed. There is a technique for pasting a "picture" of your report to a container field.... but that's for another time.

[John Chamberlain]

Hi Riley -

You are correct, placing the first record request above the loop command does make the script run. But I still don't get what I need, which is a count of the number of meetings held in each category (the numbers I showed above are from a hand count of the data in the attachment, and that is what I am trying to script.)

Using a sub-summary would give me a count of all the attendees in each category but, since a meeting may be held once or twice a week, or only once a month, and may have from 2 to 30 or more attendees, that count does not satisfy the requirement.

Incidentally, this data is needed to report to our largest donor, and their quarterly grant to us using the number of meetings in each category to calculate how much we get to provide the support group meetings we hold for cancer patients.

[lakeside]

I woudn't use a script, I'd use relationships.

Make fields cat0, cat1, cat2, cat3, cat4, cat5, cat6 with the auto-enter data 0, 1, 2, 3, 4, 5, 6 respectively (or use global fields).

Then create relationships

cat0 --> cat0 = BCHD Unit#

cat1 --> cat1 = BCHD Unit#

etc

Finally, create calculation fields:

count_cat0 = Count(cat0::BHCD Unit#)

count_cat1 = Count(cat1::BHCD Unit#)

then just put those calculation fields on their own layout and you have realtime info. If you need to separate by quarter, you can adjust the relationships and the calculation fields to reflect that.

I can't post my revisions to your file, because I don't have stuffit here, but if you'd like, I can email it to you -- but I had to convert it to FM6.

HTH.

M.

[John Chamberlain]

I guess I missed something Mike - I wish I could work in FM6, but I'm stuck with FM4. Anyway, I think I followed your instructions, but the fields all come up empty. I've attached a copy of the file - can you tell me where I went wrong?

Thanks,

John

[lakeside]

Enter data in the cat0, cat1, etc fields. They're set to auto-enter, but they only do it to new records. For existing records, you need to put the values in there

Also, your relationships should be

catX = ::BCHD Unit #

M.

[John Chamberlain]

I would appreciate an email solution to this Mike, but it will have to be in FM5, I dont have a copy of FM6.

I tried your last thing again, but must still be doing something wrong because the numbers I get are still the number of attendees, not the number of meetings. If you could look at the attached I'd appreciate it.

Thanks for your interest.

[Ugo DI LUCA]

In this instance, I'd also use a Sub-summary report.

See attachment in FM4.1

[John Chamberlain]

Hi Mike -

I was able to open the file you sent, but it gives me a count of attendees, not a count of meetings.

If you look at my file, the field Quarterly Report Concat concatenates the meeting ID (there is one for every different group/meeting) and the date of the meeting (although FMP converts that to a reference number). This makes each meeting/date combo unique - what I need is a count of those items in each BCHD Unit# category.

Thanks again.

[lakeside]

Yes, I see your point. I totally misinterpreted what you were after. I've emailed you the file with modifications to your script. And you'll need to change the Quarterly Report Compare field to a global (text) field. Sorry for the confusion.

edit: Got DropStuff installed, so here's the attachment in FM6 (sorry).

[John Chamberlain]

PERFECT!

You're a lifesaver Mike!

A million thanks for your patience with a rank amateur.

[John Chamberlain]

Hi Ugo -

Tried to download your file but my copy of Stuffit did not recognize it in any format. Maybe you could send it by email? My address is [email protected].

PS I still owe you that dinner - when will you be in LA?

[lakeside]

Just got home and looked this over again and saw an error in the script. Here's the corrected version. Sorry (again). Guess today wasn't my day.

M.

[Ugo DI LUCA]

Sorry, I had forgotten to put the extension .fp3.

Here's the file back, with adjustments to your needs for count of sessions.

You may need to open it using the "Open File" menu, as it may appear as plain text field. But FM will convert it for you, either in FM4.1 or later versions for those lurkers here.

I have no idea what Lakeside did. I also involved one relationship to produce your count of sessions. I stayed with the count of attendees by sessions, but didn't included a count per Category, as you have a lot of "A Visitor" anonymous records.

This stays a Sub-summary report, which, from what I understood from your needs, would be adapted.

To produce the count of sessions by category, you just need one intermediate calc and an additional field (which you should always have - a Record_Id ). So I went on a little loop and created this ID.

Then, the now well known "Purge Duplicate" method which tags the first dupe with a 1 and the remainder with a 0 would be sufficient. The sum() of 1 will give you the number of sessions per category, and would be used with no problem in a sub-summary part as well.

I often use too many relationships, but, really, here, unless you want to slow the whole thing, you shouldn't use them. IMO.

Don't forget to see it in Preview Mode though - Third Layout.

HTH.

Attendance CloneMod.fp3.zip

[atjonk]

Test

[Ugo DI LUCA]

The Edit screen allows you to delete the "Test". Glad it worked though. Bold black wasn't an easy one.