I have a data base that contain similar to the one below:

Col-1 Col-2

2 2

5 3 (from col-1 (5-2))

10 5 (10-5)

11 1 (11-10

Value on Col-2 is by subtrating Portal Line 2 from Line 3. Problem, I need help to do it.

Hi Tsong,

You could setup a relation in your main file to get the Col-1 value from the previous record.

You could do this by defining a serialnumber field that automatically gets a serialnumber when a new record is created.

Then you could make a calculated field that has a value of (serialnumber -1)

Finally set up a relation from the calculated field to serialnumber.

Then Col-2 could be defined as calculation:

Col-2 = Col-1 - Relation(Col-1)

But undoubtedly there are 10 other ways to achieve this...

Regards,

Ernst.

Thanks Ernst for your reply. Just a clarification, I am only referring to one record and the calculation will be through the portal where the repeating fields of this record. I don't know how to apply the serial no. into one record with several field. I hope my question make sense (pardon my english).

Hi Tsong,

So -if I understand correct- your fields are called Col-1 and Col-2, and they are both repeating? And all the numbers in your first example are in one record?

Then the serial number bit is indeed not the answer.

Maybe you can post a (simplified) example of your database for clarification.

Regards,

Ernst.

Avoid repeating fields as much as you can. You are limiting flexibility in future tasks.

Got your advice Anatoli but what will I used instead of repeating fields?

Ernst attached is the sample file.

Thanks

Sample.pdf

Hi Tsong,

I looked at your sample, and think Anatoli is right, you should not attempt to do this with repeating fields.

Each line in your sample should be a record in your filemaker database and

the kind of total you need is a running total.

I made a small example that's attached.

Regards,

Ernst.

Tsong.fp5.zip

Thanks for your time Ernst. I will follow your advice to use record per line and your sample file to use running total (per line). My other problem is the DPWF which is the reverse of the running total (instead of per line addition it is now per line subtraction) which is very hard for me. Again thanks for the help/advice that you can give me.