Search Results for 'form.getRecords'

[robinsiebler]

I added a script (Combine Items) to combine duplicate items into one.

function combineItems() {
	// Combine duplicate items into one item

	var dict = {};
	var records = form.getRecords();
	var item_id = 'fld-097c3d751c854b1fb4b528238d48b2c4';
	var quantity_id = 'fld-285e2c9de4924b7fbba2fd8311ceb625';

	for (var i = 0, count = records.length; i < count; i++){
		item = records.getFieldValue(item_id);
		quantity = records.getFieldValue(quantity_id);
		record_id = records.getId();

		if (dict[item] === undefined) {
			dict[item] = [quantity, record_id];
		} else {
			dict[item][0] = dict[item][0] + quantity;
			form.deleteRecord(records);
			rec = form.getRecordWithId(dict[item][1]);
			 rec.setFieldValue(quantity_id, dict[item][0]);
			form.saveAllChanges();
		}
	}
}

combineItems();

[Sam Moffatt]

Let’s take a step back and look at focusing on just handling the object we’re getting back. Looking at the API, it’s returning an object and then an array for cast. Let’s look at what printing that out could look like as a script. I’m going to remove the variables in the header and prompter at the bottom just to cut down a little and let us focus.

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

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

function getData() {
    getCast();
}

// prompter code below here

That’s not going to do a lot beyond (hopefully) make the web request. Let’s expand getCast a little:

function getCast() {
    var cast = fetchCastFromURL();
    console.log(JSON.stringify(cast));
    return cast;
}

All going well you should see the same JSON representation in the console as we would see from the API. Sometimes cast can be falsy if the request fails, so let’s handle that:

function getCast() {
    var cast = fetchCastFromURL();
    if (!cast) {
        console.log("Request to get cast failed");
        return [];
    }
    console.log(JSON.stringify(cast));
    return cast;
}

We’re just going to return an empty array here and log a message when we fail the request. The cast result is actually a credits object, so let’s do a little rename of our methods and return the internal cast response:

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

function getCast() {
    var credits = fetchCreditsFromURL();
    if (!credits) {
        console.log("Request to get credits failed");
        return [];
    }
    console.log(JSON.stringify(credits));
    return credits.cast ? credits.cast : [];
}

When we look at the documentation, cast is listed as an optional array element inside the credits response object. This means if it is set it should be credits.cast, you can see why we renamed the variables now. The ? syntax is a ternary operator and what we’re doing is checking if credits.cast is truthy and if it is we return it otherwise we hand back an empty array.

Now it’s time to expand out getData() and we’re going to put a loop in just to see what we get back:

function getData() {
    let cast = getCast();
    for (let castMember of cast) {
        console.log(castMember.name);
    }
}

All going well when this is tested we should see an array of cast members’ names printed out into our console. We might have an empty array returned and if that was an error hopefully we also got a log message saying as much (e.g. the “Request to get credits failed”) but otherwise we’ve got our listing of cast members that we can iterate over now. Let’s put the bulk of getData() back together and we’ll add some code to create a new record via a link to form field or table field:

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

        document.saveAllChanges();

        let cast = getCast();
        for (let castMember of cast) {
            let actorRecord = newRecord.addNewRecordToField(cast_table_field_id);
            actorRecord.setFieldValues({
                [actor_id]: castMember.name,
                [role_id]: castMember.character,
                [tmdb_actor_id]: castMember.id
            });
            document.saveAllChanges();
        }
    }

    var Poster = "https://www.themoviedb.org/t/p/w1280/" + film.poster_path
    if (Poster != null) {
        newRecord.addPhotoFromUrlToField(Poster, poster_id);
    }
    form.saveAllChanges();
}

We’ve pulled back in the original getData() and I’ve added a block for getting the cast and inside it we’re using addNewRecordToField to get a new record (similar to form.addNewRecord) added to the table field of the record we just created. Behind the scenes this ensures that Tap Forms links the data for us properly. We use setFieldValues as was done earlier because we’re just working with a record object (albeit one that’s a record in a table). I also add in two precautionary document.saveAllChanges() because when dealing with record creation and record linking via the scripting interface there are some quirks that can appear and explicitly flushing the state down generally makes that more consistent. The document.saveAllChanges() call is an alias for form.saveAllChanges() and does the same thing.

I think this should work or at the very least get you a little closer to your journey.

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