Tap Forms – Organizer Database App for Mac, iPhone, and iPad › Forums › Using Tap Forms › Mass update of some records which cannot be identified by query
- This topic has 14 replies, 3 voices, and was last updated 3 years, 2 months ago by
Daniel Leu.
-
AuthorPosts
-
March 15, 2021 at 1:26 AM #43845
JB BeParticipantI 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.March 15, 2021 at 9:55 AM #43848
Daniel LeuParticipantI 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!
March 15, 2021 at 11:49 AM #43850
JB BeParticipantThank you, Daniel! Will try (have to admit that I am not very familiar with scripting). And: greetings from Switzerland!
March 15, 2021 at 8:53 PM #43852
BrendanKeymasterTap 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 value1
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.
March 15, 2021 at 9:56 PM #43857
Daniel LeuParticipantOh, I wasn’t aware of
form.getRecordsForSearchTerm()
. That makes it a bit faster. Cool!March 15, 2021 at 11:02 PM #43859
Daniel LeuParticipantAnd: greetings from Switzerland!
Sending greetings back….
March 16, 2021 at 12:50 AM #43860
JB BeParticipantThank 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… :-( )
March 16, 2021 at 9:45 AM #43865
Daniel LeuParticipantHere 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.March 16, 2021 at 3:25 PM #43870
JB BeParticipantThank 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.March 16, 2021 at 5:42 PM #43874
Daniel LeuParticipantGood that the first version works.
March 16, 2021 at 5:47 PM #43876
Daniel LeuParticipantWhat is the content of your main database? Thanks!
March 17, 2021 at 12:57 AM #43881
JB BeParticipantContents 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.March 17, 2021 at 1:05 AM #43883
JB BeParticipantNot 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.
March 17, 2021 at 7:24 AM #43884
JB BeParticipantAmend:
Not sure whether uploading the .tapform-file as a ZIP worked (although the forum system seemed not to reject). …
March 17, 2021 at 9:38 AM #43887
Daniel LeuParticipantDoesn’t look like. You can send it to daniel_at_danielleu_dot_com and I’ll have a look.
-
AuthorPosts
You must be logged in to reply to this topic.