Hi, 

 

I have a database of records divided into account numbers - so for example account number 5000 might have 20 records and account number 5001 might have 30 records. I need a way of having a running count sorted by the account number. This is easy in newer versions of filemaker by using a summary running count field and a sorted by field. The problem is this needs to work in FM6 which has the running count but not the sorted by options. 

 

Any ideas?

 

Iaxe

If it's for on-screen use, then the best way is to use a Relationship

e.g.

rel_SameAccount
match records in TableA with records in TableA when AccountNumber = AccountNumber

and a Calculation Field using the Count function.

e.g.

c_AccountNumRecords (Calculation)
= Count (rel_SameAccount::AccountNumber)

You could also use the same Relationship to obtain a total of each account.

e.g.

c_AccountTotal (Calculation)
= Sum (rel_SameAccount::Amount)

If it's for reporting use (print or Preview), then a Summary field in an appropriate Sub-Summary Layout part will work, as long as you remember to Sort the records first.

I need a way of having a running count sorted by the account number.

 

Try defining the following fields:

 

sCount - Summary, Count of AccountNumber (or of any other field that cannot be empty);

sRunCount - Summary, Count of AccountNumber (or of any other field that cannot be empty), running;

cRunCount - Calculation =

GetSummary ( sCount, AccountNumber ) - GetSummary ( sRunCount, AccountNumber ) + sRunCount

--

 

 

 

 

...

 

Wow, thanks guys.

 

Helpful Harry - tried option 1 but it just gave me the same as a summary count field so I must be doing something wrong - but seriously, thanks for the reply it's great to have support.

 

This works great though - just need to get my head around why 

 

sCount - Summary, Count of AccountNumber (or of any other field that cannot be empty);

sRunCount - Summary, Count of AccountNumber (or of any other field that cannot be empty), running;

cRunCount - Calculation = GetSummary ( sCount, AccountNumber ) - GetSummary ( sRunCount, AccountNumber ) + sRunCount

 

Cheers

 

iaxe

tried option 1 but it just gave me the same as a summary count field so I must be doing something wrong

 

No, you're not.

I think I misunderstood what you meant by a "running count". I can see a reason for knowing how many records each account has, but it seems strange having Account Number 5001 calculating as 50 (i.e. 20 for Account Number 5000 + 30 for Account Number 5001).

I think I misunderstood what you meant by a "running count". I can see a reason for knowing how many records each account has, but it seems strange having Account Number 5001 calculating as 50 (i.e. 20 for Account Number 5000 + 30 for Account Number 5001).

Hi, It's probably my inexperience but the reason for the request is that I have to produce statements from data supplied and if an account has more than 25 records it has to be printed on two different sheets (or three or four etc). With each sheet referencing the previous and next. So, I thought I could use the running count to trigger these changes so for example - if running number > 25 change header etc. I had issues using columner list report as although it split over multiple pages they were essentially the same format.