Search Results for 'form.getRecords'

[Sam Moffatt]

I’d also to check that there is a file attachment and skip ones that don’t:

		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			// this assumes that there is only one PDF per recipe!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;

to:

		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			if (!contract.getFieldValue(pdf_id) || contract.getFieldValue(pdf_id).length == 0) {
				console.log('Record is missing PDF: ' + contract.getUrl());
				continue;
			}
			// this assumes that there is only one PDF per recipe!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;

That should check there is an attachment and validate it’s not set plus log a message with a clickable link in the console. If that ends up being everything then that’s not great but it wouldn’t bomb part way through.

There are 35,747 entries (PDF’s), so it is only one field, but there are many entries.

Many records or a single record with multiple PDFs?

Re feedback: The last few videos hit half an hour and I need to work on getting the content back in the 10 minute range. I ended up throwing a bunch of stuff in one of the videos which when I went back should have probably ended up broken out into a few videos (ended up putting in chapter links for at least one of them). I’m trying to keep them as continuous takes to make sure I don’t miss any steps along the way though sometimes that leads to mild confusion as it’s also not scripted/practiced. Sometimes seeing something small and seemingly unimportant is the hook you need to understand. Thanks for the feedback!

[Daniel Leu]

In hindsight, it would have been better if you had described your problem as is and not made up this hypothetical recipe case….

As you might have noticed, all CSV form related fields use the a csv prefix to make it clear where this field resided. And yes, my script is a from script that is part of the contracts form. My assumption was that the CSV form is deleted after use and recreated with a new tagging set. So it makes sense that the script has a more permanent location.

Here is the updated script. Hope this works.

function addKeyword(rec, kw_id, kw){
	var keywords = rec.getFieldValue(kw_id);
	
	if (keywords == undefined || keywords == ""){
		rec.setFieldValue(kw_id, kw);
	} else {
		rec.setFieldValue(kw_id, keywords + ',' + kw);
	}
}

function Update_Records() {

	var csvForm = document.getFormNamed("csv");
	var contractsForm = document.getFormNamed("Contracts");
	
	// recipes
	var keyword_id = 'fld-a2126cbe512646e9ba144fb5a90b06dc';
	var pdf_id = 'fld-3ea77ccc18604174b722ece105965c44';

	// get csv field Ids
//	var csvPdf_id = csvForm.getFieldNamed('PDF').getId();
//	var csvKeyword_id = cvsForm.getFieldNamed('Keyword').getId();

	// get use fixed field Ids!!!!!!!!!!!!
	var csvPdf_id = 'fld-0cbd22a473cd4c58aa1dc47341a7157b';
	var csvKeyword_id = 'fld-53de1c4c633a4da6a4d0213158b5ef0a';

	// Loop over all csv entries
	for (entry of cvsForm.getRecords()){
		// get pdf file name of CVS record
		var cvsPdfName = entry.getFieldValue(csvPdf_id);

		// replace spaces with underscores
		cvsPdfName = cvsPdfName.replace('/ /g', '_');
		console.log("Processing: " + cvsPdfName);
		
		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			// this assumes that there is only one PDF per recipe!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;
			
			if (cvsPdfName == pdfName){
				console.log("Found match: " + pdfName);
				
				// Update contract record
				addKeyword(contract, keyword_id, entry.getFieldValue(csvKeyword_id));
			
				break;
			}
		
		}
		document.saveAllChanges();
	
	}
	
	return 'done';
}

Update_Records();

[Sam Moffatt]

A few more questions if I may. I have tried to figure things out by watching some of Sam’s videos etc., but I am a little stuck.

Curious for feedback, were any of them helpful? What was not helpful and what would improve it? What was helpful and how was it helpful? I had thought about doing another simple intro one on the next few snippets which includes functions but haven’t done it. I tried another video on some other topics but wasn’t happy with how it turned out.

So first, from my reading it looks like I need to use a “Form Level Script” as opposed to a “Field Level Script” as I am updating existing records.

I think my rule of thumb is that field scripts are meant to act on the contents of the record as those contents change whilst form scripts are more one off or bulk action changes. You can use them somewhat interchangeably and the question is do you want this thing to run all of the time or is it something you want to control when it runs?

I only have two forms, my main database and the cvs data I want to input. Is it correct that I must place the Form Level Script in my main database and not in the cvs form? (That seems to be the only way I can get the correct fld-hash numbers that I need into the script).

While it might not be intuitive you can access any form and any field from scripts (fields or forms) in any form within a document. The editor won’t show you the fields that aren’t directly linked to the form that you’re in but you can use the editor to go to other forms and get their field ID’s. You can also get the field ID from below the description box in the field editor UI.

You can use the code button to wrap your code with backticks (e.g. `code here`) to make it a little easier to read (also ran it through a formatter since the post didn’t have any indentation):

function addKeyword(rec, kw_id, kw) {

	var keywords = rec.getFieldValue(kw_id);

	if (keywords == undefined || keywords == "") {
		rec.setFieldValue(kw_id, kw);
	} else {
		rec.setFieldValue(kw_id, keywords + ',' + kw);
	}
}

function Update_Records() {

	var csvForm = document.getFormNamed("csv");
	var ContractsForm = document.getFormNamed("Contracts");

	// Contracts
	var pdffilepath_7_id = 'fld-3ea77ccc18604174b722ece105965c44';
	var license_category_id = 'fld-d63ab1856e554efebe62b9f1826c388d';
	var license_subcategory_id = 'fld-f0c47c7e6ad244fe955e2c24cb448590';
	var agreement_keywords_id = 'fld-a2126cbe512646e9ba144fb5a90b06dc';

	// get csv field Ids
	var pdffilepath_7 = cvsForm.getFieldNamed('PDF').getId();
	var license_category = cvsForm.getFieldNamed('Category').getId(); var license_subcategory = cvsForm.getFieldNamed('Subcategory').getId();
	var agreement_keywords = cvsForm.getFieldNamed('Keyword').getId();

	// Loop over all csv entries
	// So this tells my Contracts database to look at the cvs form?
	for (entry of cvsForm.getRecords()) {

		// get pdf file name
		// is it looking for another fld-hash here?
		var pdfName = entry.getFieldValue(csvPdf_id);

		// replace spaces with underscores
		pdfName = pdfName.replace('/ /g', '_');
		console.log("Processing: " + pdfName);

		// Loop over all LICENSEdb records
		for (Contracts of Contracts.getRecords()) {

			// this assumes that there is only one PDF per Contract
			// where have I defined (pdf_id) before? Do I need to ?
			var filename = Contract.getFieldValue(pdf_id)[0].filename;

			if (pdfName == filename) {
				console.log("Found match: " + filename);

				// Update Contracts record
				addsubcategory(Contracts, subcategory_id, entry.getFieldValue(csvKeyword_id));

				// Update Contracts record
				addKeyword(Contracts, keyword_id, entry.getFieldValue(csvSubcategory_id));

				// Set category
				Contracts.setFieldValue(category_id, entry.getFieldValue(csvCategory_id))

				break;
			}
		}
	}
	document.saveAllChanges();

	return 'done';
}

Update_Records();

First thing I noticed when I put it through a formatter was that there seemed to be a missing bracket, I’ve added an extra one before the document.saveAllChanges() line and everything balanced out. I used Visual Studio Code to reformat it but I also found an online JavaScript beautifier as well.

Next, much of the script makes sense to me, and other parts seem somewhat circular. Could you look what I have done and see where I messed up? …because I think I did a good job on that : )

I need the script to look up each record that aligns with the PDF file name in the cvs file, and then update the records in the main database form called “Contracts”

Now I see you added an extra call:

addsubcategory (Contracts, subcategory_id, entry.getFieldValue(csvKeyword_id));

What this change is doing is trying to call a function here called addsubcategory but that function doesn’t exist. I think you’re trying to copy the addKeyword line for a new field and if you look at the very top of the script there is a function addKeyword which defines or creates the function. If what you’re trying to do is create a comma separated list of sub categories but in a different field, then I don’t think you need to do a new function, I think you can just call addKeyword with the sub category field ID:

addKeyword (Contracts, subcategory_id, entry.getFieldValue(csvKeyword_id));

Try fixing the curly braces and changing addsubcategory over to addkeyword as above and see if that gets you a little closer.

[Brent S]

Hi Daniel,

A few more questions if I may. I have tried to figure things out by watching some of Sam’s videos etc., but I am a little stuck.

So first, from my reading it looks like I need to use a “Form Level Script” as opposed to a “Field Level Script” as I am updating existing records.

I only have two forms, my main database and the cvs data I want to input. Is it correct that I must place the Form Level Script in my main database and not in the cvs form? (That seems to be the only way I can get the correct fld-hash numbers that I need into the script).

Next, much of the script makes sense to me, and other parts seem somewhat circular. Could you look what I have done and see where I messed up? …because I think I did a good job on that : )

I need the script to look up each record that aligns with the PDF file name in the cvs file, and then update the records in the main database form called “Contracts”

I have attached a few screen shots in case that helps with context.

Thanks again.
Brent

——————-

function addKeyword(rec, kw_id, kw) {

var keywords = rec.getFieldValue(kw_id);

if (keywords == undefined || keywords == “”){
rec.setFieldValue(kw_id, kw);
} else {
rec.setFieldValue(kw_id, keywords + ‘,’ + kw);
}
}

function Update_Records() {

var csvForm = document.getFormNamed(“csv”);
var ContractsForm = document.getFormNamed(“Contracts”);

// Contracts
var pdffilepath_7_id = ‘fld-3ea77ccc18604174b722ece105965c44’;
var license_category_id = ‘fld-d63ab1856e554efebe62b9f1826c388d’;
var license_subcategory_id = ‘fld-f0c47c7e6ad244fe955e2c24cb448590’;
var agreement_keywords_id = ‘fld-a2126cbe512646e9ba144fb5a90b06dc’;

// get csv field Ids
var pdffilepath_7 = cvsForm.getFieldNamed(‘PDF’).getId();
var license_category = cvsForm.getFieldNamed(‘Category’).getId(); var license_subcategory = cvsForm.getFieldNamed(‘Subcategory’).getId();
var agreement_keywords = cvsForm.getFieldNamed(‘Keyword’).getId();

// Loop over all csv entries
// So this tells my Contracts database to look at the cvs form?
for (entry of cvsForm.getRecords()){

// get pdf file name
// is it looking for another fld-hash here?
var pdfName = entry.getFieldValue(csvPdf_id);

// replace spaces with underscores
pdfName = pdfName.replace(‘/ /g’, ‘_’);
console.log(“Processing: ” + pdfName);

// Loop over all LICENSEdb records
for (Contracts of Contracts.getRecords()){

// this assumes that there is only one PDF per Contract
// where have I defined (pdf_id) before? Do I need to ?
var filename = Contract.getFieldValue(pdf_id)[0].filename;

if (pdfName == filename){
console.log(“Found match: ” + filename);

// Update Contracts record
addsubcategory (Contracts, subcategory_id, entry.getFieldValue(csvKeyword_id));

// Update Contracts record
addKeyword(Contracts, keyword_id, entry.getFieldValue(csvSubcategory_id));

// Set category
Contracts .setFieldValue(category_id, entry.getFieldValue(csvCategory_id))

break;
}

}
document.saveAllChanges();

return ‘done’;
}

Update_Records();

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

You can import a csv file into a separate form and then use a script to process all records and update your reference records accordingly. Then you can assign values of meal choices accordingly and update your keywords field as well.

But this seems to be a rather tedious job if you want to update all 50k food recipes in your Tap Forms database this way. I would start with looking into software that converts your PDFs into text files. Then you can add the content of your text files into fields inside Tap Forms. These fields are searchable, which makes it much easier to categorize your recipes. But yeah, it might be a challenge to convert your PDFs, specially if they were scanned and the quality is poor.

But if you want to go the CSV route, I would create a spreadsheet with columns for PDF, Category, and Keyword. Here is my example:

function addKeyword(rec, kw_id, kw){
	var keywords = rec.getFieldValue(kw_id);
	
	if (keywords == undefined || keywords == ""){
		rec.setFieldValue(kw_id, kw);
	} else {
		rec.setFieldValue(kw_id, keywords + ',' + kw);
	}
}

function Update_Records() {

	var cvsForm = document.getFormNamed("csv");
	var recipesForm = document.getFormNamed("Recipes");
	
	// recipes
	var category_id = 'fld-9828d73d445a4746a8dba4c0dac89716';
	var keyword_id = 'fld-073ee12d29174c2eb64f7cf91f5bb383';
	var pdf_id = 'fld-e870153bd0a647c9823ca998c213d1fd';

	
	// get csv field Ids
	var csvPdf_id = cvsForm.getFieldNamed('PDF').getId();
	var csvCategory_id = cvsForm.getFieldNamed('Category').getId();	var csvKeyword_id = cvsForm.getFieldNamed('Keyword').getId();	
	// Loop over all csv entries
	for (entry of cvsForm.getRecords()){
		// get pdf file name
		var pdfName = entry.getFieldValue(csvPdf_id);
		// replace spaces with underscores
		pdfName = pdfName.replace('/ /g', '_');
		console.log("Processing: " + pdfName);
		
		// Loop over all recipes records
		for (recipe of recipesForm.getRecords()){
			// this assumes that there is only one PDF per recipe!
			var filename = recipe.getFieldValue(pdf_id)[0].filename;
			
			if (pdfName == filename){
				console.log("Found match: " + filename);
				
				// Update recipe record
				addKeyword(recipe, keyword_id, entry.getFieldValue(csvKeyword_id));
				
				// Set category
				recipe.setFieldValue(category_id, entry.getFieldValue(csvCategory_id))
			
				break;
			}
		
		}
		document.saveAllChanges();
	
	}
	
	return 'done';
}

Update_Records();

The first row of my spreadsheet are the field labels: PDF, Category, Keyword. They are used to create the field names when creating a new form called csv upon importing your csv. The script is part of your Recipes form.

This script assumes that the keywords are comma separated. Keywords are added to your record if new ones are found. The category field is overwritten!

In order for the script to work, you need to update category_id, keyword_id, and pdf_id according to your recipes form.

Hope this helps! Happy cooking!

[Brecht DHeere]

I think I found it. There maybe other (simpler) approaches but it works.

….
async function myFunction() {
try {
await confirmExecution(‘Record verwijderen?’);
} catch (errorText) {
// user clicked ‘No’
console.log(‘cancelled’);
return;
}
// user clicked ‘yes’
// continue with function
// main code here

var records = form.getRecords();
var field_id = ‘fld-7a3920ab535745e8a78213b49a8b13d9’;
var naam = record.getFieldValue(field_id);

for (var index = 0, count = records.length; index < count; index++){
var aRecord = records[index];
var field_value = aRecord.getFieldValue(field_id);
if (field_value == naam) {
form.deleteRecord(aRecord);
}
}
form.saveAllChanges();
}
myFunction();

[Sam Moffatt]

Oh that’s an annoyance of the forum, it turns the backtick character into <code> and </code> respectively even though it’s inside a script context already. In Javascript the backticks are used to create template literals which allow you to embed variables inside the string. Not particularly necessary in this case but something I’m in the habit of doing.

If you’re looking for the record that is problematic, you can use record.getUrl() for the record that fails the test and console.log it to find it.

Here’s my copy of the script, I took out the template string so hopefully the forum doesn’t munge it and it’s been updated to point to different fields and field names (title and subtitle from the Books form in the document I use for the YouTube videos):

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

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

function copy_result_multiple( comments ) {

    if ( comments == true ) {
        console.log( 'Index:' + results.indexOf( selected ) );
        console.log( results_comments[ results.indexOf( selected ) ] );
        copy_comments( results_comments[ results.indexOf( selected ) ] );
    } else {
        console.log( 'Cancelled' );
    }

}

function multiple_results( all_results ) {

    let prompter = Prompter.new();
    prompter.cancelButtonTitle = 'cancel';
    prompter.continueButtonTitle = 'Copy Comment';
    prompter.addParameter('Select Result ', 'selected', 'popup', all_results)
    .show('Multiple Results Found', copy_result_multiple );

}

function search_records( haystack , needle ) {
	var title_id = 'fld-59b7b2dfa5c843afb969e74df4ad111c';
	var subtitle_id = 'fld-b7d482d0db5d4554ad0948f19394f441';
    var rec;

    for (rec of haystack) {
    	var title = rec.getFieldValue( title_id );
    	
    	if (title && title.includes( needle ) ) {
            results.push( rec.getFieldValue( title_id ) );
            results_comments.push( rec.getFieldValue( subtitle_id ) );
            result_count++;
        } else {
        	if (!title) {
        		console.log("Empty field: " + rec.getUrl());
        	}
        }

    }

    if( result_count == 0 ){
        Utils.alertWithMessage("No Results", "No results were found for the search term: " + needle);
    }else if( result_count > 1 ){
        //multiple results
        multiple_results( results );
    }else{
        //single result
        copy_comments( results[0] );
    }

}

search_records( records , search_term );

[David Gold]

I forgot to say I have separate Scripts for the iOS version and the Mac version of Tap Forms given some iOS limitations. The one above is the iOS version and below is the Mac version.

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

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

function copy_result_multiple( comments ) {

    if ( comments == true ) {
        console.log( 'Index:' + results.indexOf( selected ) );
        console.log( results_comments[ results.indexOf( selected ) ] );
        copy_comments( results_comments[ results.indexOf( selected ) ] );
    } else {
        console.log( 'Cancelled' );
    }

}

function multiple_results( all_results ) {

    let prompter = Prompter.new();
    prompter.cancelButtonTitle = 'cancel';
    prompter.continueButtonTitle = 'Copy Comment';
    prompter.addParameter('Select Result ', 'selected', 'popup', all_results)
    .show('Multiple Results Found', copy_result_multiple );

}

function search_records( haystack , needle ) {

    var location_id = 'fld-cf720b6ab4314f0bb5f47bc9bd61f0a9';
    var comment_id = 'fld-0096b55f012d4d4fb34a95f784951b55';
    var rec;

    for (rec of haystack) {
        if ( rec.getFieldValue( location_id ).includes( needle ) ) {
            results.push( rec.getFieldValue( location_id ) );
            results_comments.push( rec.getFieldValue( comment_id ) );
            result_count++;
        }

    }

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

}

search_records( records , search_term );

[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