Search Results for 'form.getRecords'

[Daniel Leu]

The Javascript API doesn’t provide a way to work on groups. The script posted by JScott shows results on all records. This could be enhanced if you can define how the records are grouped.

Another option would be to work on the current saved search. This would mean that you create a saved search for each group. Then you could replace var records = form.getRecords(); with var records = search.getRecords(); to get the results on the currently selected saved search.

Oh, ignore the alert(output) command in the script since this is not supported. You could use Utils.alertWithMessage(), but that’s not designed to display multi-line results. The console is your friend.

 

[JScottA]

Glen, I have not tested the answer below, but I hope it helps you. It is from the unofficial custom GPT, Tap Forms 5 Assistant. I suggest you work on a copy of your database when testing this. Good luck!

To calculate and display the number of unique values for each field (e.g., “Class,” “Order,” “Family,” etc.) in your Tap Forms database, you have two options: either use the app’s built-in features for a manual method or run a script.

Option 1: Manual Method Using Export and External Analysis

1.Export the Data:

•Use Tap Forms’ export functionality to export your data as a CSV file. Instructions for exporting are on page 126 of the Tap Forms manual .

2.Analyze in Excel/Spreadsheet:

•Open the CSV in Excel or another spreadsheet tool.

•Use the “Remove Duplicates” feature for each column to count unique entries.

Option 2: JavaScript Script in Tap Forms

If you’d prefer an automated method, you can use Tap Forms’ scripting functionality (JavaScript API) to calculate the unique values for each field. Here’s a sample script you can use:

Sample Script

// This script calculates unique values for specified fields in the current form.

function uniqueValueCounts() {

    var form = document.getFormNamed(‘Museum needs’); // Replace with your form’s name

    var fields = [‘Class’, ‘Order’, ‘Family’, ‘Genus’, ‘Sp’]; // Replace with your field names

    var result = “”;

    fields.forEach(function(fieldName) {

        var field = form.getFieldNamed(fieldName);

        var records = form.getRecords();

        var uniqueValues = {};

        records.forEach(function(record) {

            var value = record.getFieldValue(field);

            if (value) uniqueValues[value] = true;

        });

        result += fieldName + “: ” + Object.keys(uniqueValues).length + ” unique values\n”;

    });

    return result;

}

var output = uniqueValueCounts();

console.log(output);

alert(output); // Displays the result in an alert box

How to Use the Script

1.Go to the Scripts section in Tap Forms and create a new script.

2.Copy and paste the above JavaScript code.

3.Modify the form and field names to match your data.

4.Run the script.

The output will display the unique value counts for each field in an alert box and in the console.

For additional details on scripting in Tap Forms, refer to the Tap Forms Scripting 101 Guide.

If you need further guidance or customization of the script, feel free to ask!

[Daniel Leu]

I don’t have the API, so I can’t verify, but otherwise, this seems to work.

The changes: I wrapped your main code in a function: async function main(){....} and in there, I check if the id exists, if not I use the Prompter to get it.

var apiKey = 'xxxxxxx';

//var title_id = 'fld-a211cf1facf34470b4723b2b77e3f159';
var genre_id = 'fld-f2fe50de9ca44bc6bee8887a6a1d1083';
var IMDBrating_id = 'fld-e8c5f6890dce415396e3c5d6aa84a446';
var runtime_id = 'fld-a917ded5c581468795cfcce40cb9f43b';
var imdbid_id = 'fld-2b3bfd5a43ae46c1a3f059dbda9a3dd4';
var year_id = 'fld-7f1cf965a9e04e7dae76e1f380f43367';
var imdbsummary_id = 'fld-08a25e880aca4f7f89cf4cc268c55013';
var poster_id = 'fld-688c78c4bd5148f0877e9a3f99799d5d';
var graphic_id = 'fld-9e2d22d7b5d141678e741f41b2205fb5';
var imdb_url_id = 'fld-2253e940bec14e7ca246db78109db488';
var last_imdb_check_id = 'fld-d9b429bd674c4b5e8235844abaed9ba3';
var checkdate = new Date();
var imdb_url = ("https://www.imdb.com/title/" + record.getFieldValue(imdbid_id) + "/");
// var imdb;

var prompterVar;

function textPrompter(title, text="") {
   return new Promise(function(resolve, reject) {
      let prompter = Prompter.new();
      prompter.cancelButtonTitle = 'Cancel';
      prompter.continueButtonTitle = 'Continue';
      prompter.addParameter(text,'prompterVar')
      .show(title, ((status) => {
         if (status == false || prompterVar == undefined) {
            reject('Cancel');
         } else {
            resolve(prompterVar);
         }
      }));
   });
}

// Note: the following parameters are valid .addParameter(label, field_name, control type, values_list, default_value)


function fetchMovieFromURL()
{
var movieID = imdbid_id;
//    var movieID = record.getFieldValue(imdbid_id);
    var fetchURL = 'https://www.omdbapi.com/?i=' + movieID + '&apikey=' + apiKey + '&r=json&plot=full';
    console.log('Requesting: ' + fetchURL);
    var movie_info = Utils.getJsonFromUrl(fetchURL);
    return movie_info;
}


async function main(){

var movieID = record.getFieldValue(imdbid_id);

   if (!movieID) {
   try {
await textPrompter("Enter the IMDB ID");
console.log(prompterVar + " entered");
const id = prompterVar;

console.log("ID: " + id);
     record.setFieldValue(imdbid_id, id);
form.saveAllChanges();

// movieID = newID;
// console.log(movieID);
imdb_url = ("https://www.imdb.com/title/" + id + "/");

   } catch (errorText) {
      // user clicked 'No'
      console.log('Cancel button clicked');
      return "aborted by user";
   }

    }


// var idValue = record.getFieldValue(imdbid_id);

var results = fetchMovieFromURL();

// if (results.totalItems) {
//  var movie_data = results[0];

var movie_genre = (results["Genre"]);

var movie_runtime = (results["Runtime"]);
var movie_summary = (results["Plot"]);
var movie_poster = (results["Poster"]);
var movie_year = (results["Year"]);
//var movie_title = (results["Title"]);
var movie_IMDBrating = (results["imdbRating"]);
var movie_stars = (results["Actors"]);
var movie_writers = (results["Writer"]);
var movie_director = (results["Director"]);


record.setFieldValues({
  [genre_id]: movie_genre,
  [runtime_id]: movie_runtime,
  [imdbsummary_id]: movie_summary,
  [imdb_url_id]: imdb_url,
  [poster_id]: movie_poster,
  [year_id]: movie_year,
  [IMDBrating_id]: movie_IMDBrating,
//  [imdbid_id]: imdb,
  [last_imdb_check_id]: checkdate
}, false);

var poster = record.getFieldValue(graphic_id);
if (poster <= "") {
record.addPhotoFromUrlToField(movie_poster, graphic_id);
}

form.saveAllChanges;

// Now process actor and director records
var actors_form = document.getFormNamed("Actors");
var actor_records = document.getFormNamed("Actors").getRecords();
var actor_name_id = 'fld-9e6772cd3ce64be1b0ba9f4bcaaeddd6';
var actors_link_id = 'fld-dda27dcbe76447978890b3ad6be470d2';
var actor_found = "N";

// var writer_found = "N";

let stars = movie_stars.split(', ');

//loop to parse json results:

for (let star of stars) {
// console.log("**star** " + star);
var candidateRecord = star;
actor_found = "N";

//now check if in actor form already, add if not

for (var index = 0, count = actor_records.length; index < count; index++) {
      var actor_name = actor_records[index].getFieldValue(actor_name_id);
//      console.log("actor_name " + actor_name);
      var actor_record_id = actor_records[index].getId(actor_name_id);
var candidateField = actors_form.getRecordWithId(actor_record_id);
// var currentRecord = movie_form.getRecords();

if (actor_name == candidateRecord) { //ie, actor is already in actors form
// console.log(actor_name === candidateRecord); // true or false
actor_found = "Y";
console.log("Actor found - " + actor_name);
// console.log("candidate_found should be Y " + candidate_found);
// console.log("Yep - bye");
// console.log("Existing actor found - adding to link table... "+ actor_name);
// record.addRecordToField(candidateField, actors_link_id);
// console.log("checking " + star + " in link table");
// break;

} else {

actor_found = "N";

} //if actor name

} //for var index loop

if (actor_found == "N") {
console.log("New actor found - adding to main record... " + candidateRecord);
var newRecord = actors_form.addNewRecord();
newRecord.setFieldValue(actor_name_id, candidateRecord);
// console.log("New actor found - adding to link table... " + candidateRecord);
// record.addRecordToField(newRecord, actors_link_id);
// form.saveAllChanges();
} //if candidate found
console.log("Saving...");
form.saveAllChanges();
console.log("Existing actor found - updating link table... " + candidateRecord);
record.addRecordToField(candidateField, actors_link_id);
} //for let star of stars loop
document.saveAllChanges();
//....directors

var directors_form = document.getFormNamed("Directors");
var director_records = document.getFormNamed("Directors").getRecords();
var director_name_id = 'fld-2132ad9eddc4412aaeed59e366946a3b';
var directors_link_id = 'fld-e5ea9fce00e84ecd9d342f13840a10b4';
var director_found = "N";
let directors = movie_director.split(', ');

//loop to parse json results:

for (let director of directors) {
var directorRecord = director;
director_found = "N";

for (var index = 0, count = director_records.length; index < count; index++) {
      var director_name = director_records[index].getFieldValue(director_name_id);
      var director_record_id = director_records[index].getId(director_name_id);
var directorField = directors_form.getRecordWithId(director_record_id);

if (director_name == directorRecord) { //ie, director is already in directors form
director_found = "Y";
console.log("Director found - " + director_name);
// console.log("candidate_found should be Y " + candidate_found);
// console.log("Yep - bye");
// console.log("Existing director found - adding to link table... "+ director_name);
// record.addRecordToField(directorField, directors_link_id);
// console.log("checking " + star + " in link table");
// break;

} else {

director_found = "N";

} //if actor name

} //for var index loop

if (director_found == "N") {
console.log("New director found - adding to main record... " + directorRecord);
var newRecord = directors_form.addNewRecord();
newRecord.setFieldValue(director_name_id, directorRecord);
// console.log("New director found - adding to link table... " + directorRecord);
// record.addRecordToField(newRecord, directors_link_id);
// form.saveAllChanges();
} //if director found
console.log("Saving...");
form.saveAllChanges();
console.log("Existing director found - adding to link table... " + directorRecord);
record.addRecordToField(directorField, directors_link_id);
} //for let star of stars loop

document.saveAllChanges();

}

main();

• This reply was modified 1 month, 3 weeks ago by Daniel Leu.
• This reply was modified 1 month, 3 weeks ago by Daniel Leu.

[JScottA]

Does this help you? I’ve not tested it but maybe it will spur your creativity for a real solution:

To achieve the desired effect of consistently placing duplicated records immediately after the original in Tap Forms, a JavaScript-based scripted function could provide a workaround. Here’s a general approach, leveraging the Tap Forms API capabilities found in the documentation:

1.Retrieve the Current Record’s Position: Identify the position of the original record within the sorted list.

2.Duplicate the Record: Use the record.duplicate() function to create a copy of the current record .

3.Insert at Correct Position: Based on the original record’s position in the form’s sorted list, reposition the duplicate if needed. This could be done by iterating over records to ensure the duplicate’s position is adjusted relative to the original.

Here’s a basic script outline:

// Retrieve the current record

var records = form.getRecords();

var currentRecord = form.getSelectedRecord(); // assuming this is the record to duplicate

var index = records.indexOf(currentRecord);

// Duplicate the record

var duplicatedRecord = currentRecord.duplicate();

// Reposition the record if needed by sorting again or programmatically moving

// Example: You might delete and re-add in sorted order if Tap Forms sorting is strict

form.saveAllChanges();

This script, while a creative workaround, may have limitations if Tap Forms enforces strict sorting on save, potentially reordering records automatically. Testing would determine if the manual duplication places the record in the intended position consistently.

[Daniel Leu]

Do you have some type of criteria to select these records? Can you create a saved search for them? Or is it for all records?

Do add them to the list, I think the only way is using a script. Here’s an example of what I’m using:

var prompterVar;

function callbackSetTag() {
   const name_id = 'fld-xxx';
   const tags_id = 'fld-xxx';

   // get records from a saved search
   let recs = form.getSearchNamed('Selected').getRecords();

   // // use all records
   // let myRecords = form.getRecords();
   console.log('Found ' + recs.length + ' records');

   for (rec of recs){
      console.log("Processing " + rec.getFieldValue(name_id))

      var tags = new Array();

      // load initial values
      var tag = rec.getFieldValue(tags_id);

      // create an array of tags
      if (tag) {
         tags = tag.split(",");
      }

      // add new tag
      if (prompterVar){
         tags.push(prompterVar);
      }

      // save new tags
      rec.setFieldValue(tags_id, tags.toString());

      console.log("Setting tags: " + tags.toString());
   }
   document.saveAllChanges();
};

function setTag(){
   let prompter = Prompter.new();
   prompter.addParameter('Tag', 'prompterVar', 'text')
   .show('Add Tag to current record', callbackSetTag);
}

setTag();

You will need to update the name_id and tag_id constants. name_id is used in the console log to provide some infos on which record the routine is running. tag_id is the field where you collect your tags. In your case, that would be the Mailings field.

Next, you have to decide if this works on a saved search or on all records. My saved search is called Selected. Either you update this, or comment this line and uncomment the next block to perform the tagging on all fields.

When you run the script, you get a prompter where you enter the new tag. Note, the tag may not contain commas!

Hoffentlich hilft das. Viel Spass und Erfolg!

[David Oxtoby]

Sam Moffatt wrote:

Having the save all changes outside of the loop commits all of the changes at once and makes it more efficient. Inside the loop slows things down because it commits everything for each row. There is also a progress bar control you can use to communicate where you’re at with things which is useful for an import style use case like this.

Thx for the idea, I found some posts about the progress bar and have now implemented also, I’ll include full code below in case helpful for others searching….

 

// Import the Papa Parse script

form.runScriptNamed(‘PapaParse’);

// Replace with field IDs

var panelid_id = ‘fld-36efa15d8bd6410b8fe7f19d6e0b53bd’;

var panelname_id = ‘fld-2b9958a1f5e145fe929993d960ca2bd2’;

var paneldate_id = ‘fld-1c958e3560f54bb4a2069679437ffff2’;

var paneltime_id = ‘fld-5b8a1a7f74884f2c8889cf2e607b93ab’;

var paneltype_id = ‘fld-94a01f8bac044807a92665787ce31e84’;

var forenames_id = ‘fld-a7a75f6b28734d79a1b9f9f7dfd9b62e’;

var surname_id = ‘fld-761d46b9635a47c691077c0e421babe2’;

var email_id = ‘fld-39b7278d93a140738bd4d43c398200af’;

var attendeetype_id = ‘fld-3243fd4febb8442388b94ef448d51cdb’;

var diocesename_id = ‘fld-59d821b7348046fa8558a88a2eb0e04f’;

var portalref_id = ‘fld-3f7992d2c715418eb4876742c0f42cac’;

var hometel_id = ‘fld-16675cd8390a4360beb8f48a8a61cf63’;

var worktel_id = ‘fld-200cc33874da4a2caa022b245f94c874’;

var panelid2_id = ‘fld-9aef36638f2d47918ba8e4ef0ebf02d5’;

var sponsoredministry_id = ‘fld-07fe8cc64e2d49e1907358d1412676c7’;

var uniquefield_id = ‘fld-e10dc8a981164a689c1396432fe827fe’;

var season_id = ‘fld-7e019716a16a439786b5f18f4ee77e90’;

var p_lay_id = ‘fld-f33a9860109c4e2a9e337413de2f436f’;

var p_ordained_id = ‘fld-0a3fd09705a849148741e7375be67ca9’;

var mfaorpfa_id = ‘fld-6eb4defe3b0943f1ba84526dece268c4’;

var mp1or2_id = ‘fld-3cd37ee306234fc7b9fd924327889849’;

var attendeeteam_id = ‘fld-086d714f95884357b0fbcf214abb316c’;

var teamkey_id = ‘fld-800f79fc21f84ec0ac978053afe829ed’;

var name_id = ‘fld-14547469b33f4620b2196218dd29e322’;

var carousel_id = ‘fld-c97e4fc2aaac4d16a1d43dd1bf41f16d’;

var panel_id = ‘fld-f595e901f0cd4a7fbc8d6a752f048c78’;

var portalid_id = ‘fld-c1fc7ac3bf8e49be8a483d90bb1942a7’;

var panelsecsurname_id = ‘fld-fef1fa3cae61404384647ec54c9dd6a4’;

// Function to delete all records

function deleteAllRecords(){

for (rec of form.getRecords()){

form.deleteRecord(rec);

}

document.saveAllChanges();

}

// Function to split date and parse it

function parseDate(dateString) {

if (!dateString) {

return undefined;

}

let pieces = dateString.split(“/”);

return new Date(pieces[2], pieces[1] – 1, pieces[0]);

}

// Function to parse time in the format hh:mm

function parseTime(timeString) {

if (!timeString) {

return undefined;

}

let pieces = timeString.split(“:”);

let date = new Date();

date.setHours(pieces[0], pieces[1], 0, 0);

return date;

}

// Function to import entries

function Import_Entries() {

let filename = “file:///Users/davidoxtoby/Library/CloudStorage/OneDrive-ChurchofEnglandCentralServices/Documents/In progress/Reports/qryPanelData (TapForms).csv”;

let csvFile = Utils.getTextFromUrl(filename);

if (!csvFile) {

console.log(“No CSV file?”);

return;

}

var output = Papa.parse(csvFile);

// Abort if there are any errors and log to console.

if (output.errors.length > 0) {

console.log(output.errors.join(“\n”));

return;

}

// Initialize progress indicator

var progress = Progress.new();

progress.totalCount = output.data.length;

progress.currentCount = 1;

progress.show(‘Importing Data…’);

// Read each line

for (let index in output.data) {

let line = output.data[index];

if (progress.cancelled) {

console.log(‘Cancelled operation’);

break;

}

// Date and time parsing

let paneldate_new = parseDate(line[2]);

let paneltime_new = parseTime(line[3]);

var newRecord = form.addNewRecord();

newRecord.setFieldValues({

[panelid_id]: line[0],

[panelname_id]: line[1],

[paneldate_id]: paneldate_new,

[paneltime_id]: paneltime_new,

[paneltype_id]: line[4],

[forenames_id]: line[5],

[surname_id]: line[6],

[email_id]: line[7],

[attendeetype_id]: line[8],

[diocesename_id]: line[9],

[portalref_id]: line[10],

[hometel_id]: line[11],

[worktel_id]: line[12],

[panelid2_id]: line[13],

[sponsoredministry_id]: line[14],

[uniquefield_id]: line[15],

[season_id]: line[16],

[p_lay_id]: line[17],

[p_ordained_id]: line[18],

[mfaorpfa_id]: line[19],

[mp1or2_id]: line[20],

[attendeeteam_id]: line[21],

[teamkey_id]: line[22],

[name_id]: line[23],

[carousel_id]: line[24],

[panel_id]: line[25],

[portalid_id]: line[26],

[panelsecsurname_id]: line[27]

});

// Update the progress indicator

progress.updateProgress(index);

}

// Dismiss the progress indicator

progress.dismissProgress();

// Save changes

document.saveAllChanges();

Utils.alertWithMessage(“Import Successful”, “The data delete & import has been completed successfully.”, “info”);

}

// Delete all records before importing

deleteAllRecords();

// Start the import process

Import_Entries();

[Daniel Leu]

This script will delete all records in the current form. So that’s a bit dangerous! Use with care!

function deleteAllRecords(){

for (rec of form.getRecords()){

form.deleteRecord(rec);

}

document.saveAllChanges();

}

deleteAllRecords();

[Fernando DS]

Hello everyone, I have an amount search script in the table field of all records. But it works for me with some quantities and not with others. And I don’t understand why.
This is the script:

// Definiciones de IDs de campos dentro de la tabla
var pagos_cobros_tabla_id = ‘fld-a878bb1af5534101bcfab905279b1180’; // ID del campo Pagos/Cobros dentro de la tabla
var movs_banco_tabla_id = ‘fld-57a5fdcd593e46b298948845321d78ac’; // ID del campo Mvtos. Banco dentro de la tabla
var fecha_id = ‘fld-547c13e982e84d0793b0aba6c220cf76’; // ID del campo de fecha dentro de la tabla
var nombre_id = ‘fld-baf116cd116e4cde9b65e79014a49f28’; // ID del campo de nombre dentro de la tabla
var tabla_id = ‘fld-d8a58dbf35914f3c858c6dfb880c1c48’;
var nota_id = ‘fld-7918261f865b489a8f7672cf733712a9’;
var listado_de_script_id = ‘fld-555622f4bf0344f9b9e92af0806210fc’;
// Reemplazar ‘XXXXXX’ con el ID del campo “Listado de script”

// Define el importe a buscar
var buscarImporte = “19.99”; // Ajusta este valor según el importe que desees buscar

console.log(“”);
console.log(“*************************************”);
console.log(“Importe buscado: ” + buscarImporte);
console.log(“*************************************”);
console.log(“”);

// Inicializar el contenido del campo “Listado de script”
var output = “”;

// Función para parsear el importe independientemente del formato
function parseImporte(importeStr) {
if (typeof importeStr === ‘string’) {
return parseFloat(importeStr.replace(/[^\d.-]/g, ”)) || 0; // Devolver 0 si el valor no se puede convertir a número
} else {
return parseFloat(importeStr) || 0; // Devolver 0 si el valor no se puede convertir a número
}
}

buscarImporte = parseImporte(buscarImporte);

var records = form.getRecords();

for (var i = 0; i < records.length; i++) {
var record = records;
var registrosTabla = record.getFieldValue(‘fld-d8a58dbf35914f3c858c6dfb880c1c48’) || []; // Reemplaza ‘tabla_id’ con el ID del campo de la tabla
var nombre = record.getFieldValue(‘fld-baf116cd116e4cde9b65e79014a49f28’); // Obtener el nombre fuera del bucle de la tabla

// Array para almacenar los movimientos correspondientes a cada nombre
var movimientosNombre = [];

for (var j = 0; j < registrosTabla.length; j++) {
var registroTabla = registrosTabla[j];
var pagosCobros = parseImporte(registroTabla.getFieldValue(pagos_cobros_tabla_id));
var movsBanco = parseImporte(registroTabla.getFieldValue(movs_banco_tabla_id));
var fechaRaw = new Date(registroTabla.getFieldValue(fecha_id));
var fecha = ${('0' + fechaRaw.getDate()).slice(-2)}/${('0' + (fechaRaw.getMonth() + 1)).slice(-2)}/${fechaRaw.getFullYear().toString().substr(-2)};
var nota = registroTabla.getFieldValue(nota_id);

// Comparar el valor absoluto de los importes
if (Math.abs(pagosCobros) === Math.abs(buscarImporte) || Math.abs(movsBanco) === Math.abs(buscarImporte)) {
movimientosNombre.push({ fecha: fecha, movsBanco: movsBanco, pagosCobros: pagosCobros, nota: nota });
}
}

// Mostrar el nombre y luego los movimientos correspondientes
if (movimientosNombre.length > 0) {
output += “\n” + nombre + “\n\n”;
console.log(“” + nombre);
console.log(“”);
for (var k = 0; k < movimientosNombre.length; k++) {
var movimiento = movimientosNombre[k];
output += ${movimiento.fecha}: ${movimiento.movsBanco} | ${movimiento.pagosCobros}\n\n${movimiento.nota}\n;
console.log(${movimiento.fecha}: ${movimiento.movsBanco} | ${movimiento.pagosCobros});
console.log(“” + movimiento.nota);
console.log(“”);
}
}
}

// Actualizar el campo “Listado de script” del primer registro con el texto generado
record.setFieldValue(listado_de_script_id, output);

// Guardar los cambios en los registros
form.saveAllChanges();

Any help will be welcome.

 

[Glen Forister]

I can’t figure out how to fix the code so the first actual record after the “Begin Year Mark” record adds the $100 to $0 of the initialization record for the year.

Thanks…

Date Org $ Total$

01/01/24 Begin. 0 0 Begin Year mark

01/01/24. KVMR 100 0

03/12/24 test 20 20

03/27/24. test.2. 50 70

04/17/24. test.3. 55 125

Can’t remember how to display the code correctly – searched for it – Put that hint somewhere? “!”???

!

function Year_to_date() {

var date_id = ‘fld-e2d20da877cb4a6c8fd72153b86f1ab1’;

var donation_id = ‘fld-05c429d6a4484061b554aa584a45f8fc’;

var total_id = ‘fld-7c6b9bb288ab47e0b295af6eacc9cd26’;

var records = form.getRecords(); // calls function getRecords()

var currentRecordIndex = records.indexOf(record);

if (currentRecordIndex > 0) {

var previousRecord = records[currentRecordIndex-1];

} else {

return 0;

}

// Is this the beginning of the year?

var date = record.getFieldValue(date_id);

if (date.getMonth() == 0 && date.getDate() == 1){

return 0;

}

var today = record.getFieldValue(donation_id);

var previous_total = previousRecord.getFieldValue(total_id);

var total = today + previous_total;

console.log(“Today: ” + today)

console.log(“Previous: ” + previous_total)

console.log(“Total: ” + total)

return total;

}

Year_to_date();

!

[Glen Forister]

I can’t figure out how to fix the code so the first actual record after the “Begin Year Mark” record adds the $100 to $0 of the initialization record for the year.

Thanks…

Date              Org         $     Total$

01/01/24    Begin.      0         0            Begin Year mark

01/01/24.   KVMR   100      0

03/12/24    test          20      20

03/27/24.  test.2.      50      70

04/17/24.  test.3.      55     125

Can’t remember how to display the code correctly – searched for it – Put that hint somewhere? “!”???

!

function Year_to_date() {

var date_id = ‘fld-e2d20da877cb4a6c8fd72153b86f1ab1’;

var donation_id = ‘fld-05c429d6a4484061b554aa584a45f8fc’;

var total_id = ‘fld-7c6b9bb288ab47e0b295af6eacc9cd26’;

var records = form.getRecords();   // calls function getRecords()

    var currentRecordIndex = records.indexOf(record);

    if (currentRecordIndex > 0) {

        var previousRecord = records[currentRecordIndex-1];

    } else {

      return 0;

    }

// Is this the beginning of the year?

var date = record.getFieldValue(date_id);

if (date.getMonth() == 0 && date.getDate() == 1){

return 0;

}

var today = record.getFieldValue(donation_id);

var previous_total = previousRecord.getFieldValue(total_id);

var total = today + previous_total;

console.log(“Today: ” + today)

console.log(“Previous: ” + previous_total)

console.log(“Total: ” + total)

return total;

}

Year_to_date();

!

[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]

• 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]

• 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 accumulated”, 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 is executed and the following actions occur: In the line of the last pending payment, the Payments field will be filled in with the value of the Amounts field, so that payment or collection will be settled. 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 amount of 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 completed. Two more fields will also be completed, 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 amounts, but I find it very difficult to put 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. 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);
}
}
});

[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);

[Daniel Leu]

Hi Glen, in your original script, you were reading the previous total from the wrong field. Now it should work as expected:

function toHrsMin(sec){
let str = "";

let h = Math.floor(sec / 3600);
let m = (sec / 60) % 60;
return h + ' hr, ' + m + ' mins';
}

function Accum_Tot() {

var records = form.getRecords();
    var currentRecordIndex = records.indexOf(record);

    if (currentRecordIndex > 0) {
        var previousRecord = records[currentRecordIndex-1];
        var previous_total = previousRecord.getFieldValue('fld-b2f9616bf5e44fb4880ad9addd2afc6e');
    } else {
    var previous_total = 0;
    }

var today = record.getFieldValue('fld-44acc9d2314f41beb3f2257ace5bae01');
var total = today + parseInt(previous_total);

console.log("Today: " + toHrsMin(today))
console.log("Previous: " + toHrsMin(previous_total))
console.log("Total: " + toHrsMin(total))

return total;
}

Accum_Tot();

• This reply was modified 11 months, 4 weeks ago by Daniel Leu.