Script to automatice expenses/incomes form

[Fernando DS]

• Hi everybody,

  I have an income and expense form and I want to automate it as much as possible. To do this, and with the invaluable help of my friend ChatGPT, I have made the script that I am sending you. What I want is for the last pending payments or collections to be credited when the script is executed, if the current date is later than the date of said payments or collections. The payment records are in a table-type field called “Calculations”, which includes the fields: – “Payment or collection date”, date type field, is the expected date for payment or collection. – “Amounts and accumulated”, numerical field, is the amount to be paid or collected. – “Payments and accruals”, numerical field, is filled in with the value of the Amounts field when the Payment Date arrives. What the script intends is that when the Payment or Collection Date is before the current date, the script will be executed and the following actions will occur: In the line of the last pending payment, the Payments field will be filled with the value of the Amounts field, so that payment or collection will be skipped. At the same time, a new line will be created with the new Payment or Collection Date and the new Amount, which is always the same, and therefore will copy the one imported from the previous movement. Regarding the new date, the script will take into account the “Periodicity” field, a text field that marks the type of period that passes between one movement and the next, monthly, annual, etc., and which is also outside the Calculations table. And with this the next pending payment or collection will be fulfilled. 2 more fields will also be fulfilled, which are outside the Calculations table: – Next payment or collection date, date type field, which will copy the date of the last pending payment or collection that has been created. – Balance/next payment or collection, field calculated with a formula, which is the balance between the totals of the Amounts and the Payments. Here the new balance will be incorporated, which will be equal to the previous one. And that would be it. I want to clarify that I also have records with different imports, but I find it very difficult to measure these in a script. Each record in the form is a different payment or collection and are independent of each other. Without prejudice to the fact that the amounts are added to obtain totals and balances.
  Obviously the script does not work. It has no errors in the console, but not working.

• Anyway, I hope I have explained myself. Although I will clarify any doubts. And this is the script

 

var fechaActual = new Date();
var registros = form.getRecords();

registros.forEach(function (registro) {
var calculos = registro.getFieldValue(‘fld-d8a58dbf35914f3c858c6dfb880c1c48’) || [];
var fechaProximoPagoCobro = registro.getFieldValue(‘fld-3671b67b092b4b3b949984292c023511’);
var saldoProximoPagoCobro = registro.getFieldValue(‘fld-a554770fd7834b22802b65c488be9f0d’);
var periodicidadTexto = registro.getFieldValue(‘fld-aaba9a24064a4a8893a3963f7cbe8595’);
if (calculos.length > 0 && fechaProximoPagoCobro && saldoProximoPagoCobro && periodicidadTexto) {
var fechaUltimoCalculo = new Date(calculos[calculos.length – 1].fecha_pago_o_cobro_id);

// Si la última fecha de pago es anterior a la fecha actual
if (fechaUltimoCalculo < fechaActual) {
var nuevaFecha = new Date(fechaUltimoCalculo);
var meses = { ‘Mensual’: 1, ‘Bimensual’: 2, ‘Trimestral’: 3, ‘Semestral’: 6, ‘Anual’: 12 };
var dias = { ‘7 días’: 7 };

// Añadir tiempo a la fecha según la periodicidad
if (meses[periodicidadTexto]) {
nuevaFecha.setMonth(nuevaFecha.getMonth() + meses[periodicidadTexto]);
} else if (dias[periodicidadTexto]) {
nuevaFecha.setDate(nuevaFecha.getDate() + dias[periodicidadTexto]);
}

// Si la nueva fecha es en el futuro, añadimos una nueva entrada
if (nuevaFecha > fechaActual) {
var ultimoImporte = calculos[calculos.length – 1].importes_y_acumulado_id;
calculos.push({
fecha_pago_o_cobro_id: nuevaFecha,
importes_y_acumulado_id: ultimoImporte,
pagos_y_acumulado_id: ”
});
}

// Actualizamos el campo ‘Pagos y acumulado’ del último cálculo
calculos[calculos.length – 1].pagos_y_acumulado_id = saldoProximoPagoCobro;

// Guardamos los cambios en el registro
registro.setFieldValue(‘fld-d8a58dbf35914f3c858c6dfb880c1c48’, calculos);

form.saveRecord(registro);
}
}
});

 

Thank you in advance for your kind help.

[Fernando DS]

<p style=”text-align: left;”>I send another script later and I think it’s more complete.

</p>
var fecha_pago_o_cobro_id = ‘fld-3671b67b092b4b3b949984292c023511’;
var pagos_y_acumulado_id = ‘fld-a1ee93e141f34774be95df31d316be12’;
var importes_y_acumulado_id = ‘fld-d72e37e939fe476e820ff4f3c4ef12c7’;
var fecha_proximo_pago_o_cobro_id = ‘fld-51be737772e840d2b4d365bd1f5230cd’;
var saldo_proximo_pago_o_cobro_id = ‘fld-a554770fd7834b22802b65c488be9f0d’;

var fechaActual = new Date();
var registros = form.getRecords();

registros.forEach(function (registro) {
var calculos = registro.getFieldValue(‘fld-d8a58dbf35914f3c858c6dfb880c1c48’) || [];

if (calculos.length > 0) {
var ultimoCalculo = calculos[calculos.length – 1];
var fechaPagoCobro = new Date(ultimoCalculo[fecha_pago_o_cobro_id]);
var importe = ultimoCalculo[importes_y_acumulado_id];

if (fechaPagoCobro < fechaActual) {
// Cumplimentar el campo Pagos con el valor del campo Importes para saldar el pago o cobro
ultimoCalculo[pagos_y_acumulado_id] = importe;

// Determinar la nueva fecha de pago o cobro
var periodicidadTexto = registro.getFieldValue(‘fld-aaba9a24064a4a8893a3963f7cbe8595’);
var nuevaFechaProximoPagoCobro = new Date(fechaPagoCobro);
switch (periodicidadTexto) {
case ‘Mensual’:
nuevaFechaProximoPagoCobro.setMonth(nuevaFechaProximoPagoCobro.getMonth() + 1);
break;
case ‘Bimensual’:
nuevaFechaProximoPagoCobro.setMonth(nuevaFechaProximoPagoCobro.getMonth() + 2);
break;
case ‘Trimestral’:
nuevaFechaProximoPagoCobro.setMonth(nuevaFechaProximoPagoCobro.getMonth() + 3);
break;
case ‘Semestral’:
nuevaFechaProximoPagoCobro.setMonth(nuevaFechaProximoPagoCobro.getMonth() + 6);
break;
case ‘Anual’:
nuevaFechaProximoPagoCobro.setFullYear(nuevaFechaProximoPagoCobro.getFullYear() + 1);
break;
// Añadir más casos de periodicidad si es necesario
default:
console.error(‘Periodicidad no reconocida: ‘ + periodicidadTexto);
break;
}

// Crear una nueva línea con la nueva fecha de pago o cobro y el mismo importe
var nuevoCalculo = {
‘fecha_pago_o_cobro_id’: nuevaFechaProximoPagoCobro,
‘importes_y_acumulado_id’: importe,
‘pagos_y_acumulado_id’: ”
};
calculos.push(nuevoCalculo);

// Actualizar los campos fuera de la tabla ‘Cálculos’
registro.setFieldValue(fecha_proximo_pago_o_cobro_id, nuevaFechaProximoPagoCobro);
// No es necesario actualizar ‘Saldo/próximo pago o cobro’ si es un campo calculado automáticamente

// Guardar los cambios en el registro actual
registro.setFieldValue(‘fld-d8a58dbf35914f3c858c6dfb880c1c48’, calculos);
}
}
});

form.saveAllChanges();

[Fernando DS]

<p style=”text-align: left;”>I’m sending also a photo of the table for better understanding.</p>

Attachments:

You must be logged in to view attached files.

[Fernando DS]

Already solved. Thank you

[Author]

Posts