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

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

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

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

Thanks again. All is working well. Slick technique.

Dan