Search Results for 'form.getRecords'

[Sam Moffatt]

This one is a fun one, TF doesn’t track users but devices. It has a modified by device field but that can change if someone modifies it (which makes sense). You can do a ‘created by device’ or ‘record author’ by doing the following.

First step is to add a new field of type ‘Modified by Device’. You’ll need it’s field ID from below the description field. The second step is to create a new text field to store the ‘created by device’ or maybe ‘record author’. The reason to use text field is so you can override it later, especially as your old records will be likely wrong. The third field is a script field that looks something like this:

function Set_Record_Author() {

	var record_author = record.getFieldValue('fld-471ac88b92364a228d9fec90d9a5347b');
	
	if (!record_author) {
		var deviceToAuthor = {
			"pancake": "Sam",
		};

		var device = record.getFieldValue('fld-0bbeb9cfa1024c11a624ed63cc8200e0');
		
		record.setFieldValue('fld-471ac88b92364a228d9fec90d9a5347b', deviceToAuthor[device] ? deviceToAuthor[device] : device);
		document.saveAllChanges();
	}
}

Set_Record_Author();

• fld-471ac88b92364a228d9fec90d9a5347b is the ID of the Modified By Device field
• fld-0bbeb9cfa1024c11a624ed63cc8200e0 is the ID of the Record Author field

This creates a one time copy of the modified by field to our field though if you delete the contents of the text box it will reset. It also maps the device name (in my case my laptop is called pancake because it was thinner than my older laptop) to a human name as well but falls back to device name if there isn’t a match.

function Aggregate_By_Date() {
	// set our date format, see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString
	// remove "day" from it and you'll roll up by month for example.
	var dateFormat = { "day": "2-digit", "month": "2-digit", "year": "2-digit"};
	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var marketplace = rec.getFieldValue('fld-c163aba17ae64c4d93b5a53819a139dc');
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.toLocaleDateString("en-AU", dateFormat);

		// Rollup to this date, add to the existing value or set it if not set.
		if (!rollups[formattedDate]) {
			rollups[formattedDate] = { [marketplace]: price };
		} if (!rollups[formattedDate][marketplace]) {
			rollups[formattedDate][marketplace] = price;
		} else {
			rollups[formattedDate][marketplace] += price;
		}
	}
	
	// log to console the aggregated values.
	for (var month in rollups) {
		for (var marketplace in rollups[month]) {
			console.log(month + ", " + marketplace + ": $" + rollups[month][marketplace]);
		}
	}
}

Aggregate_By_Date();

First change is to get the extra field, I used marketplace since I was testing against my products document but replace it with the field ID we just created.

Second change is to how we do the rollup to add multiple dimensions (in this case, marketplace). For each dimension we need to check if it exists and create it if it doesn’t. If both date and marketplace are set, the else handles adding values together.

The last change is to the output loop to iterate over each day and each “marketplace”.

You’re using the script that doesn’t hit the rollup form but if you want to use it, you’ll need to add a new field to it and then where there is the addNewRecord line add an extra setFieldValue after it with the new field ID:

function Aggregate_By_Date() {	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	//var marketplaces = new Set();
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var marketplace = rec.getFieldValue('fld-c163aba17ae64c4d93b5a53819a139dc');
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.getFullYear() + "-" + purchase_date.getMonth() + "-" + purchase_date.getDay();
		
		//marketplaces.add(marketplace);

		
		// Rollup to this date, add to the existing value or set it if not set.
		if (!rollups[formattedDate]) {
			rollups[formattedDate] = { [marketplace]: price };
		} if (!rollups[formattedDate][marketplace]) {
			rollups[formattedDate][marketplace] = price;
		} else {
			rollups[formattedDate][marketplace] += price;
		}
	}
	
	// Get the rollups form.
	var rollupsForm = document.getFormNamed("Rollups");

	// Delete previous roll up records.
	rollupsForm.getRecords().forEach(rollupRec => rollupsForm.deleteRecord(rollupRec));
	
	var lines = [];
	
	// log to console the aggregated values.
	for (var month in rollups) {
		for (var marketplace in rollups[month]) {
			var line = month + ","  + marketplace + "," + rollups[month][marketplace];
			console.log(line);
			lines.push(line);

			var rollupRec = rollupsForm.addNewRecord();
			rollupRec.setFieldValue("fld-cd1d454672c84bce8103a4267507ca03", month);
			rollupRec.setFieldValue("fld-4c9f208bb5ed406489a54a76d4b6cd18", marketplace);
			rollupRec.setFieldValue("fld-9eeeff7120db401b830ccec4e06f2bc3", rollups[month][marketplace]);
		}
	}
	
	document.saveAllChanges();	

	Utils.copyTextToClipboard(lines.join("\n"));
}

Aggregate_By_Date();

[Guillermo q]

Sorry to bother you again guys.

With this code you gave me:

function Aggregate_By_Date() {
	// set our date format, see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString
	// remove "day" from it and you'll roll up by month for example.
	var dateFormat = { "day": "2-digit", "month": "2-digit", "year": "2-digit"};
	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.toLocaleDateString("en-AU", dateFormat);

		// Rollup to this date, add to the existing value or set it if not set.
		rollups[formattedDate] ? rollups[formattedDate] += price : rollups[formattedDate] = price;
	}
	
	// log to console the aggregated values.
	for (var month in rollups) {
		console.log(month + ": $" + rollups[month]);
	}
}

Aggregate_By_Date();

I have a field depending on who added this field (my wife or me). I would like this report by day, but with XX or XY depending on who added the field and the price.

Thank you very much, you helped me a lot.

[Sam Moffatt]

If you’re on iOS/iPadOS, I’d just copy the values out and paste into Numbers (Apple’s spreadsheeting tool) or Excel (free for phones, I think a charge exists on iPads). If you change the console output to use commas, it should paste fine.

A small change could also directly put this on your pasteboard:

	var lines = [];
	// log to console the aggregated values.
	for (var month in rollups) {
		var line = month + "," + rollups[month]
		console.log(line);
		lines.push(line);
	}

	Utils.copyTextToClipboard(lines.join("\n"));

The Mac version of Tap Forms has some charting functionality, I’m not entirely sure if it supports aggregation or not but that would be the first place I’d look. Supporting aggregation seems like a reasonable feature ask if it doesn’t exist but that can only be answered by the Keymaster.

If you’re on the Mac, then an alternative could be to just write it to a new form and then chart it. To do this is going to be a bit more of a rewrite so that we can sort things easier

	
function Aggregate_By_Date() {	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.getFullYear() + "-" + purchase_date.getMonth() + "-" + purchase_date.getDay();

		// Rollup to this date, add to the existing value or set it if not set.
		rollups[formattedDate] ? rollups[formattedDate] += price : rollups[formattedDate] = price;
	}
	
	// Get the rollups form.
	var rollupsForm = document.getFormNamed("Rollups");

	// Delete previous roll up records.
	rollupsForm.getRecords().forEach(rollupRec => rollupsForm.deleteRecord(rollupRec));
	
	var lines = [];
	
	// log to console the aggregated values.
	for (var month in rollups) {
		var line = month + "," + rollups[month]
		console.log(line);
		lines.push(line);

		var rollupRec = rollupsForm.addNewRecord();
		rollupRec.setFieldValue("fld-cd1d454672c84bce8103a4267507ca03", month);
		rollupRec.setFieldValue("fld-9eeeff7120db401b830ccec4e06f2bc3", rollups[month]);
	}
	
	document.saveAllChanges();	

	Utils.copyTextToClipboard(lines.join("\n"));
}

Aggregate_By_Date();

Change to match the form name and field IDs but that should populate a new form with the records in the right format. Then you can use the chart feature on the Mac to visualise it.

Major changes in the full script, the formatted date is done by getting each value out:

		// format the date for use in the rollup.
		var formattedDate = purchase_date.getFullYear() + "-" + purchase_date.getMonth() + "-" + purchase_date.getDay();

This produces a format that is easier to sort on but not zero padded, doing so would require grabbing the sprintf implementation via the Script Manager so I’ve left it out for simplicity sake.

Next big change is our loop, I’ve incorporated the above pasteboard change but the core is as follows:

	// Get the rollups form.
	var rollupsForm = document.getFormNamed("Rollups");

	// Delete previous roll up records.
	rollupsForm.getRecords().forEach(rollupRec => rollupsForm.deleteRecord(rollupRec));
	
	var lines = [];
	
	// log to console the aggregated values.
	for (var month in rollups) {
		var line = month + "," + rollups[month]
		console.log(line);
		lines.push(line);

		var rollupRec = rollupsForm.addNewRecord();
		rollupRec.setFieldValue("fld-cd1d454672c84bce8103a4267507ca03", month);
		rollupRec.setFieldValue("fld-9eeeff7120db401b830ccec4e06f2bc3", rollups[month]);
	}
	
	document.saveAllChanges();	

We’re getting a form named “Rollups”, we’re going to clean it out each script run and then as we go through the rollups, we’ll add a new record and set a text field and a number field. We need to tell Tap Forms to save the changes as well which is the last line of that snippet. As with previously, replace the field ID’s with your own.

When doing this roll up, if in your main form you add a new script field (I called mine “Purchase Date Simplified”) and put this content in it:

function Purchase_Date_Simplified() {
		var purchase_date = record.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		if (!purchase_date) {
			return;
		}
		
		// format the date for use in the rollup.
		return purchase_date.getFullYear() + "-" + purchase_date.getMonth() + "-" + purchase_date.getDay();
}

Purchase_Date_Simplified();

Then in your rollup form you can create a new “Link to Form” field, link it to the original form, then you set it to the JOIN type and you can match the text field to the script field. Then you can easily go from a roll up record to see which items were in the rollup.

[Mark Robbins]

I have a need to loop through each record in a table and run a script for that record. I wrote the script below, but it does not seem to work. I’m new to Tap Forms, and a bit new to javascript, so my understanding may be at fault. I know the called script works well when I run it from the Form window for a record, I just can’t get it to run against each record.

Thanks.

function Process_All_Records() {

var records = form.getRecords();
var field_bggid = “fld-ce86e98498894969839b4d574c754511”
var value_bggid = 0

for (thisRecord in records)
{
// go to the record and run the function;
form.selectRecord(records[thisRecord]);
console.log(“BGGid: ” + records[thisRecord].getFieldValue(field_bggid));
console.log(“Index: ” + thisRecord);
form.runScriptNamed(‘Fetch_Bgg_Information’);
}
}

Process_All_Records();

[Sam Moffatt]

You can do that by sorting by that date field, enabling “show group summaries” (this will also enable section headings) and then in your form settings, set the calculation to be total and the price field. Then you’ll get a summary at the bottom of each section. If you use the multicolumn list view, it’ll give you the option to display a calculations row and control per field aggregations as well.

In terms of a script, you’ll need to loop over all of the records in the form. Something simple like this could do it:

function Aggregate_By_Date() {
	// set our date format, see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString
	// remove "day" from it and you'll roll up by month for example.
	var dateFormat = { "day": "2-digit", "month": "2-digit", "year": "2-digit"};
	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.toLocaleDateString("en-AU", dateFormat);

		// Rollup to this date, add to the existing value or set it if not set.
		rollups[formattedDate] ? rollups[formattedDate] += price : rollups[formattedDate] = price;
	}
	
	// log to console the aggregated values.
	for (var month in rollups) {
		console.log(month + ": $" + rollups[month]);
	}
}

Aggregate_By_Date();

If you want to do more with the date formatting you’d have to pull out the fields from the date object. This will also be sorted in the order you retrieve them from the form which I think will be the default form ordering, so you’ll want to make sure that your first sort field is set to the date field to get the script output in the format you need.

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

Cheers, Daniel

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

[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

[Daniel Leu]

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

Cheers, Daniel

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

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

Cheers, Daniel

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