Number of employees from payroll report

[trinket]

In order to fill out my IRS quarterly payroll form, the IRS wants to know how many employees were paid during the quarter. My current quarterly report has 11 records. It shows that I have written 11 checks for two employees (one employee received 10 checks, the other employee received 1 check). Every summary field I have tried so far gives me 11 rather than 2. Any help? Thank you.

[comment]

This is a tricky one - it requires three fields:

1. sCountSerialID, Summary = Count of SerialID (or any field that's never empty);

2. cInverseCount, Calculation = 1 / GetSummary ( sCountSerialID ; EmployeeID )

3. sCountEmployees, Summary = Total of cInverseCount

The last field will show the count of distinct employees.

Edited July 23, 2008 by Guest
Changed hyphens to commas, so that they are not mistaken for minus

[trinket]

Wow. O.K. I'll give it a try. Thanks for the solution. I should be able to get to it tomorrow after I return from work. Thank you again.

[trinket]

Thank you so much for your help. The solution worked perfectly and it was very easy to do. I also noticed your edit, and either way, your help was easy to understand. Thank you again.

[jim lee]

I'm trying to do the exact same thing. Can't get it to work.

I don't understand the inverse count thing. What's this bit trying to do?

Thanks!

-jim lee

[comment]

It divides 1 by the number of records in each group. The sum of the fractions for each group is 1, and the grand total is the number of groups.

[jim lee]

Wait! I got it to work.

You have to sort on exactly the field you use for the break field. IE you can't sort on the person's name if you use the person's ID as the break field.

Then it worked!

Thanks

-jim lee

Edited October 14, 2008 by Guest

[comment]

That's right: sub-summaries, including GetSummary(), only work when records are sorted by the designated breakField.

Another thing to keep in mind is that breakField must be a local field - so if PersonID is a foreign key, and you need to sort by the related people's names, you must define a local calculation field = People::Name and use it as your breakField.

[benji]

Yeah, that is a tricky one. I think there should be a "Count of" summary field option for "Unique count".

I submitted a feature request at:

http://www.filemaker.com/company/feature_request.html

It seems like it wouldn't be too hard for them to add that one in. If FM can find duplicate values in a Find Request (!), it should be able to figure out unique values in a Count.