Search Results for 'form.getRecords'

[David Gold]

I have a Script that used to work perfectly but has stopped working in recent versions of Tap Forms and can’t workout why.

The script is supposed to take a search term from clipboard, do a search, if one result is found bring up a screen displaying the “Comments” field and also copy it to the clipboard. If multiple results are found it presents them, you pick one and then it also displays the “Comments” field of the selected one and copies it to the clipboard. What has stopped this working? I’m not getting any errors in the Console and the variable references are correct.

var myForm = document.getFormNamed('Travel Database');
var records = myForm.getRecords();
var search_term = Utils.copyTextFromClipboard();
var result_count = 0;
var results = [];
var selected;

function copy_comments( comments ) {
    Utils.copyTextToClipboard( comments );
}

function multiple_results() {

    var joined = '--multiple_matches--';
    var res;
    for (res of results) {
        joined = joined +
        res.location + '::' + res.comment;
    }
    copy_comments( joined );
}

function search_records( haystack , needle ) {

    var location_id = 'fld-c55265c3f56b43feb423f5a198dffe6c';
    var comment_id = 'fld-141d923e785148e3aec84576c746a4a4';
    var rec;

    for (const rec of haystack) {
        if ( rec.getFieldValue( location_id ).toLowerCase().includes( needle.toLowerCase() ) ) {
            results.push( { location: rec.getFieldValue( location_id ) , comment: rec.getFieldValue( comment_id ) } );
            result_count++;
        }

    }

    if( result_count == 0 ){
        console.log( 'No results found!' );
    }else if( result_count > 1 ){
        multiple_results();
    }else{
        copy_comments( results[0].comment );
    }

}

search_records( records , search_term );

[cf]

I’ve been using a form for storing bookmarks but text-only makes it hard to browse the list. Here’s a script that automatically fetches the favicon for each URL in a form. Obviously you would need to update with your own ids.

// https://stackoverflow.com/a/54947757/952123
const getHostname = (url) => {
  // run against regex
  const matches = url.match(/^https?\:\/\/([^\/?#]+)(?:[\/?#]|$)/i);
  // extract hostname (will be null if no match is found)
  return matches && matches[1];
}

function favIconURL(domain) {
	return https://www.google.com/s2/favicons?domain=${domain};
}

function getFavIconURL(url) {
	return favIconURL(getHostname(url));
}

function getFavicon(r) {
	const url = r.getFieldValue('fld-026ad10a88d74569a6d37b19aa7b77a6');
	console.log(getFavIconURL(url));
	r.addPhotoFromUrlToField(getFavIconURL(url), 'fld-42eeb21b65464cb39aa966772620acba', { "filename": "favicon", "prevent_duplicates": true})	
}

function getIfEmpty(r) {
	const thumbnail = r.getFieldValue('fld-42eeb21b65464cb39aa966772620acba');
	if (thumbnail.length) return;
	return getFavicon(r);
}

form.getRecords().forEach(getIfEmpty);
form.saveAllChanges();

[T.L. Ford]

It can be done like this (for smaller datasets):
Copy “target” records into their own table and link to that.
A simple “rebuild records” script can keep the copied-form data in sync with the real form data.
Hide things you don’t want to see.

See attached Tap Forms database. The copy script (at the end of this post) uses a search to limit exercise_muscles to target.

Random trivia: NoSQL tables don’t need IDs (if you are using traditional numbers for ids (tough to accept going from SQL-brain to NoSQL-brain). Links between objects are maintained for you.

Question for experienced Tap Form scripters:
What’s the correct way to copy links? This works, but feels cumbersome and incorrect.

// only going to be one match, if any
var possible_records = records[index].getLinkedRecordsForField(em_exercise_id);
if (possible_records) {
	var possible_record = records[index].getFieldValue(em_exercise_id);
	if (possible_record) {
		var possible_rec_id = possible_record.getId();
		var possible_record = frm_exercise.getRecordWithId(possible_rec_id);
		rec.addRecordToField(possible_record, exercise_id);
	}
}

Notes for Brendan,
TFFormEntry object needs documentation.
Typo at:
record.recordExistsInField(field_id’); // example is missing the record object parameter, and ‘
record.getNoteFieldValue(field_id’); // I think I reported this one already.

Chance of support for directly copying linked records? i.e.
rec.setFieldValue(muscles_id, records[index].getLinkedRecordsForField(em_muscles_id));

——-
Copy Code:

function rebuild_data() {

	// delete old records
	var records = form.getRecords();
	for (var index = 0, count = records.length; index < count; index++){
		form.deleteRecord(records[index]);
	}
	document.saveAllChanges();

	// this form's ids
	var role_id = 'fld-e9c606f133124d6891c9a04645221833';
	var exercise_id = 'fld-22527485052d4c0f98aa91bff884f75a';
	var muscles_id = 'fld-5d856f1c8bfb4010843755a98f556f5a';

	// exercise_muscle's ids
	var em_role_id = 'fld-c2d53eebfed9446dbc57a11919c8b85d';
	var em_exercise_id = 'fld-99daf5cc157f4f5d9b51ed3496f43e28';
	var em_muscles_id = 'fld-a982502aca7649dcb1c9e47f812762d7';

	var frm = document.getFormNamed('exercise_muscle');
	var src = frm.getSearchNamed('target');
	records = src.getRecords();

	var frm_exercise = document.getFormNamed('exercise');
	var frm_muscles = document.getFormNamed('muscles');

	for (index = 0, count = records.length; index < count; index++){
		var rec = form.addNewRecord();
		rec.setFieldValue(role_id, records[index].getFieldValue(em_role_id));

		// only going to be one match, if any
		var possible_records = records[index].getLinkedRecordsForField(em_exercise_id);
		if (possible_records) {
			var possible_record = records[index].getFieldValue(em_exercise_id);
			if (possible_record) {
				var possible_rec_id = possible_record.getId();
				var possible_record = frm_exercise.getRecordWithId(possible_rec_id);
				rec.addRecordToField(possible_record, exercise_id);
			}
		}

		var possible_records = records[index].getLinkedRecordsForField(em_muscles_id);
		if (possible_records) {
			var possible_record = records[index].getFieldValue(em_muscles_id);
			if (possible_record) {
				var possible_rec_id = possible_record.getId();
				var possible_record = frm_muscles.getRecordWithId(possible_rec_id);
				rec.addRecordToField(possible_record, muscles_id);
			}
		}

	}
	document.saveAllChanges();

}

rebuild_data();

[Sam Moffatt]

With the link you can absolutely fill it in from the Magazines form, that makes complete sense because when you add from the Magazines form, it autolinks the Magazine to the article for you similar to how the Table field operates.

The Articles form is just a more accessible version of the table field you created, I think under the hood Brendan modelled them the same way, it’s just that you don’t see the form as the field itself is the form. You could hide the Articles form if you wanted so you’d not see it in the forms list but would still be able to interact with it. I personally like having them all available but the option is there.

We can automate a lot of things using the calculation fields to pull data from the parent record. Tap Forms can’t give you a table view that has fields from either two different forms or fields from the parent form and rows in a table field. That’s in part where that Articles form comes in because we can reference that from other forms (Keywords in this case) and since it is linked to the Magazine form, we can use calculation fields in the Articles form to automatically replicate values from the parent Magazine form. The other advantage is that Tap Forms’ Link to Form JOIN field works on the form making it a little easier.

If you only want to link to the magazine plus magazine month and don’t need the article name or page details (essentially anything in your articles table or the articles form) in your keyword form, then you could change the Articles form (and link) back into just a simple table. I might have misunderstood along the way but I thought it was useful to see the article and page details for the keyword as well. That’s what a lot of this is predicated on is pulling all of those fields together into the keyword to make a table.

Looking at your second screenshot, if the Magazines link isn’t useful then you can safely remove it and for the articles link in your second screenshot if you click on the X button on the far right you can hide fields and reorder them (this also works in the multicolumn list view as well). Getting magazine month and number should be a calculation field that populates the value from the parent magazine form, this is just a matter of setting the fields up and making sure they have the right type (date for the month field and number for the number field [number should be the default]). If the calculation field is misbehaving, jump to the Articles form and click on the refresh icon on the bottom of the list view and it should recalculate the fields to update to the right value.

The challenge with automatically creating a keyword if one doesn’t exist is that we need to scan the keywords form to find what keywords exist and do an insert if one doesn’t exist. If I was using something like MySQL, it has functionality that is optimised to make this mostly cheap through it’s indexing system but Tap Forms isn’t built with a columnar index structure but is a document store. That means we need to build the index ourselves. Doing this each time you edit the keywords field is probably a little intensive and would slow Tap Forms down as your database scaled in keywords. I’m also going to make an assumption that you’re going to hit a threshold where adding a new keyword is a rare event rather than a common one, as it will be today. Given both of those, I went to create a form script that handles it. You could also modify it to be a script field (might even work as is) but again that would be a little disruptive to editing that might not be appreciated.

Here is a quick little form script that runs on the currently selected magazine record, scans the linked articles and checks the keyword table to see if it exists and creates a new keyword record if it doesn’t exist. It’s actually a slightly modified version of the earlier script, just without the link creation steps:

function Update_Keywords() {
	// This is the other form that has our keywords in it.
	let keywordForm = document.getFormNamed('Keywords');
	
	// This is the field ID in the "keywords" form for the link to field to Magazines.
	let magazines_id = 'fld-b4724a28d4094b3faa9910f18374588f';

	// This is the field ID in the "keywords" form for the keyword field.
	let keywords_keyword_id = 'fld-ebaf7ffa3d484834bdcc34d3ffb9c5f2';
	
	// This is the field ID in the "magazine" form for the keyword field in the articles table.
	let keyword_id = 'fld-a75febca3ee54d2d9d77b8c176ac08db';

	// Articles link field ID
	let articles_link_id = 'fld-9af3b672710949d8a96591e23ba5466b';

	// This is to store a map of keyword to keyword record.
	let kwRecords = {};

	// Build the map of keyword to keyword record (cache if you will).
	console.log("Finding keyword records...");
	for (let keywordRecord of keywordForm.getRecords())
	{
		console.log("Adding keyword: " + keywordRecord.getFieldValue(keywords_keyword_id));
		kwRecords[keywordRecord.getFieldValue(keywords_keyword_id)] = keywordRecord;
	}
	console.log("Completed finding keyword records.");

	// Iterate over every record in this form...
	for (let sourceRecord of record.getFieldValue(articles_link_id))
	{
		// Get the value of the keyword field...
		let keyword = sourceRecord.getFieldValue(keyword_id);
			
		// Skip it if it is empty...
		if (!keyword)
		{
			continue;
		}

		// Check if a keyword record exists for it...
		if (!kwRecords[keyword])
		{
			// Create keyword record if it doesn't exist...
			console.log('Creating keyword record');
			kwRecords[keyword] = keywordForm.addNewRecord();
			kwRecords[keyword].setFieldValue(keywords_keyword_id, keyword);
		}	
		
		// Log the keyword we processed, we're done!
		console.log(keyword);
	}
	
	// Save the changes.
	form.saveAllChanges();
}

Update_Keywords();

[Sam Moffatt]

If you only had one keyword, then I think a JOIN link to field type would work for you because you could have a single “keyword” record and then it’d automatically JOIN on other records. If you made “articles” it’s own form and used “Link to Form” fields to join it all together then that would work. You’ve gone with a table approach which can’t be used to handle the JOIN.

If you wanted to keep the table, what could work is to leverage a script to maintain the links. I did a really quick one here;

function Build_Keyword_Map() {
	// This is the other form that has our keywords in it.
	let keywordForm = document.getFormNamed('Keywords');
	
	// This is the field ID in the "keywords" form for the link to field to Magazines.
	let magazines_id = 'fld-b4724a28d4094b3faa9910f18374588f';

	// This is the field ID in the "keywords" form for the keyword field.
	let keywords_keyword_id = 'fld-ebaf7ffa3d484834bdcc34d3ffb9c5f2';
	
	// This is the field ID in the "magazine" form of the table.
	let articles_id = 'fld-dca513dfa3894e8aaa3792a9eb09b106';
	
	// This is the field ID in the "magazine" form for the keyword field in the articles table.
	let keyword_id = 'fld-055da0f29f764ea399e6f317427ff8ed';

	// This is to store a map of keyword to keyword record.
	let kwRecords = {};

	// Build the map of keyword to keyword record (cache if you will).
	for (let keywordRecord of keywordForm.getRecords())
	{
		kwRecords[keywordRecord.getFieldValue(keywords_keyword_id)] = keywordRecord;
	}

	// Iterate over every record in this form...
	for (let sourceRecord of form.getRecords())
	{
		// And iterate over each row in the articles table...
		for (let article of sourceRecord.getFieldValue(articles_id))
		{
			// Get the value of the keyword field...
			let keyword = article.getFieldValue(keyword_id);
			
			// Skip it if it is empty...
			if (!keyword)
			{
				continue;
			}

			// Check if a keyword record exists for it...
			if (!kwRecords[keyword])
			{
				// Create keyword record if it doesn't exist...
				console.log('Creating keyword record');
				kwRecords[keyword] = keywordForm.addNewRecord();
				kwRecords[keyword].setFieldValue(keywords_keyword_id, keyword);
			}
			
			// Link the keyword record to this record.
			kwRecords[keyword].addRecordToField(sourceRecord, magazines_id);
			
			// Save the changes to make sure the link persists properly...
			form.saveAllChanges();
			
			// Log the keyword we processed, we're done!
			console.log(keyword);
		}
	}
}

Build_Keyword_Map();

There are comments throughout which should explain what it does and I’ve attached a sample archive with the script in it inside the “Magazine” form. You can play with it to get it to add links. What it doesn’t do right now is handle if you delete or remove a keyword. You’d also have to do a split or similar to do a comma separated list of keywords as well but this is a quick example of how you can use scripting to build that link.

If you change from a table to a child form then you could link a single keyword easily. If you wanted to do multiple keywords (e.g. Big Sur, Photos) then you’d be back needing a script again.

Hopefully this helps :)

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

The Javascript API gives you the ability to iterate over all records in a form (form.getRecords()), you can use a search term (form.getRecordsForSearchTerm('term')) and you can also use a saved search (with the saved search selected, search.getRecords()).

If you know what your criteria is and you can programmatically validate them then you can use a form script to update the values. I think I’d aim to have a saved search because you can review the target records and then use search.getRecords() to set the value.

Something like this should work, you’d need to replace the field ID with your own:

search.getRecords().forEach(rec => rec.setFieldValue('fld-changeme', 1337))

If you put that into a form script (delete all of the boilerplate), update the ‘fld-changeme’ to match the field ID (if you double click on the left it should insert a place holder you can get it from) and then change 1337 to the number you want it to be then it should bulk fill the field for you. I’ve typed this a little off the cuff but it should work.

With a little bit more work you could probably set it up with a prompter to prompt for the value but that should work to begin with.

[Rocky Machado]

Hi Brendan – Thanks, I am able to use them. I just wasn’t sure if they were being deprecated in the future. I have a question relating to form.getRecordsForSearchTerm(). Not sure if I am understanding the function correctly. In my Trades form, I have created a trade Id that is used to identifies a cycle of a trade (buy and sell). I noticed that when I use the entire key, I get no record results. Here is a sample of the Trade Id TSLA-2020-06-01-Butterfly:503 . However, If I use parts of the Trade Id I get results. (i.e Butterfly:503, TSLA or 2020-06-01). Is this the correct behavior of the function?

thanks,
rocky

[Brendan]

Hi Rocky,

No, those haven’t been deprecated. In fact, I recently added the form.getRecordsForSearchTerm() function. The issue is I forgot to add those as keywords to the keywords file that tells the editor what functions to colour.

For recalculating the form, just click the refresh button at the bottom of the record. Or at the bottom of the records list view.

[Rocky Machado]

Hi Brendan – I have a couple of questions. Have the following methods been deprecated. form.getRecordsForSearchTerm() and record.setRecordColor() It doesn’t show up in the IntelliSense. Also, in the future will you being adding a feature to recalculate a form via the javascript API?

Oh, I’m running version 5.3.11 (Build 962)

thanks,
rocky

[Victor Warner]

Sam,

Thank for the further reply,

I eventually worked out how to use

search.getREcords()

, by replacing:

for (let rec of form.getRecords()){

with

for (let rec of search.getRecords()){

Just one follow-up question: Does the it only work with an advanced search and not an ordinary search (Command + F)? I have tried with an ordinary search but the script does not operate.

[Victor Warner]

I am getting the following error:

02/05/2020, 18:24:01 / Time spent / add 0s to Time charged for (not empty)
add 0s to Time charged for (not empty): TypeError: value.replace is not a function. (In ‘value.replace(find_text, replace_with)’, ‘value.replace’ is undefined), line:(null)

when running the following script

var time_charged_for_id = 'fld-755fd264b59b42e59c7254edf03ea281';

function findAndReplace(find_text, replace_with) {
	for (let rec of form.getRecords()){
		let value = rec.getFieldValue(time_charged_for_id);
		if (value) {	
			rec.setFieldValue(time_charged_for_id, value.replace(find_text, replace_with));
		}
	}
	form.saveAllChanges();
	return;
}

findAndReplace(/[a-zA-Z0-9]*/, '0');

I created a test database and it ran and did what is should. But in a working database it is causing the error. The field type is the same (a number field) in the test database and the working database.

The code was provided by Daniel Leu in the exchanges at https://www.tapforms.com/forums/topic/find-and-replace-script-to-replace-anything/.

Because I do not really have any understanding of JavaScript I cannot tell why it is not working.

I would grateful for any help in identifying what is wrong.

[Daniel Leu]

Everything is possible with a script, well almost ;-)

Here is the version that doesn the replacement of strings in a field:

var field_id = 'fld-64b0b17a635f49b8b40867e45f8d24db';

function findAndReplace(find_text, replace_with) {
	for (let rec of form.getRecords()){
		let value = rec.getFieldValue(field_id);
		if (value) {	
			rec.setFieldValue(field_id, value.replace(find_text, replace_with));
		}
	}
	form.saveAllChanges();
	return;
}

findAndReplace("Ford", 'Tesla');

And this version replaces an empty field with the replacement text:

var field_id = 'fld-34fb289f2b124f26982df66430b31fbd';

function findAndReplace(find_text, replace_with) {
	for (let rec of form.getRecords()){
		let value = rec.getFieldValue(field_id);
		if (find_text === ""){	// special case with empty string
			if (value == undefined){
				rec.setFieldValue(field_id, replace_with);
			}
		} else {
			if (value){
				rec.setFieldValue(field_id, value.replace(find_text, replace_with));
			}
		}
	}
	form.saveAllChanges();
	return;
}

findAndReplace("Ford", 'Tesla');

Enjoy!

[Victor Warner]

I found the find and replace script posted by Brendan at https://www.tapforms.com/forums/topic/find-and-replace-script/ very helpful, but would like some help in adapting it:

var field_id = 'fld-64b0b17a635f49b8b40867e45f8d24db';

function findAndReplace(find_text, replace_with) {
	
	var records = form.getRecords();
	
	for (var index = 0, count = records.length; index < count; index++){
		var rec = records[index];
		var value = rec.getFieldValue(field_id);
		if (value == find_text) {
			value = replace_with;
			rec.setFieldValue(field_id, value);
		}
	}

	document.saveAllChanges();
	
	return;
}

findAndReplace('Ford', 'Tesla');

specifically:

1. at present it searches for a specific item in a specific field. How is it possible to search for anything in that field and replace with specific text (so rather than ‘Ford’ in Brendan’s example, there might be Ford, Mercedes or BMW etc). I have tried adding a wildcard indicator (*), but it does not work.

2. A variation on 1. is where there is nothing in the specific field. Changing ‘Ford’ in Brendan’s example, to ” also does not work.

Is this possible with the script?

Any help would be gratefully received.

[Sam Moffatt]

If you’re doing an import flow you might want to add a field with a flag on it to control if you already did the search/replace on the record already. Depending on what your search/replace looks for, running it more than once might be problematic for you.

Another trick you might be interested in is creating a saved search using “date created” to specify when you import the records to ensure that you only select and modify the records that were recently created. A slight change to Brendan’s script is to change:

var records = form.getRecords();

To something like:

var records = search.getRecords();

You’ll need to be in the search for the search object to exist but then it’ll only apply the script to the currently searched records.

[Brendan]

The Basic Loop Snippet will get you part of the way. But here’s a more comprehensive example:

var field_id = 'fld-64b0b17a635f49b8b40867e45f8d24db';

function findAndReplace(find_text, replace_with) {
	
	var records = form.getRecords();
	
	for (var index = 0, count = records.length; index < count; index++){
		var rec = records[index];
		var value = rec.getFieldValue(field_id);
		if (value == find_text) {
			value = replace_with;
			rec.setFieldValue(field_id, value);
		}
	}

	document.saveAllChanges();
	
	return;
}

findAndReplace('Ford', 'Tesla');