Hi. Does anyone know how to calculate the number of the record you are on, without doing a sort? For example, every record I create has an account number and date. So if I create a new record with account #101, for example, I need a counter that says "6", if this is the 6th record with account #101. Similarly, if I create another record, say #102, and that is the 3rd record with account number 102, then the counter should return "3" here.

Any idea how I would do that?

thanks!

-Emery

Create a self relationship based on the field "Account Number"

Then create a new calculation field with the calculation;

Count(RelationshipName::Acount Number)

That should give you the number of Accounts with the same number as the current record.

Phil

Thanks, Phil. Yes, that will give me the number of records, but I need a running total, rather than a summary total. I need to know what the number of the record is, that I'm looking at. This is because we have an interest rate calculation we have to run, and one of the required variables is the number of months that have gone by for that particular record. Since we create one record per month per account number, I need some sort of counter that restarts for each account number, and returns a different running total number, depending on which record I'm in. So record number 4 would say "4," and record number8 would say "8", etc.

Any ideas on that? thanks.

-Emery

Make the calculation a number field with the auto-enter calculation given above. It will only evaluate when a record is created.

I would say that my answer may be different depending on how you go about creating the new record each month.

At some point, depending on how you create the new records, you would run a SetField script step that copied the count to a static field for that record or a script step that would set a field to record count plus 1.

So, how do create the new records each month? Is the record creation scripted? Is the Account Number typed manually or set as part of a new record script?

Phil

Actually, I got it to work, thanks to your idea, Phil, and Queue's suggestion.

Much appreciated!

-Emery