Problem with a calculate form

[Fernando DS]

Hi again,

Greetings from Spain.

I’m doing a form of creditors and debtors.
There are three basic fields: amount, payments and balance.
I have done the form with amount and payments as numeric fields, and balance as a calculated field.
It’s very simple. The amount is the initial debt and is a fixed quantity, payments are the partial or total payments to cancel the debt. And balance is the diference between amount and payments.
In this way, I have to calculate separately the total of debt paid until now, to introduce it in the field payments, every time I have to write a payment.
I would like to can introduce just the amount paid every time, not the total paid until now.
I hope you are understanding my very bad english.
And this is my question. Is there a way to do what I want, to introduce just the amount paid every time and not the total amount paid until now?

Thank you in advance for your help.

[Brendan]

Hi Fernando,

There’s no direct function for computing a running total in Tap Forms, which is what it sounds like you want to do. Tap Forms has access to the current record’s data and it can get a total for all records.

You could possibly achieve what you want with a script that you can run which will loop through all your records and compute the running total and update a field in each record to include the total of the records up until that point.

There’s help in the online user manual about scripting here:

https://www.tapforms.com/help-mac/5.3/en/topic/scripts

Thanks,

Brendan

[Fernando DS]

Hi Brendan,

Thank you very much.

I don't quite understand what you want to tell me.
I think I must have expressed myself poorly. I'm going to give a practical example to see if it becomes clearer.
Someone owes me 1000 dollars, which I write in the Amount field.
This same amount appears in the Balance field, by the formula Balance = Amount-Payments.
They make me a payment of $500, which I write in the Payments field.
And in the Balance field, by the aforementioned formula, there are 500 dollars left.
Well, now they make another payment of another 500 dollars. If I write 500 in the Payments field, the Balance field will not remain at 0, but at 500. To make it remain at 0 I would have to enter the total amount paid so far, that is, 1000 dollars.
This is not difficult in this example, but with other amounts I have to make the sum separately and enter it in Payments.
What I want is to be able to enter partial payments, not have to enter the total amount paid each time.
That is, in the case of the example, I would make 2 entries of $500 in the Payments field, and Balance would remain at 0.
Now I have to make a note of 500 and another of 1000.
I hope I am explaining myself well.
I have tried the script thing by setting the Balance field as a script type. But I haven't gotten it to work for me.
Anyway, excuse me for my bad English. I hope that now it is clearer to you what I want to do.

Thank you,

Fernando

 

[Glen Forister]

You need to be able to access the total information from the previous record.

If you keep all your records in date order, then you might be able to use the script I was given for a simple purpose as an example,

See:https://www.tapforms.com/forums/topic/previous-record-problem/

You can also download the file and see how it relates.  Read the info about the previous record number id  – tricky.

Good luck.

[Fernando DS]

Thank you Glen.

I must say that my records are independent. Every record is a different debt.

I have done the following script, but it continues not working. The saldo field is the result of importe-pagos, not saldo-pagos as I want. Any sugestions on the script?

 

function updateRunningTotal(records) {
records.forEach(function(record) {
var importe = parseFloat(record.getFieldValue(‘fld-1cff7a1e6d3d4c68a081fa20be53ba48’)) || 0;
var pagos = parseFloat(record.getFieldValue(‘fld-c4d4d96ce0584cef99a8422512ece4e6’)) || 0;
var saldoAnterior = parseFloat(record.getFieldValue(‘fld-db305c87f3db46d0bbcaaf95dcb47858’)) || 0;

// Calcular el nuevo Saldo basándose en el Saldo anterior y los Pagos
var nuevoSaldo = saldoAnterior – pagos;

// Actualizar el campo Saldo en el registro actual
record.setFieldValue(‘fld-db305c87f3db46d0bbcaaf95dcb47858’, nuevoSaldo);

console.log(“Importe: ” + importe);
console.log(“Pagos: ” + pagos);
console.log(“Saldo Anterior: ” + saldoAnterior);
console.log(“Nuevo Saldo: ” + nuevoSaldo);
});
}

var allRecords = form.getRecords();

// Llamar a esta función para actualizar el Saldo basándose en el Saldo anterior y los Pagos en todos los registros
updateRunningTotal(allRecords);

[Fernando DS]

Excuse me. I have sent another script. I have done very much.

the last is this:

 

function updateRunningTotal(record) {
var importe = parseFloat(record.getFieldValue(‘fld-1cff7a1e6d3d4c68a081fa20be53ba48’)) || 0;
var pagos = parseFloat(record.getFieldValue(‘fld-c4d4d96ce0584cef99a8422512ece4e6’)) || 0;

// Calcular el nuevo Saldo
var nuevoSaldo = importe – pagos;

// Actualizar el campo Saldo en el registro actual
record.setFieldValue(‘fld-db305c87f3db46d0bbcaaf95dcb47858’, nuevoSaldo);

console.log(“Importe: ” + importe);
console.log(“Pagos: ” + pagos);
console.log(“Nuevo Saldo: ” + nuevoSaldo);
}

// Llamar a esta función cada vez que cambie Importe o Pagos
updateRunningTotal(record);

[Fernando DS]

The result is what i have said. Saldo=Importe-Pagos. Not what I want: Saldo=Saldo-Pagos.

[Fernando DS]

And, I repeat, this must be for every record independently.

[Daniel Leu]

How about using a table field inside your record that you use to record the partial payments?

There’s one thing missing at the end of the updateRunningTotal(record) function: document.saveAllChanges();. This will save all the changes you made to your form.

[Fernando DS]

In the end, what I managed to get to work was the table-type field that Daniel Leu proposed to me. It is a type of field that I had never used and was unaware of its usefulness. From now on I will definitely use it often.
Thank you all very much for your valuable help, and happy new year.

All the best,
Fernando

[Author]

Posts