Search Results for 'form.getRecords'

[Daniel Leu]

I don’t think that you can merge records when importing a csv file.

I would import the csv file into a new form “Email Received”. Then add a form script to your main form. This script (shown below) loops over all imported emails and then searches for a matching email in your main form. If one is found, then “Email Received” checkbox is marked.

You need to set email_id and email_received_id according to your form fields. The script assumes the CSV form is named “Email Received” and has a field “Email”.

function Mark_Email_Received() {
	// define fields, main form
	const email_id = 'fld-xxx';
	const email_received_id = 'fld-xxx';

	// open csv form and get all records
	let formCsv = document.getFormNamed("Email Received");
	let recordsCsv = formCsv.getRecords();
	
	// get fields form csv form
	const emailCsv_id = formCsv.getFieldNamed("Email").getId();
	// loop over all csv records
	for (recordCsv of recordsCsv){
		let emailCsv = recordCsv.getFieldValue(emailCsv_id);
		
		console.log("Checking " + emailCsv);
		
		// loop over all contacts
		for (rec of form.getRecords()){
			let email = rec.getFieldValue(email_id);
//			console.log(">  " + email);

			// is there a match?
			if (email == emailCsv) {
				console.log(">  found match");
				rec.setFieldValue(email_received_id, 1);
				break;
			}
		}
		
	
	}
	document.saveAllChanges();

}

Mark_Email_Received();

This is not the most efficient implementation as the inner loop is executed for each imported email address. A more advanced version would create a saved search that only contains emails that have ’email received’ not checked.

Hope this helps!

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Sam Moffatt]

The undefined type means that its not an object which is why when you do value.search you get an error because value is undefined and has no value. Since you’re iterating through all of your records, you need to check if the value you get back is undefined and skip because if the field isn’t set on a record, then you will get an undefined value back from the scripting API.

Try something like this that ignores empty fields and also logs any records missing tags for further review:

function Feel_Good() {

    var nombre = 0;
    var movie_form = document.getFormNamed(‘Movies’);

    var movie_form_tags_field = movie_form.getFieldNamed(‘Tags’);
    var movie_form_tags_id = movie_form_tags_field.getId();

    var movie_form_records = movie_form.getRecords();

    for (var index = 0, count = movie_form_records.length; index < count; index++) {
        var value = movie_form_records[index].getFieldValue(movie_form_tags_id);

        if (value) {
            var pos = value.search(“Feel good”);

            if (pos > -1) {
                nombre++;
            }
        } else {
            console.log("Record missing tags: " + record.getUrl());
        }

    }

    return nombre;
}

[Ray Robillard]

Hi !

I’ve a movie database in which there’s a Tag pick list, made of checkboxes. I want to find how many different records have a certain item in this pick list checked.

So thinking the pick list returned something I wasn’t sure of, I tried to get the type of the object, but all I am getting is undefined.

var value = movie_form_records[index].getFieldValue(movie_form_tags_id);
console.log(typeof value);
(console output is “undefined”)

So when I try this :
var pos = value.search(“Feel good”);

It never returns any item, despite the fact that some of the records have this tag. I know this because using the search filter, I can find several movies for which this tag is checked.

My loop goes through all the records and make the following test :
if (pos >-1) {
nombre++;
}

But when I am running the code, I am getting the following output in the console :

2021-03-10, 8:17:57 AM / Movies / Feel good
Feel good: TypeError: undefined is not an object (evaluating ‘value.search’), line:(null)
Feel good: TypeError: undefined is not an object (evaluating ‘value.search’), line:(null)

The whole code is :

function Feel_Good() {

var nombre = 0;
var movie_form = document.getFormNamed(‘Movies’);

var movie_form_tags_field = movie_form.getFieldNamed(‘Tags’);
var movie_form_tags_id = movie_form_tags_field.getId();

var movie_form_records = movie_form.getRecords();

for (var index = 0, count = movie_form_records.length; index < count; index++) {

var value = movie_form_records[index].getFieldValue(movie_form_tags_id);

var pos = value.search(“Feel good”);

if (pos >-1) {
nombre++;
}

}

return nombre;
}

So.. what am I doing wrong ? I’m a total newbie at JavaScript. So maybe I missed something somewhere.

Thanks,

Ray

[Daniel Leu]

Hi Gerhard,

You can use the progress feature for that, but this seems to be an overkill for such a simple task. Following script does the same thing:

function setAddress() {
	const adresse_lang_id = 'fld-xxx';
	const maps24_id = 'fld-xxx';

	for (r of form.getRecords()){
		let x = r.getFieldValue(adresse_lang_id);
		r.setFieldValue(maps24_id, x);	
	}
	document.saveAllChanges();
}

setAddress();

But I’m wondering if using a form script for this tasks is what you really want. Whenever you run the script, all your maps24 field are updated.
It might be better to use a field script for that.

function setAddress() {
	const adresse_lang_id = 'fld-xxx';
	const maps24_id = 'fld-xxx';

	let x = record.getFieldValue(adresse_lang_id);
	record.setFieldValue(maps24_id, x);	
	document.saveAllChanges();
}

setAddress();

Now the target field is only updated when the adresse_lang field is changed.

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Gerhard Hoffmann]

Hi Eddy,

I’m using TabForms since 4 months and the script is a real game changer. I’m not an expert in script programming, but my son helped me a lot.
I had a similar problem and I want to fill in the address fields to the field location, but not only to one address, it should do the work to all addresses. See, we use a for-next-loop.

// start

var adresse_lang_id = ‘fld-5840ffe2e4b04f01a150329d15498877’;
var maps24_id = ‘fld-ce41104cdbc2405eb2ad0f9be8baa35a’;
var progress = Progress.new();

function ProcessRecords(records) {
for (index in records) {

if (progress.cancelled) {
console.log(‘Cancelled operation’);
break;
}

var aRec = records[index];
var x = aRec.getFieldValue(adresse_lang_id);
aRec.setFieldValue(maps24_id, x);

progress.updateProgress(index);
}
}

var records = form.getRecords();
progress.totalCount = records.length;
progress.currentCount = 1;
console.log(‘Begin’);

// show the progress sheet
progress.show(‘Processing Records…’);

ProcessRecords(records);

// dismiss the progress sheet when done.
progress.dismissProgress();

// save your changes
form.saveAllChanges();

console.log(‘End’);

// end

Regards,

Gerhard

[Daniel Leu]

I suppose I could just use a text field, but even that would be annoying because if I convert an existing Date field to a text field, the data is erased. I already have 200 records, so converting to text would require me to cut and paste the existing date 200 times.

You could use a form script to translate the date field into a text field.

Following script would do that. You just need to update date_id and text_date_id to match your form.

function Convert_Date() {

	var date_id = 'fld-f74f442f93b7499aba86170cbab5e3a1';
	var text_date_id = 'fld-87e6c288a4e042c1bbfff27849a4b224';

	// loop over all records
	for (rec of form.getRecords()){
		let date = rec.getFieldValue(date_id); // get date
		if (date){ // if date exists
			let textDate = date.toLocaleDateString("en-US", { year: 'numeric', month: 'numeric', day: 'numeric' }); // create date string
			console.log(textDate);
			rec.setFieldValue(text_date_id, textDate); // store date string
		} else {
			console.log("Empty date");
		}
	}

	document.saveAllChanges();

}

Convert_Date();

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Gregory martin]

Here is a more compact version. Sets the script field to 1 if exists or 0 if unique.

var mainfield_id = 'fld-d5dba46d240d4d41bd792970526b5bc9'; // change this to your field ID
var newvalue = record.getFieldValue(mainfield_id);
var thisrecord = record.getId();
checkunique();
function checkunique() {
unique=0;
var records = form.getRecords();
unique = 0;
for (var index = 0, count = records.length; index < count; index++){
	// do not check current record
	if (records[index].getId()!=thisrecord) {	
  	    // check other record values for duplicate
    	if ((unique == 0) && (records[index].getFieldValue(mainfield_id) == newvalue)) {
    	   unique=1;
           console.log("Already exists");
           break;
        }     
        return unique;  
}}};

[Gregory martin]

You could check a field for being duplicated by adding this to a script field on your form, and the script is triggered when you press enter on the field you specify in the mainfield_id. Not sure how fast this would be on thousands of records though! You need to change your id for the field your are checking. This code snippet writes out ALREADY EXISTS in the console, otherwise the values of the records, so amend as needed of course

// ***** this needs to be your field ID
var mainfield_id = 'fld-d5dba46d240d4d41bd792970526b5bc9';
// ===========

var newvalue = record.getFieldValue(mainfield_id);
var thisrecord = record.getId();
checkunique();

function checkunique() {
unique=0;
var records = form.getRecords();
unique = 0;
for (var index = 0, count = records.length; index < count; index++){
    // ***************************
	// do not check current record
    // ***************************
	if (records[index].getId()!=thisrecord) {
	
  	   console.log(records[index].getId());
  	   console.log(records[index].getFieldValue(mainfield_id));
  	   console.log(newvalue)
  	   
  	    // check other record values for duplicate
    	if (records[index].getFieldValue(mainfield_id) == newvalue) {
           console.log("Already exists");
        }       
}}};

[Brendan]

Hi Chris,

You’re right. It’s not working right for checkmark field types. I’m working on fixing that. In fact, I just fixed it (for the next update).

In the meantime, you can write a Form Script to accomplish the same thing that loops through the records, looking for the checkmark field value and turning it off if it’s on.

Are you familiar with scripts in Tap Forms yet? Here’s a sample bit of code that will do that for you, but you’d have to put your own Checkmark field’s ID in it:

function Toggle_Checkmark() {
   var records = form.getRecords();
   var check_mark_id = 'fld-f14ccd51913b4348b2c32d9ac4eaf7f2';

   for (var index = 0, count = records.length; index < count; index++){
	// do something;
	var record = records[index];
	var value = record.getFieldValue(check_mark_id);
	if (value == true) {
		record.setFieldValue(check_mark_id, 0);
	}
   }
   form.saveAllChanges();
}

Toggle_Checkmark();

Thanks,

Brendan

[Sam Moffatt]

If you were to aim to optimise the script, I’d probably want to have a map of PDF name to Tap Forms record ID. You can do a retrieve of a record by key/record ID via the script so if you precomputed a map of these keys or used the record ID import mode for Tap Forms to import the records then it’ll be a single retrieve for retrieving the record (form_record_id is the field in the CSV file which if present will be prepended with a rec- prefix and allow you to use form.getRecordWithId). The horse has likely already bolted for this one but something to consider for future projects, so let’s go ahead with the other suggestion: PDF attachment name in a script field.

If you have the PDF attachment name in a script field, you could use the getRecordFromFormWithKey function in my script manager. The method signature looks like this:

getRecordFromFormWithKey(formName, keyFieldId, keyValue, createIfMissing = true, earlyTermination = true, alwaysScanOnMiss = false)

It creates in Javascript an index on a first pass and then does looks up from there (set earlyTermination to false and alwaysScanOnMiss to true). When you’re in a tight loop this should improve performance by doing the record loop once to build the index and then you can do a similar sort of roughly constant time retrieval for matching records. It’s still two loops but you remove the nesting and replace it with a more optimised data structure.

First step is to create a new script field in your Contracts form and in it you want to add in the code we use to grab the filename from the PDF:

record.getFieldValue('fld-e870153bd0a647c9823ca998c213d1fd')[0].filename;

You’ll need to tick the “Update records when saving” option when creating that to populate the new field in all of your record. Essentially we’re just copying the filename as a new field. At this point you might also want to setup a search to look for records that have this field empty because that seems likely a bug. You’ll also need the field ID from the form panel (underneath the “Description” field) which I refer to later as “fld-scriptfieldidhere” that you’ll need to change.

Using the code you provided as a basis, something like this should get you some of the way there:

document.getFormNamed('Script Manager').runScriptNamed('getRecordFromFormWithKey');

function Update_Records() {

    var csvForm = document.getFormNamed("csv");

    // Contracts
    var keyword_id = 'fld-a2126cbe512646e9ba144fb5a90b06dc';
    var pdf_id = 'fld-3ea77ccc18604174b722ece105965c44';

    // get use fixed field Id
    var csvPdf_id = 'fld-4bcd0f1fba5c4178bb8d10a112b17489';
    var csvKeyword_id = cvsForm.getFieldNamed('Keyword').getId();

    // Loop over all csv entries
    for (entry of csvForm.getRecords()) {
        // get pdf file name of CSV record
        var csvPdfName = entry.getFieldValue(csvPdf_id);

        // replace spaces with underscores
        csvPdfName = csvPdfName.replace('/ /g', '_');
        console.log("Processing: " + csvPdfName);

        contract = getRecordFromFormWithKey("Contracts", "fld-scriptfieldidhere", csvPdfName, false, false, true);

        if (!contract) {
            console.log('Unable to find matching PDF record: ' + csvPdfName);
            continue;
        }

        console.log("Found match: " + csvPdfName);

        // Update contract record
        var type_of_agreement_id = 'fld-6af499ed439143b297828643341d939b'
        contract.setFieldValue(type_of_agreement, 'Licensing Agreement')

        // This should “replace” keyword or keywords in a selected record
        var agreement_keywords_id = 'fld-a2126cbe512646e9ba144fb5a90b06dc';
        contract.setFieldValue(agreement_keywords_id, entry.getFieldValue(csvKeyword_id));
    }
    document.saveAllChanges();

    return 'done';
}

Update_Records();

We’ve still got the outer loop that iterates over the incoming CSV form, the “Contracts” loop is removed and replaced with the call to getRecordsFromFormWithKey which internally does a loop for you but also builds an index in memory that should make subsequent accesses quicker. Watch your memory usage in Activity Monitor but you’ve got 64GB of RAM so we should be fine. The script is finding the record for you so the code that lived inside your inner for loop moves up a level though there is a check to see if we got a record back. I did some minor changes to set the keyword based on the CSV form as an example of how I think that to work.

One thing I’ve done in scripts like this is put in a field for when it was processed to be able to skip it and also a counter to limit how many records are processed. This is useful for debugging scripts and being able to progressively execute it to get an idea of it:

let minTime = new Date().getTime() - 86400000; // one day
let processed = 0;
let skipped = 0;

for (...) {

    let previousDate = entry.getFieldValue('fld-previousdateidhere');
    if (previousDate && previousDate.getTime() > minTime) {
        skipped++;
        continue;
    }

    entry.setFieldValue('fld-previousdateidhere', new Date());

    processed++;

    if (processed > 100) {
        break;
    }

    // your existing for loop logic here
}

This uses an extra field and updates when the script is run to set it that way the next time you run the script, it skips past records it’s already “processed” so to speak and moves onto the next batch of 100. Instead of handling the date logic in code, you could also tie this into a saved search as well so that Tap Forms only gives you the candidate records.

[Brent S]

This is how I tried to fix, but still have the two loops:

function Update_Records() {

var csvForm = document.getFormNamed(“csv”);
var contractsForm = document.getFormNamed(“Contracts”);

// Contracts
var keyword_id = ‘fld-a2126cbe512646e9ba144fb5a90b06dc’;
var pdf_id = ‘fld-3ea77ccc18604174b722ece105965c44’;

// get use fixed field Id
var csvPdf_id = ‘fld-4bcd0f1fba5c4178bb8d10a112b17489’;

// Loop over all csv entries
for (entry of csvForm.getRecords()){
// get pdf file name of CSV record
var csvPdfName = entry.getFieldValue(csvPdf_id);

// replace spaces with underscores
csvPdfName = csvPdfName.replace(‘/ /g’, ‘_’);
console.log(“Processing: ” + csvPdfName);

// Loop over all contract records
for (contract of contractsForm.getRecords()){
if (!contract.getFieldValue(pdf_id) || contract.getFieldValue(pdf_id).length == 0) {
console.log(‘Record is missing PDF: ‘ + contract.getUrl());
continue;
}

// this assumes that there is only one PDF per contract!
var pdfName = contract.getFieldValue(pdf_id)[0].filename;

if (csvPdfName == pdfName){
console.log(“Found match: ” + pdfName);

// Update contract record
var type_of_agreement_id = ‘fld-6af499ed439143b297828643341d939b’
record.setFieldValue (type_of_agreement, ‘Licensing Agreement’)

// This should “replace” keyword or keywords in a selected record
var agreement_keywords_id = ‘fld-a2126cbe512646e9ba144fb5a90b06dc’;
record.setFieldValue (agreement_keywords_id, ‘Vaccine, Animal, Bacterial’)

break;
}

}
document.saveAllChanges();

}

return ‘done’;
}

Update_Records();

[Daniel Leu]

Brent, great to hear that it worked, finally!

Is there a way you can select the 1200 file records with the advanced search? If that’s the case you can select the saved search and just run the script with a little change:

Before:
for (contract of contractsForm.getRecords()){

After:
for (contract of search.getRecords()){

It is strange to get the ’empty records’ notice although there should be a file associated with every record. That’s something I would look into.

Fetching the file record might be what takes a bit of time. If you run this script frequently, it might be worth a try to save the PDF filename in a field and then just use this field in the comparison.

Oh, don’t forget to backup your database, specially with so many entries!

I might add a little sanity check as well to verify that a record was really found:

		var success = 0;
		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			if (!contract.getFieldValue(pdf_id) || contract.getFieldValue(pdf_id).length == 0) {
				console.log('Record is missing PDF: ' + contract.getUrl());
				continue;
			}

			// this assumes that there is only one PDF per contract!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;
			
			if (csvPdfName == pdfName){
				console.log("Found match: " + pdfName);
				success = 1;
				
				// Update contract record
				addKeyword(contract, keyword_id, entry.getFieldValue(csvKeyword_id));
			
				break;
			}
		
		}
		if (!success) {
			console.log("No match found: " + csvPdfName, "#FF0000");
		}

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Daniel Leu]

Your code is a bit messed up with two contract loops…

unction addKeyword(rec, kw_id, kw){
	var keywords = rec.getFieldValue(kw_id);
	
	if (keywords == undefined || keywords == ""){
		rec.setFieldValue(kw_id, kw);
	} else {
		rec.setFieldValue(kw_id, keywords + ',' + kw);
	}
}

function Update_Records() {

	var csvForm = document.getFormNamed("csv");
	var contractsForm = document.getFormNamed("Contracts");
	
	// recipes
	var keyword_id = 'fld-a2126cbe512646e9ba144fb5a90b06dc';
	var pdf_id = 'fld-3ea77ccc18604174b722ece105965c44';

	// get csv field Ids
//	var csvPdf_id = csvForm.getFieldNamed('PDF').getId();
//	var csvKeyword_id = csvForm.getFieldNamed('Keyword').getId();

	// get use fixed field Ids!!!!!!!!!!!!
	var csvPdf_id = 'fld-0cbd22a473cd4c58aa1dc47341a7157b';
	var csvKeyword_id = 'fld-53de1c4c633a4da6a4d0213158b5ef0a';

	// Loop over all csv entries
	for (entry of csvForm.getRecords()){
		// get pdf file name of CSV record
		var csvPdfName = entry.getFieldValue(csvPdf_id);

		// replace spaces with underscores
		csvPdfName = csvPdfName.replace('/ /g', '_');
		console.log("Processing: " + csvPdfName);
		
		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			if (!contract.getFieldValue(pdf_id) || contract.getFieldValue(pdf_id).length == 0) {
				console.log('Record is missing PDF: ' + contract.getUrl());
				continue;
			}

			// this assumes that there is only one PDF per contract!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;
			
			if (csvPdfName == pdfName){
				console.log("Found match: " + pdfName);
				
				// Update contract record
				addKeyword(contract, keyword_id, entry.getFieldValue(csvKeyword_id));
			
				break;
			}
		
		}
		document.saveAllChanges();
	
	}
	
	return 'done';
}

Update_Records();

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Sam Moffatt]

I’d also to check that there is a file attachment and skip ones that don’t:

		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			// this assumes that there is only one PDF per recipe!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;

to:

		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			if (!contract.getFieldValue(pdf_id) || contract.getFieldValue(pdf_id).length == 0) {
				console.log('Record is missing PDF: ' + contract.getUrl());
				continue;
			}
			// this assumes that there is only one PDF per recipe!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;

That should check there is an attachment and validate it’s not set plus log a message with a clickable link in the console. If that ends up being everything then that’s not great but it wouldn’t bomb part way through.

There are 35,747 entries (PDF’s), so it is only one field, but there are many entries.

Many records or a single record with multiple PDFs?

Re feedback: The last few videos hit half an hour and I need to work on getting the content back in the 10 minute range. I ended up throwing a bunch of stuff in one of the videos which when I went back should have probably ended up broken out into a few videos (ended up putting in chapter links for at least one of them). I’m trying to keep them as continuous takes to make sure I don’t miss any steps along the way though sometimes that leads to mild confusion as it’s also not scripted/practiced. Sometimes seeing something small and seemingly unimportant is the hook you need to understand. Thanks for the feedback!

[Daniel Leu]

In hindsight, it would have been better if you had described your problem as is and not made up this hypothetical recipe case….

As you might have noticed, all CSV form related fields use the a csv prefix to make it clear where this field resided. And yes, my script is a from script that is part of the contracts form. My assumption was that the CSV form is deleted after use and recreated with a new tagging set. So it makes sense that the script has a more permanent location.

Here is the updated script. Hope this works.

function addKeyword(rec, kw_id, kw){
	var keywords = rec.getFieldValue(kw_id);
	
	if (keywords == undefined || keywords == ""){
		rec.setFieldValue(kw_id, kw);
	} else {
		rec.setFieldValue(kw_id, keywords + ',' + kw);
	}
}

function Update_Records() {

	var csvForm = document.getFormNamed("csv");
	var contractsForm = document.getFormNamed("Contracts");
	
	// recipes
	var keyword_id = 'fld-a2126cbe512646e9ba144fb5a90b06dc';
	var pdf_id = 'fld-3ea77ccc18604174b722ece105965c44';

	// get csv field Ids
//	var csvPdf_id = csvForm.getFieldNamed('PDF').getId();
//	var csvKeyword_id = cvsForm.getFieldNamed('Keyword').getId();

	// get use fixed field Ids!!!!!!!!!!!!
	var csvPdf_id = 'fld-0cbd22a473cd4c58aa1dc47341a7157b';
	var csvKeyword_id = 'fld-53de1c4c633a4da6a4d0213158b5ef0a';

	// Loop over all csv entries
	for (entry of cvsForm.getRecords()){
		// get pdf file name of CVS record
		var cvsPdfName = entry.getFieldValue(csvPdf_id);

		// replace spaces with underscores
		cvsPdfName = cvsPdfName.replace('/ /g', '_');
		console.log("Processing: " + cvsPdfName);
		
		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			// this assumes that there is only one PDF per recipe!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;
			
			if (cvsPdfName == pdfName){
				console.log("Found match: " + pdfName);
				
				// Update contract record
				addKeyword(contract, keyword_id, entry.getFieldValue(csvKeyword_id));
			
				break;
			}
		
		}
		document.saveAllChanges();
	
	}
	
	return 'done';
}

Update_Records();

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks