Mass update of some records which cannot be identified by query

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!

Tap Forms will match up records upon importing if there’s a form_record_id column that has the same unique identifier for a record already in the database. But I’m guessing you probably don’t have that field in your CSV file. Was the CSV file generated externally? Or did you first export it from Tap Forms? If so, if you include the Record ID option on the Export Records settings, then you could achieve what you want and have Tap Forms update the Email Received checkbox for each of the matching records. Your Email Received field in the CSV file would have to have the value 1 to have it turn on that Checkmark field.

Thanks for providing this script to JB, Daniel! Very kind of you.

Another option to speed that inner loop up a bit in the script might be to use:

var matching_records = form.getRecordsForSearchTerm('"email@company.com"');

// loop over all contacts
   for (rec of matching_records){
       // do what you're doing now, but on a smaller subset.
   }

Of course, this would only work if the email address you’re targeting isn’t in any other fields because this is no different than using the general search to find things. Which also means that to get an exact match that doesn’t ignore punctuation, you need to double-quote the search term, which if you look closely, I’ve done in the search term.

And: greetings from Switzerland!

Sending greetings back….

Here is the updated script using the search feature Brendan pointed out:

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

		// get matching records
		let rec = form.getRecordsForSearchTerm(`"${emailCsv}"`);
		if (rec.length == 0) {
			console.log("Error: no matching record found for " + emailCsv, "#ff0000");
		} else if (rec.length == 1) {
			console.log(">  found match");
			rec[0].setFieldValue(email_received_id, 1);
		} else {
			console.log("Error: more than one record found for " + emailCsv, "#ff0000");
		}		
	}
	document.saveAllChanges();
}

Mark_Email_Received();

I added some additional checks to highlight if more than one matching record is found or if none is found at all.

For the technically inclined, this is how you can create the required quotes around a variable to get the exact match: form.getRecordsForSearchTerm(`"${variable_name}"`);

Brendan, it would be very helpful if form.getRecordsForSearchTerm() would support a field id as well to constrain the search to one field.

Good that the first version works.