JOIN on multiple columns

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

It should have warned you that it didn’t like the attachment extension or it might have been too large (though I thought it was reasonably generous for size). There are a few ways things can disappear, try attaching to a new post and see if you get any error messages when attaching/posting. There is also sometimes a message that shows up under your post about why the attachment was dropped as well.