# Running count in a relationship

This topic is 7512 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

Can I create a calculation that will give me a running count of the number of records in a self-join relationship?

For example, I have a db with 10 records, and a field called TxnNum. The first four records have a TxnNum of 1. The next 4 have a TxnNum of 2. The last 2 have a TxnNum of 3. I'd like to get a running count so that the records would be enumerated within the TxnNum = TxnNum relationship. The TxnNum, RunningCount pairs would be:

1 , 1

1 , 2

1 , 3

1 , 4

2 , 1

2 , 2

2 , 3

2 , 4

3 , 1

3 , 2

Can I do this?

Thanks,

Dan

##### Share on other sites

Hi Dan,

There are in fact several ways for this.

You could have the "remainder" (1 to 4) auto-entered from a lookup, and then calculated.

This was discussed a while back. Have a search for "enter from previous"

Do you have an order (sort) for this ?

If not, you could also base your calc upon the value list item function, returning each Ids, where the remainder would be a wordcount of these Ids..

i.e. WordCount(Left(ValueListItems(Status(CurrentFileName), "YourIdsList"), Position(ValueListItems(Status(CurrentFileName), "YourIdsList"), ID, 0, 1)))

if your Ids are built using a "ID"&Left("0000"&serial,4) for example

##### Share on other sites

Attached a quick test file for this using the Value List Items function (unstored though) and the auto-entered from serial.

HTH

RankingSJ.fp5.zip

##### Share on other sites

Thanks! I've never used the Value List Items function before. Didn't even know it was there to tell the truth. I'm pulling apart your example to see how it works. This will do the job once I figure it out.

Happy New Year,

Dan

##### Share on other sites

Thanks again. All is working well. Slick technique.

Dan

##### Share on other sites

This topic is 7512 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account