Mass update of some records which cannot be identified by query

[JB Be]

I have a database with >500 members of a club. Every record (member) has the field “email address”.

Now, in real life, some 300 members have been notified of an event by means of a specific email message, some have not. There is no way to identify the members who have an email message received (or the members who have not received it, respectively) by an appropriate query in the database.

To monitor which of the members have received this specific email message, I have added the field “Email (x) received” (content: bascically Yes/No). I also have a “csv list of all the email addresses” to which the email message has been sent.

Is there a way to bulk update the field “Email (x) received” with the entry Yes in all those records (and only in those) where the value in the field “email address” matches an email address contained in the “csv list of all the email addresses”?

In other terms, I would like to use the email address as the unique identifier for all the records where a specific field should be updated; kind of ‘if email address is on the list, then update; if not, don’t update’.

Thanks for any hint.
JB Be.

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

[JB Be]

Thank you, Daniel! Will try (have to admit that I am not very familiar with scripting). And: greetings from Switzerland!

[Brendan]

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.

[Daniel Leu]

Oh, I wasn’t aware of form.getRecordsForSearchTerm(). That makes it a bit faster. Cool!

[Daniel Leu]

And: greetings from Switzerland!

Sending greetings back….

[JB Be]

Thank you for the additional input!

… But I’m guessing you probably don’t have that field in your CSV file. Was the CSV file generated externally?…

Yes, generated externally. That’s the point. The CSV file is basically the content of the “To:” field of the respective ordinary Outlook message and generated in Excel or LibreOffice.

Another option to speed that inner loop …

Hmmm. Where exactly should this go in the full script? And the term “email@company.com”, should it stay exactly like that or does it need to be replaced in my specific script by something specific (sorry, as I pointed out above, scripting is not my expertise… :-( )

[Daniel Leu]

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.

[JB Be]

Thank you again, Daniel (and Brendan, before).

I made some trials:

-> the first (yesterday’s) version worked fine. :-)

-> the second (today’s) version produced a rather buggy result:
While the form ‘Email Received’ contained the records:
BBB@abc.ch
CCC@abc.ch
EEE@abc.ch
(see attachment)
the script pretended to have found an issue with the record ‘CCC@abc.ch’ (which I don’t understand), and pretended to have found a match with ‘EEE@abc.ch’ (which is plainly wrong); see the other attachment.

Any idea, why?

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

Good that the first version works.

[Daniel Leu]

What is the content of your main database? Thanks!

[JB Be]

Contents of my main database: See attached.

I am also adding my .tapform-file as a ZIP.

Attachments:

You must be logged in to view attached files.

[JB Be]

Not sure whether uploading the .tapform-file as a ZIP (although the forum system seemed not reject). Happy to share via private mail, if you wish.

[JB Be]

Amend:

Not sure whether uploading the .tapform-file as a ZIP worked (although the forum system seemed not to reject). …

[Daniel Leu]

Doesn’t look like. You can send it to daniel_at_danielleu_dot_com and I’ll have a look.

[Author]

Posts