Running Sum Problem...Need expertise!

[Cin]

I am trying to work the kinks out of my running sum in a report. It is behaving erratically and trying to find out why. My report is set up as follows

FieldW FieldX FieldY FieldZ RunSumZ.

FieldZ is a calculation field (FieldW + FieldX - FieldY)

The running sum will go along and run then start over when it should not. To run I have a a serial # and a previous serial #. I have a relationship linking the previous serial # to serial#. My running sum is to start over when a new account# is reached. (its supposed to run for account# 100 then start over for 102 etc..) I have a script that finds my data set and sorts and then replaces serial#. When I look at the data the serial# and previous serial# works. The account # will suddenly say 105. I see nothing in my fields or script tha tells it to do this. Has anyone experienced this and could give me some insight as to what the problem is and how to correct it?

Thanks!!

Cin

[Cin]

p.s. I wanted to clarify that the account# that says 105 is the previousrecords::account#

Cin

[-Queue-]

How are you determining the 'previous' serial?

[Cin]

The previous serial # is serial# -1

Cin

I spoke to someone who said it could be in the replace funtion. If the same serial # exists from a previous found set could this be the culprit? If so is there a work around?

Cin

[-Queue-]

Are you allowing deletion of records? Then your previous serial becomes serial - 2 (or more). The best way to handle this is to run a loop with Set Field to append each serial in the found set to a global field or use the Copy All Records trick and paste the result into the global on another layout. Then use a calculation that determines what the previous serial is based on its position in the global field. Is this how you are doing it? If not, what method are you using to perform the replace?

[Cin]

Queue,

I am not deleting records, merely running a script after a find that sorts then replace contents serial# with serial numbers (initial value 1 increment by 1). The previous serial # is that calculation of serial# -1. Since I run this report at different times with different accounts I see how there could be many serial numbers with the value of 1, therefore causing the computer to choose the first 1 that it comes to. (when calculating the running balance) Does this make sense?

Cin

[-Queue-]

You are not using a classic serial number then, as in a serial that is entered at creation and then never changed. So, yes, you will have several duplicates and this process will give inaccurate results. It would be better to use a true serial, copying all serials in the found set to a global field, then a calculation to determine which serial is previous, and a relationship from that calculation to serial. Then you could use your replace on the current found set to order the pseudo-serial as you desire, and a unique relationship to pull data from the previous record in the found set.

If this sounds reasonable, I can make a sample file to demonstrate for you.

[Cin]

Thank you, Queue!

This does make sense to me. I already have a unique ID that happens at creation. If you could give me an example I would greatly appreciate it!

Cin

[-Queue-]

Here's a basic example that simply shows the 'previous' record's data. Change your found set, omit some records or create new ones, then click the Update button. Once you understand how it works, you can either hide the gSerial by making it transparent, its text the same color as your background, disallowing entry, and shrinking it as much as you want. You can opt to put it on a different layout also, as long as you change the script to go to the correct layout before Pasting.

PreviousRecord4Cin.zip