Sam,
Thank you very much and for the revised code. At first (other than the first console.log) I was still getting “undefined”.
Then realised that the if statement is the wrong way around for the if part of the if/else:
if (company_name == null)
statement the code that follows should not be for the company for the individual.
After this it works fine.
Thank you again. I will have to explore your Script Manager Form Logger
I think what might be happening is that you’re getting tricked up by Javascript variable scope, it doesn’t feel right but you’re declaring the Dear_Name and Email_to_send_invoice inside the if statement. I would personally probably declare these at the top and maybe put a sentinel value in:
// Return values
var Dear_Name = "Placeholder";
var Email_to_send_invoice = "test@example.com";
This would then go to validate that they’re being overwritten properly. I don’t think that’s your problem here but it’s an approach I’d take as a first pass.
The second pass is rewriting so that the branches are a little more clear. You’re setting up a bunch of variables at the top of the script and then using a smaller if statement at the bottom so it’s hard to know which statements belong to the block. My next step is a rewrite to try to group the variables together:
// Return values
var Dear_Name = "Placeholder";
var Email_to_send_invoice = "test@example.com";
// Form ID
var client_contact_details_id = 'fld-c53e48e48e3a40c7a5656ab92f39ecc9';
var client_first_name_and_email_address_details = record.getFieldValue(client_contact_details_id)
//Company details
// company name
var company_name_id = 'fld-a3300e3db044421598ae171c0a2d4834';
var company_name = client_first_name_and_email_address_details.getFieldValue(company_name_id);
console.log(company_name);
if (company_name == null) {
// first name of company contact
var first_name_company_contact_id = 'fld-24ee4dae33d941bf81e1667853a7a949';
var first_name_company_contact = client_first_name_and_email_address_details.getFieldValue(first_name_company_contact_id);
// email address of company contact
var email_company_contact_id = 'fld-8cfa7613f04f4a8b82652bc27f3c05df';
var email_company = client_first_name_and_email_address_details.getFieldValue(email_company_contact_id);
Dear_Name = first_name_company_contact;
Email_to_send_invoice = email_company;
} else {
//Individual details
// first name of individual
var first_name_id = 'fld-45599a4f0a6342d8973faed53dfbfed8';
var first_name = client_first_name_and_email_address_details.getFieldValue(first_name_id);
// Email address of individual
var email_id = 'fld-8f9a928a7d554527b4127ef811136210';
var email_individual = client_first_name_and_email_address_details.getFieldValue(email_id);
Dear_Name = first_name;
Email_to_send_invoice = email_individual;
}
console.log(Dear_Name + " " + Email_to_send_invoice);
There was some dead code at the top that I eliminated along the way which makes it a little easier to follow.
I put in a console.log(company_name) to see what is in that variable so I would know what pathway to take. You’ve got a couple of the console.log statements but I’d probably beef it up to make sure what you get back from getFieldValue is set to a value.
The last step is to make sure all of the field ID’s make sense. You’re not getting any runtime errors so the client_first_name_and_email_address_details field is set to a correct value but I’d double check the other ID fields. It’s easy to accidentally hook up the wrong fields (in fact I have it on video!) so it never hurts to go back and double check. Keep in mind that Tap Forms won’t give you an error if you give it a field ID that isn’t set in the record, it’ll return undefined. One other trick I have here is that in my Script Manager is a Form Logger that can output variables similar to the Tap Form’s Script Editor but prefixed with the form name as well, e.g.:
// Boxes
var boxes_formID = "frm-8ba92c77b5e8476a987945f40ff2b41a";
var boxes__title_fldID = "fld-e289240daccc40409d75aee26493110c"; // text
var boxes__box_id_fldID = "fld-f25492cf56384369b278f7ea41ee258f"; // text
var boxes__note_fldID = "fld-69754dee7e9247e0b3087e2526881626"; // note
var boxes__dimensions_fldID = "fld-3e5e30f1d352495f989cc0dc1b306128"; // text
var boxes__box_type_fldID = "fld-97b2ab2bdbb1400d9384c7634e7c6d48"; // text
var boxes__box_photos_fldID = "fld-72a520cff50a4bbd92f1e8ff6eec91db"; // photo
var boxes__box_is_full_fldID = "fld-c824d1ff2ced4b948b5907e0a4804bca"; // check_mark
var boxes__purchases_fldID = "fld-482c4f5c71ba4cd7a06c32d1c1d7d983"; // form
var boxes__containing_box_fldID = "fld-fe868108cdd844d895ea982e820b346c"; // text
var boxes__boxes_fldID = "fld-74466a4b96dc43b4bcbe153646b76911"; // form
var boxes__tracking_number_fldID = "fld-54af5c1e20e14d20a7c82a40679a2a8a"; // text
var boxes__shipments_in_box_fldID = "fld-bc84b43e3cf649cdb27e4085b401aa2b"; // form
It makes it clear which field a variable is from because I ended up running into situations where it was hard to keep track of the forms for which a field applied. It also includes the field type as an extra hint which is sometimes useful to be reminded about.
The database I use for some of my work has
1. a form which contains client contact details (this has separate sections for contact details for company clients and then individual clients)
2. a second form which contains the individual jobs
3. there is a Link to Form field from 1 to 2.
In the jobs form I have a section which contains details of invoicing and I want to pull in the first name and address from the client contact form.
There are separate first name and email addresses fields in the client contact form for companies and individuals.
In the jobs form I wish to create a script the text of the email and would use the first name and address within the script (which I then export as CSV, and using Keyboard Maestro automatically create the email and automatically attach the invoice)
I have created a script where I have pulled in the fields from the client contact details and wish to create an if statement which selects the first name and email of whether the client is a company or an individual.
However, the script only produces ‘undefined’ although I know when I use the actual fields from the client contact form the console.log returns the correct information.
The script is as follows:
// Form ID
var client_contact_details_id = 'fld-c53e48e48e3a40c7a5656ab92f39ecc9';
var result = '';
var EmailAddressRecord = record.getFieldValue(client_contact_details_id);
var email_id = 'fld-8f9a928a7d554527b4127ef811136210';
var emailaddressfield = EmailAddressRecord.getFieldValue(email_id);
result = emailaddressfield;
result;
var client_first_name_and_email_address_details = record.getFieldValue(client_contact_details_id)
//Company details
// company name
var company_name_id = 'fld-a3300e3db044421598ae171c0a2d4834';
var company_name=client_first_name_and_email_address_details.getFieldValue(company_name_id);
// first name of company contact
var first_name_company_contact_id = 'fld-24ee4dae33d941bf81e1667853a7a949';
var first_name_company_contact=client_first_name_and_email_address_details.getFieldValue(first_name_company_contact_id);
// email address of company contact
var email_company_contact_id = 'fld-8cfa7613f04f4a8b82652bc27f3c05df';
var email_company = client_first_name_and_email_address_details.getFieldValue(email_company_contact_id);
//Individual details
// first name of individual
var first_name_id = 'fld-45599a4f0a6342d8973faed53dfbfed8';
var first_name = client_first_name_and_email_address_details.getFieldValue(first_name_id);
// Email address of individual
var email_id = 'fld-8f9a928a7d554527b4127ef811136210';
var email_individual = client_first_name_and_email_address_details.getFieldValue(email_id);
if (company_name == null) {
var Dear_Name = first_name_company_contact;
var Email_to_send_invoice = email_company;
} else {
var Dear_Name = first_name;
var Email_to_send_invoice = email_individual;
}
// console.log(yesnot);
console.log(Dear_Name + " " + Email_to_send_invoice);
Running the script produces:
24/03/2021, 11:36:03 / Notarial Act / Email adress for invoice
undefined undefined
Help would be gratefully received.
With a bit of practice I think you can get there. Scripting in Tap Forms unlocks a large amount of power to add custom workflows and interactions, so if you’re able to get the knack of it you can do a lot more.
I did a follow up video on the pick list idea as well as including some other suggestions from Brendan as well. The pick list approach is a little more nuanced and I must admit it’s a great suggestion because I use pick list based record colouring in a few of my own forms.
I did a quick video and posted on YouTube walking through create a new form with a couple of fields, creating a new script field that can be used to set the record colour and then I walk through using a calculation field to make the script a little simpler.
Here’s the final script from the video to make it easier to copy that portion in (don’t forget to change the record.getFieldValue lines to your own field IDs):
function Record_Colour_Setter() {
var rating_date = record.getFieldValue('fld-072414fe5ab04e93aba36c1051dc1f22');
if (!rating_date) {
record.setRecordColor(null);
form.saveAllChanges();
return;
}
var date_difference = record.getFieldValue('fld-14c4599832f74205a842c2c4d666709b');
if (date_difference <= 0) {
record.setRecordColor("#FF0000");
form.saveAllChanges();
} else if (date_difference >= 90) {
record.setRecordColor("#00FF00");
form.saveAllChanges();
} else {
record.setRecordColor("#FFBF00");
form.saveAllChanges();
}
}
Record_Colour_Setter();
Thank you Sam Moffatt,
What yon suggest seems to be a good solution. So, the million dollar question, how do I actually setRecordColor for a script cell and how do I make it compare cell content to the current date? I am very new to Tap Forms and not really much of a techy – so need simple “do this, then do that” type instructions please…
Cheers
It works for me. What you don’t see anymore is the ‘alias arrow’ on the thumbnail. Brendan had to change some stuff in order to add Javascript API support for this feature.
But look at the size of the file. If it is an alias, it is about 700 bytes.
Attachments:
You must be
logged in to view attached files.
Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks
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.
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.
Cheers, Daniel
---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks
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… :-( )
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.
Thank you, Daniel! Will try (have to admit that I am not very familiar with scripting). And: greetings from Switzerland!
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
This is a message for Sam Moffat concerning his video (https://www.youtube.com/watch?v=Juwt0OGoEkc) where he provided a script to automatically add an incremental number (from 14:14 in the video).
I have tried to replicate exactly what is shown in the video, but on adding a new record does not insert a number as Sam’s video shows.
Here is the script I created:
function Auto_Line_Number() {
var orders = record.getFieldValue('fld-01d4dcc690be4cfab6e634f78da8e568');
var line_items = orders.getFieldValue('fld-fd35beba82334d6586f354d914eb1857');
console.log("found autoline number entries: " + line_items.length);
var line_number = record.getFieldValue('fld-5d11f80da2e04cadbb82b52829e1be8c');
if (line_number){
record.setFieldValue('fld-5d11f80da2e04cadbb82b52829e1be8c', line_items.length);
document.saveAllChanges();
return "updated line number to " + line_items.length;
}
return "Did not update line number, already set";
}
Auto_Line_Number();
Attached is the Tap Forms database.
Help would be gratefully received.
Attachments:
You must be
logged in to view attached files.
You can set the colour of a record via scripting (setRecordColor) and you could do that via a script field that checks your date versus the current and updates the record colour and also that would be a field you could search on (Good, Warn, Expired). One other limitation is that Tap Forms won’t re-evaluate all records in a form without being asked to do so which for these date based use cases you will need to click on the “refresh records list” to update the calculations for all of your records. There was a post on programmatically setting record colour that might be helpful for you as well.
