Search Results for 'script'

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

Cheers, Daniel

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

[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… :-( )

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

[JB Be]

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

[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

[Victor Warner]

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.

[Sam Moffatt]

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.

[Sam Moffatt]

There is a bunch of Javascript resources out there, mostly focused on the web unfortunately but the basics of the language are transferable so it’s not a complete waste, plus it helps understand how the web works as well so not a complete loss. Javascript is an awkward language at times with a bunch of beginner pitfalls but it’s one of the most popular languages out there when you realise every website is powered by it, it’s used as a scripting engine in a bunch of places like Tap Forms and then you have node.js for running it as a server language.

In any case you’ve got your own use cases with Tap Forms which I feel is the best way to learn. Have a problem you care about that you want to solve and search for how to solve it. It’s a great way to learn how to program :)

[Sam Moffatt]

Why don’t you just do the following:

prompter.addParameter('Kalender: ', 'calname', 'popup', arr)
         .show('Bitte Kalender auswählen:', callbackFunction);

And sorting should just be arr.sort() instead of just arr:

prompter.addParameter('Kalender: ', 'calname', 'popup', arr.sort())
         .show('Bitte Kalender auswählen:', callbackFunction);

Since arr is already an array, you don’t need to extract it back out to be an array.

One other trick is the JavaScript spread operator which will expand out an array for you automatically though in this case it’s not useful (since you’re expanding an array back into the same array):

prompter.addParameter('Kalender: ', 'calname', 'popup', [...arr])
         .show('Bitte Kalender auswählen:', callbackFunction);

You can also do a [...arr].sort() as well because the [] syntax is returning an array object you can operate on directly.

It’s possible I miss understood something but I think this solves the problem for you.

[Sam Moffatt]

I think the best option I’d go for is T L Ford’s JavaScript 101 which is focused from a Tap Forms users approach and I have some videos on my YouTube channel as well.

There are plenty of web focused Javascript resources (Mozilla Developer Network, W3Schools) and a few resources leveraging node.js (also W3Schools as an example), there aren’t that many resources that focus on pure Javascript that I’ve found. A lot of the resources include platform specific stuff that really only relates to the use in web browsers or to node.js rather than the ECMAScript standard.</p>

[Ray Robillard]

Thanks a lot, Sam ! I will try this tomorrow.

Is there an online course on JavaScript you recommend ?

[Chris Ju]

Hi,

i’m using the new JS API function Utils.getCalendarNames() (Big thanks to Brendan for this!) in the prompter dialog to list/choose and use for my scripts:

...
arr = Utils.getCalendarNames();
var calname;
let prompter = Prompter.new();
prompter.addParameter('Kalender: ', 'calname', 'popup', [arr[0], arr[1], arr[3], arr[4], arr[5], arr[6], arr[7], arr[8], arr[9], arr[10], arr[11], arr[12], arr[13], arr[14], arr[15], arr[16], arr[17], arr[18], arr[19], arr[20], arr[21], arr[22], arr[23], arr[24], arr[25], arr[26], arr[27], arr[28], arr[29], arr[30]])
.show('Bitte Kalender auswählen:', callbackFunction);
...

1. In my case i have 30 items from the array in my list. If i have less than 30 calendars, the “missing” items in the prompter are empty. Is it possible to limit the list according to the length of the array so that only the calendars that actually exist are displayed?

2. Is it possible to sort the calendar list alphabetically?

Thanks!

Chris

[Sam Moffatt]

Well we think the script field execution in a table row is also bugged right now. I can get it to trigger when I edit a related field in the same row but it doesn’t recalc everything and when I run the recalc formulas button at the bottom of the record. So you end up with the situation where all of the other rows are wrong until you click into the record and hit enter to trigger the script field for that row.

The alternative that works consistently is to create a script field in the form that handles recalculating. I haven’t been able to get it to trigger consistently either but when I click “recalc formulas” it at least updates consistently for me.

Here’s the script:

function Populate_Percentage() {
	let total = record.getTotalOfLinkedFieldForField('fld-b9927271feeb4fe2b9c0cd32f36c7cd1', 'fld-3fead523555c48f9a77377fd340e59f6');
	let table = record.getFieldValue('fld-b9927271feeb4fe2b9c0cd32f36c7cd1');
	
	for (let index in table) {
		var per_serving_amount = table[index].getFieldValue('fld-3fead523555c48f9a77377fd340e59f6');
		table[index].setFieldValue('fld-9048ea3263164a739908f8add07b5ed7', per_serving_amount / total);
	}
	document.saveAllChanges();
}

Populate_Percentage();

Some definitions:

• fld-b9927271feeb4fe2b9c0cd32f36c7cd1 is the ID of the table field.
• fld-3fead523555c48f9a77377fd340e59f6 is the ID of the per_serving_amount field or for you this would be your quantity field.
• fld-9048ea3263164a739908f8add07b5ed7 is the ID of the percentage field or your ‘% of recipe’ field.

You should be able to replace the IDs with the ones from your form and it should more or less work.

• Decomposing the script, the let total line gets the total from the table field of the field in the table field.
• The let table line gets all of the records in the table field.
• The for line is the start of a loop that goes through all of the records.
• The var per_serving_amount gets the value of the per serving field from the table row number index.
• The setFieldValue is setting our percentage field for that row (the table[index]).
• The document.saveAllChanges() is required to tell Tap Forms you made a change and you want to keep it.
• Finally the Populate_Percentage() line triggers everything.

This should work if you create a new script field in your form, ensure your percentage field is a number field and then you update the ID’s to match. It does mean to update you will need to click on the “Recalculate formulas” button at the bottom of your record. You can hide the script field so that it doesn’t show up in your default layout as well.

[scott johnson]

Oof – that may be exceeding my capabilities. I really appreciate the insight – let me play with it a bit today and see if a non-computer guy can figgure out scripting 8-)

[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;
}