Search Results for 'form.getRecords'

[JCK]

Inspired by this post detailing how to get watched TV shows, I decided to attempt a Movie import script via TheMovieDB’s API. I’m very new to scripting (really only used iOS Shortcuts) and have gotten stuck and could use some direction or assistance.

The big issue I can’t seem to crack is iterating through the JSON for to pull out the cast and add them to the cast table on the form. I’ve looked through the example of the above of pulling the singular episode details, but the JSON for TheMovieDB is more robust than OMDB and I’ve confused myself.

Any Ideas?

var tmdbAPI = 'xxx';

var title_id = 'fld-7f17a3883cf742ca90a732565f687953';
var released_id = 'fld-4f1d3a5878914910954b65c2f782abfd';
var imdbid_id = 'fld-0b8bd8338d8f494aa5b7099c42230e70';
var poster_id = 'fld-bace3b81b9ab4cc9951a9445d12a63b3';
var summary_id = 'fld-d16b4361266b48ee9c3b88afd29fd5ac';
var runtime_id = 'fld-f096b51db4c447e18bf10298135dfaa8';
var tagline_id = 'fld-ac1ad056b5004ed8a19f8d272ae01e2b';
var cast_id = 'fld-0b85d9aef49f4fd58726f6830a03ba11';

var actor_id = 'fld-07249465a7ea45e8830da27e62b3121d';
var role_id = 'fld-bf225b3c443248fd97c5737312acd28b';

var itemID; 

function fetchDetailsFromURL() {
    fetchURL = <code>https://api.themoviedb.org/3/movie/${itemID}?api_key=${tmdbAPI}&language=en-US</code>;
    return Utils.getJsonFromUrl(fetchURL);
}

function fetchCastFromURL() {
    fetchURL = <code>https://api.themoviedb.org/3/movie/${itemID}/credits?api_key=${tmdbAPI}&language=en-US</code>;
    return Utils.getJsonFromUrl(fetchURL);
}

function getCast() {
	var cast = fetchCastFromURL()
	return cast
	}

function getData() {
	var film = fetchDetailsFromURL();
	
	var imdbID = film.imdb_id;
	
	console.log(imdbID)
	
	var itemIds = new Set();
	var currentItemsByImdbID = {};
   var allCurrentItems = form.getRecords();
   for (let currentItems of allCurrentItems) {
    currentItemsByImdbID[currentItems.getFieldValue(imdbid_id)] = currentItems;
    itemIds.add(currentItems.getFieldValue(imdbid_id));
  }
	
	let newRecord;
  	if (itemIds.has("http://imdb.com/title/" + imdbID)) {
  	  	Utils.alertWithMessage(film.title + ' already exists.', 'Sorry.');
  	} else {
		newRecord = form.addNewRecord();
		newRecord.setFieldValues({
     		 [title_id]: film.title,
     		 [released_id]: film.release_date,
     		 [imdbid_id]: "http://imdb.com/title/" + film.imdb_id,
    		 [summary_id]: film.overview,
    		 [runtime_id]: film.runtime,
    		 [tagline_id]: film.tagline,
      })	
	}
	
	var Poster = "https://www.themoviedb.org/t/p/w1280/" + film.poster_path
	if (Poster != null) {
   newRecord.addPhotoFromUrlToField(Poster, poster_id);
  	}
   form.saveAllChanges();
}

var prompter = Prompter.new();
prompter.cancelButtonTitle = 'Cancel';
prompter.continueButtonTitle = 'Go';
prompter.addParameter('TMDB Number', 'itemID');

prompter.show('Enter an TMDB code', getData)

Here are the TMdb API details: https://developers.themoviedb.org/3/movies/get-movie-credits

[Sam Moffatt]

There isn’t a way to write a file directly, if I was to put it straight to a file I would send it to a web service and use that to dump it to disk for me. You can use Utils.postContentToUrlWithContentType() or Utils.postJsonToUrl() to call out to a web service. Probably for your use case postContentToUrlWithContentType with a simple text content would suffice. They’re pretty simple API’s with details on the Javascript page.

Something like this should work:

let entries = [];
for (let record of form.getRecords()) {
  entries.append(record.getFieldValue('fld-yourfieldid'));
}
Utils.postContentToUrlWithContentType(entries.join("\n"), "http://localhost/tapforms-bridge.php", "text/plain");

Then tapforms-bridge.php could look like this:

<?php
file_put_contents("/tmp/file.txt", file_get_contents("php://input"));

As a simple example of getting data out, it’s not the greatest solution but it should do the trick. You’d need to get the built in Apache and PHP set up on your Mac but there are plenty of guides out there for that.

[Sam Moffatt]

The CSV export will likely wrap things in quotes for compatibility reasons, I don’t believe there is a way I’ve seen to turn it off.

You could easily create a form script that generates the output via console.log that you could easily grab out of the display, something like:

for (let record of form.getRecords()) {
  console.log(record.getFieldValue('fld-yourfieldid'));
}

Would do the trick to dump out all of the records to the console for you. Replace the fld-yourfieldid with the field ID you care about, you can get the field ID by double clicking on the field you want in the script editor or from underneath the field description in the field editor.

[Daniel Leu]

1) Yes, it is. There is an API function that returns all the records for a named search: var records = getSearchNamed(name)

2) There is an API function as well to perform a search var records = form.getRecordsForSearchTerm("search term");, but it searches all fields.

But that’s not really what you want. But I have a little function for you that only searches a given field.

// Returns an array of record objects for the specified search term in the given field. All records of the current form are searched.
function getRecordsForSearchTermInField(searchTerm, fieldId) {
	
	let result = []
	for (r of form.getRecords()) {
		let field = r.getFieldValue(fieldId)
		
		if (typeof field === 'object' && field !== null){
			field = JSON.stringify(field)
		}
		
 		if (field.search(searchTerm) >= 0){
 			result.push(r)
 		}	
	}

	return result
}

You can use it like this
let records = getRecordsForSearchTermInField("X", 'fld-xxx'),
or use a regular expression as follows
let records = getRecordsForSearchTermInField(/x/i, 'fld-xxx').
More about using regular expression can be found here https://www.w3schools.com/js/js_regexp.asp.

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

[Daniel Leu]

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

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

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!

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

[Ray Robillard]

Hi !

I’ve a movie database in which there’s a Tag pick list, made of checkboxes. I want to find how many different records have a certain item in this pick list checked.

So thinking the pick list returned something I wasn’t sure of, I tried to get the type of the object, but all I am getting is undefined.

var value = movie_form_records[index].getFieldValue(movie_form_tags_id);
console.log(typeof value);
(console output is “undefined”)

So when I try this :
var pos = value.search(“Feel good”);

It never returns any item, despite the fact that some of the records have this tag. I know this because using the search filter, I can find several movies for which this tag is checked.

My loop goes through all the records and make the following test :
if (pos >-1) {
nombre++;
}

But when I am running the code, I am getting the following output in the console :

2021-03-10, 8:17:57 AM / Movies / Feel good
Feel good: TypeError: undefined is not an object (evaluating ‘value.search’), line:(null)
Feel good: TypeError: undefined is not an object (evaluating ‘value.search’), line:(null)

The whole code is :

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

var pos = value.search(“Feel good”);

if (pos >-1) {
nombre++;
}

}

return nombre;
}

So.. what am I doing wrong ? I’m a total newbie at JavaScript. So maybe I missed something somewhere.

Thanks,

Ray

[Daniel Leu]

Hi Gerhard,

You can use the progress feature for that, but this seems to be an overkill for such a simple task. Following script does the same thing:

function setAddress() {
	const adresse_lang_id = 'fld-xxx';
	const maps24_id = 'fld-xxx';

	for (r of form.getRecords()){
		let x = r.getFieldValue(adresse_lang_id);
		r.setFieldValue(maps24_id, x);	
	}
	document.saveAllChanges();
}

setAddress();

But I’m wondering if using a form script for this tasks is what you really want. Whenever you run the script, all your maps24 field are updated.
It might be better to use a field script for that.

function setAddress() {
	const adresse_lang_id = 'fld-xxx';
	const maps24_id = 'fld-xxx';

	let x = record.getFieldValue(adresse_lang_id);
	record.setFieldValue(maps24_id, x);	
	document.saveAllChanges();
}

setAddress();

Now the target field is only updated when the adresse_lang field is changed.

[Gerhard Hoffmann]

Hi Eddy,

I’m using TabForms since 4 months and the script is a real game changer. I’m not an expert in script programming, but my son helped me a lot.
I had a similar problem and I want to fill in the address fields to the field location, but not only to one address, it should do the work to all addresses. See, we use a for-next-loop.

// start

var adresse_lang_id = ‘fld-5840ffe2e4b04f01a150329d15498877’;
var maps24_id = ‘fld-ce41104cdbc2405eb2ad0f9be8baa35a’;
var progress = Progress.new();

function ProcessRecords(records) {
for (index in records) {

if (progress.cancelled) {
console.log(‘Cancelled operation’);
break;
}

var aRec = records[index];
var x = aRec.getFieldValue(adresse_lang_id);
aRec.setFieldValue(maps24_id, x);

progress.updateProgress(index);
}
}

var records = form.getRecords();
progress.totalCount = records.length;
progress.currentCount = 1;
console.log(‘Begin’);

// show the progress sheet
progress.show(‘Processing Records…’);

ProcessRecords(records);

// dismiss the progress sheet when done.
progress.dismissProgress();

// save your changes
form.saveAllChanges();

console.log(‘End’);

// end

Regards,

Gerhard

[Daniel Leu]

I suppose I could just use a text field, but even that would be annoying because if I convert an existing Date field to a text field, the data is erased. I already have 200 records, so converting to text would require me to cut and paste the existing date 200 times.

You could use a form script to translate the date field into a text field.

Following script would do that. You just need to update date_id and text_date_id to match your form.

function Convert_Date() {

	var date_id = 'fld-f74f442f93b7499aba86170cbab5e3a1';
	var text_date_id = 'fld-87e6c288a4e042c1bbfff27849a4b224';

	// loop over all records
	for (rec of form.getRecords()){
		let date = rec.getFieldValue(date_id); // get date
		if (date){ // if date exists
			let textDate = date.toLocaleDateString("en-US", { year: 'numeric', month: 'numeric', day: 'numeric' }); // create date string
			console.log(textDate);
			rec.setFieldValue(text_date_id, textDate); // store date string
		} else {
			console.log("Empty date");
		}
	}

	document.saveAllChanges();

}

Convert_Date();

[Gregory martin]

Here is a more compact version. Sets the script field to 1 if exists or 0 if unique.

var mainfield_id = 'fld-d5dba46d240d4d41bd792970526b5bc9'; // change this to your field ID
var newvalue = record.getFieldValue(mainfield_id);
var thisrecord = record.getId();
checkunique();
function checkunique() {
unique=0;
var records = form.getRecords();
unique = 0;
for (var index = 0, count = records.length; index < count; index++){
	// do not check current record
	if (records[index].getId()!=thisrecord) {	
  	    // check other record values for duplicate
    	if ((unique == 0) && (records[index].getFieldValue(mainfield_id) == newvalue)) {
    	   unique=1;
           console.log("Already exists");
           break;
        }     
        return unique;  
}}};

[Gregory martin]

You could check a field for being duplicated by adding this to a script field on your form, and the script is triggered when you press enter on the field you specify in the mainfield_id. Not sure how fast this would be on thousands of records though! You need to change your id for the field your are checking. This code snippet writes out ALREADY EXISTS in the console, otherwise the values of the records, so amend as needed of course

// ***** this needs to be your field ID
var mainfield_id = 'fld-d5dba46d240d4d41bd792970526b5bc9';
// ===========

var newvalue = record.getFieldValue(mainfield_id);
var thisrecord = record.getId();
checkunique();

function checkunique() {
unique=0;
var records = form.getRecords();
unique = 0;
for (var index = 0, count = records.length; index < count; index++){
    // ***************************
	// do not check current record
    // ***************************
	if (records[index].getId()!=thisrecord) {
	
  	   console.log(records[index].getId());
  	   console.log(records[index].getFieldValue(mainfield_id));
  	   console.log(newvalue)
  	   
  	    // check other record values for duplicate
    	if (records[index].getFieldValue(mainfield_id) == newvalue) {
           console.log("Already exists");
        }       
}}};