Search Results for 'form.getRecords'

[Roy McKee]

Hi Sam
Thanks for the script. I have created it as you say, replacing the check-mark-id and field ref, thus:

function Script_Google_No_1_S() {

// My Code
var Google_No_1_id = ‘fld-4cbc05636709431a8305cfb7739a9bc5’;
var records = form.getRecords();
var checked = 0;
for (record of records)
{
checked += record.getFieldValue(Google_No_1_id);
}

checked / records.length * 100

Script_Google_No_1_S();

but nothing happens. Have I done something wrong?

[Sam Moffatt]

A form script like this should do it (replace check_mark_id with your field ID):

var check_mark_id = 'fld-acaa569a30294a02a26e6fb116781718';
var records = form.getRecords();
var checked = 0;
for (record of records)
{
	checked += record.getFieldValue(check_mark_id);
}

checked / records.length * 100

[Daniel Leu]

Yeah, the Prompter() is asynchronous…

Maybe there is a better way, but I would first create an array where you identify the multiple stocks. Then you can process those with the prompter loop.
Here is a small proof of concept. I just us an array that contains all records of my form. In your case, this would be the result of your filtered stock. Then I call my processing function. And as callback, I use the same processing function. So now the prompter loops over all entries of my list.

var recs = form.getRecords();
var loop_count = recs.length;

var xyzFunc = function() {
	if (loop_count == 0) {
		return "done";
	} else {
		loop_count -= 1;
	}

	let prompter = Prompter.new();
	prompter.show('Message prompt. Loop Count ' + loop_count, xyzFunc);
};

xyzFunc();

Maybe this gives you a hint how you can approach your problem. Personally, I would love if the Prompter() is blocking, but I got to use what we have available.

[Sam Moffatt]

I had a situation where I wanted to automatically download a set of files from the web and store them as Tap Forms document. In my case I have two forms: one parent form that stores the URL I want to grab data from as a base and second form that I want put new records with more details keyed for me by source URL.

It takes a few parameters to operate (the last three are optional):

• formName: The name of the source form for the records; can also be a form ID (required).
• keyFieldId: The field ID of the key field in the record (required).
• keyValue: The value to match as the key (required).
• createIfMissing: Control if a record is created if none is found (default: true).
• earlyTermination: Control if function terminates as soon as a match is found (default: true).
• alwaysScanOnMiss: Control if we should assume concurrent access (default: false)

I use this with my Script Manager idiom, so I have something like this:

document.getFormNamed('Script Manager').runScriptNamed('getLinkedRecord');
let currentRecord = getRecordFromFormWithKey('Location Tracker', source_id, entry.href, true, false);

The early termination option is interesting and how I’d advise using it is dependent upon your use case. If you set early termination, when the script is iterating through all of the records it will return the first match it finds. If you only expect to call this function once, maybe twice, then this will work fine for you because depending on your ordering you will avoid a full traversal of your recordset.

If you are running this in a loop or some other construct where you expect to call the function multiple times then disabling early termination ensures that everything is indexed on the first pass. If you expect to have many cache hits, then this will improve performance significantly as subsequent calls will immediately return based on a direct lookup. Record creation is added as a method here so that newly created records can be immediately indexed in the system avoiding future cache misses.

The worst case for this method is a cache miss. After the first scan of the records, it assumes no other concurrent access. If you don’t want this behaviour, there is another flag to always scan if a miss isn’t found and the form has already been scanned.

Here’s the script:

// ========== getRecordFromFormWithKey Start ========== //
// NAME: Get Record From From With Key
// VERSION: 1.0

/**
 * Get a record from a form with a key field, optionally creating it if missing.
 * Note: assumes your key field is unique.
 *
 * formName           The name of the source form for the records (required).
 * keyFieldId         The field ID of the key field in the record (required).
 * keyValue           The value to match as the key (required).
 * createIfMissing    Control if a record is created if none is found (default: true).
 * earlyTermination   Control if function terminates as soon as a match is found (default: true).
 * alwaysScanOnMiss   Control if we should assume concurrent access (default: false)
 */
function getRecordFromFormWithKey(formName, keyFieldId, keyValue, createIfMissing = true, earlyTermination = true, alwaysScanOnMiss = false)
{
	// Check if our basic parameters are set.
	if (!formName || !keyFieldId || !keyValue)
	{
		throw new Error("Missing required parameters");
	}
	
	// Determine the target form (check if we were given an ID or else assume a name)
	let targetForm = undefined;
	if (formName.match(/frm-/))
	{
		targetForm = document.getFormWithId(formName);
	}
	else
	{
		targetForm = document.getFormNamed(formName);
	}

	// Check if our global variable has been setup.
	if (!indexedRecordIndex)
	{
		var indexedRecordIndex = {};	
	}
	
	// Flag for if we've indexed this form already.
	if (!indexedRecordState)
	{
		var indexedRecordState = {};
	}

	// Create a key for this form-field combination.
	// Form+Field is the key.
	let indexedRecordKey = targetForm.getId() + "_" + keyFieldId;

	// Check to see if this particular link field has been setup.
	if (!indexedRecordIndex[indexedRecordKey])
	{
		indexedRecordIndex[indexedRecordKey] = {};
	}

	
	// Short circuit if we have an exact match.
	if (indexedRecordIndex[indexedRecordKey][keyValue])
	{
		return indexedRecordIndex[indexedRecordKey][keyValue];
	}
	
	// No immediate match, check to see if we should scan everything.
	// alwaysScanOnMiss forces this code path each execution.
	// The check to indexedRecordState is if this has been indexed.
	if (alwaysScanOnMiss || !indexedRecordState[indexedRecordKey])
	{	
		// Brute force search :(
		let records = targetForm.getRecords();

		// Iterate through all of the records and look for a match.
		for (currentRecord of records)
		{
			// Set up a reverse link for this value.
			let recordKeyValue = currentRecord.getFieldValue(keyFieldId);
			indexedRecordIndex[indexedRecordKey][recordKeyValue] = currentRecord;

			// If this is a match and early termination is setup, return immediately.
			if (earlyTermination && recordKeyValue == keyValue)
			{
				return currentRecord;
			}
		}
		
		// Flag this record-field as being indexed.
		indexedRecordState[indexedRecordKey] = true;
	}

	// Check to see if we got a match here and return if the key exists.
	if (indexedRecordIndex[indexedRecordKey][keyValue])
	{
		return indexedRecordIndex[indexedRecordKey][keyValue];
	}
	else if (createIfMissing)
	{
		// If createIfMissing is set, create a new record.
		// Note: it's expected the caller will call document.saveAllChanges();
		let newRecord = targetForm.addNewRecord();
		// Set the key value to our search value.
		newRecord.setFieldValue(keyFieldId, keyValue);
		// Link this up to save us another lookup in future.
		indexedRecordIndex[indexedRecordKey][keyValue] = newRecord;
		// And now we return the new record. 
		return newRecord;
	}
	
	// If we didn't find anything, return undefined.
	return undefined;
}
// ========== getRecordFromFormWithKey End ========== //

[David Gold]

I’ve edited the above but am running into issues with the final copy to clipboard. Have I made an error I’m missing somewhere:

// get form of based on form name
var search = Utils.copyTextFromClipboard();
var myForm = document.getFormNamed('Travel Database');
// get all records
var records = myForm.getRecords();
// loop over all records
var rec;
for (rec of records) {
   // get value and compare
   if (rec.getFieldValue('fld-cf720b6ab4314f0bb5f47bc9bd61f0a9') == search) {
      // if match, do something
	Utils.copyTextToClipboard(rec.getFieldValue('fld-cf720b6ab4314f0bb5f47bc9bd61f0a9'));
      break;
   }
}

[Daniel Leu]

Maybe something like this:

// get form of based on form name
var myForm = document.getFormNamed('my form');
// get all records
var records = myForm.getRecords();
// loop over all records
var rec;
for (rec of records) {
   // get value and compare
   if (rec.getFieldValue(field_id) == 'expected value') {
      // if match, do something
      console.log("success");
      break;
   }
}

Please note that I didn’t run this code…. It might make sense to put this all in a function os it can be easily reused.

[Marcus]

Ya, got it.
I already created a bunch of helpful scripts,
I only was stuck hanging to automate with a script field rather than execute it manually.

One last question:
Is there a possibility to get filtered recordsets only ?

This returns ALL records:

var records=form.getRecords();

[Brendan]

Hi Jose,

You can’t get the specific selected records if you’ve multi-selected some records. But you can loop through record in a form or a saved search and do something with those. And you can get the currently selected record (singular).

To get all the records in a form:

var records = form.getRecords();

To get all the records in the currently selected Saved Search:

var records = search.getRecords();

To get the current record just reference record.

But I suppose it could be useful to be able to get an array of the currently selected records instead of just one.

[Brendan]

form.selectRecord(someRecord) is for telling Tap Forms to select a record. It’s useful when you use JavaScript to add a record to your form.

For deleting a record, you need to get the record first before you can delete it.

If you want to delete a single record in a loop, you have to first get the records from the form. Then loop through them, check the record to see if it’s the right one to delete, then call the function to delete it.

var records = form.getRecords();
var field_id = 'fld-....';
for (var index = 0, count = records.length; index < count; index++){
    var aRecord = records[index];
    var field_value = aRecord.getFieldValue(field_id);
    if (field_value == 'some value') {
        form.deleteRecord(aRecord);
    }
}

form.saveAllChanges();

Something like the above should work but it has to be modified for your own form of course.

[Ryan M]

Thought I’d play around and see if I could come up with a different approach to the same mileage tracker. This would probably be closer to a fuel log rather than a simple mileage tracker.

The inputs would be:

– odometer value
– Price per gallon
– Gallons filled

A script would calculate your mileage since last fill up as well as your miles per gallon.

The script uses the default created date field to sort all records by their created date. This assumes that you’re entering in your mileage log around the time you filled up.

Here’s the script for those interested

var odometer_id = 'fld-9674e316bfed408ca6710ce81b72bf05';
var vehicle_id = 'fld-64b0b17a635f49b8b40867e45f8d24db';
var date_created_id = 'fld-2097149a2eeb4d4e892f13b62de6b5ea';

// sort records in ascending order by date value
var dateSorter = (a, b) => 
  a.getFieldValue(date_created_id) - b.getFieldValue(date_created_id)
  

var run = () => {
  var vehicle = record.getFieldValue(vehicle_id);
  var odometer = record.getFieldValue(odometer_id);

  var records = form.getRecords()
    .filter(r =>  r.getFieldValue(vehicle_id) === vehicle)
    .sort(odometerSorter)

  var odometerValues = records.map(r => r.getFieldValue(odometer_id));
  var currentRecordIndex = odometerValues.indexOf(odometer);
  var previousRecordIndex = currentRecordIndex === 0 ? 0 : currentRecordIndex - 1
                 
  var mileageValue = odometerValues[currentRecordIndex] - odometerValues[previousRecordIndex]

  return mileageValue;
}

run();

Attachments:

You must be logged in to view attached files.

[Andy Rawlins]

In the script editor I selected the rating field and copied the ID into your script like this:

var rating_id = 'fld-0f294ce98c3043e0a9873bfe1933276e;;
var records = form.getRecords();
for (var index = 0, count = records.length; index < count; index++){
var aRecord = records[index];
var rating = aRecord.getFieldValue(rating_id);
aRecord.setFieldValue(rating_id, rating * 2);
}
form.saveAllChanges();

I didn’t see anything happen at all. Looking at it now, I left off the single quotes, have some extra characters at the beginning and an extra semicolon at the end. All these things appear in the email version of the script but not here on the website. I guess Mail has garbled it a bit. I have some other databases to do so will try it on them :)

Many thanks

Andy

[Brendan]

Hi Andy,

You could definitely write a Form Script to do this. Or you could also use the Advanced Find & Replace function to do this. I’d make a backup of your document first whatever you do though.

With the Find & Replace method you could start with searching the Rating field for 5, then set the value to 10. Then search for 4 and set the value to 8, then search for 3 and set it to 6, then 2 setting it to 4, then 1 setting it to 2.

You have to do it in reverse as described above because if you go the other way, you’ll end up changing values you already changed.

Or here’s a Form Script you can use for it:

var rating_id = 'fld-3baff54c8e164de9b9ad3f0fbb040bc6';
var records = form.getRecords();
for (var index = 0, count = records.length; index < count; index++){
	var aRecord = records[index];
	var rating = aRecord.getFieldValue(rating_id);
	aRecord.setFieldValue(rating_id, rating * 2);
}
form.saveAllChanges();

You need to use your own rating field ID though (e.g. ‘fld-…..’). The above is specifically for my test form.

But make sure you run it only 1 time otherwise you’ll double the rating values again.

[Brendan]

Sure, you could update a record in a Field Script. An example of that might be selecting something from a Pick List that triggers a script to run and it updates a different field in that same record.

You could also have a Saved Search that fetches the records you want to update and run the script just on those records. You would use search.getRecords() instead of form.getRecords(). Or you can ask the form for a specific with a provided name. E.g. var search = form.getSearchNamed('Action & Adventure'); Then do search.getRecords();.