Search Results for 'form.getRecords'

[Brendan]

You can get the previous record with this code:

function Get_Previous_Record() {

	var records = form.getRecords();
	var currentRecordIndex = records.indexOf(record);
	var previousRecord;
	
	if (currentRecordIndex > 0) {
		previousRecord = records[currentRecordIndex - 1];
	}
	
	if (previousRecord != undefined) {
		// fetch some value and do some calculation with it and the current record
		// the currently selected record is always accessed with the built-in variable
		// record
	}
}

Get_Previous_Record();

• This reply was modified 1 year, 11 months ago by Brendan.
• This reply was modified 1 year, 11 months ago by Brendan.
• This reply was modified 1 year, 11 months ago by Brendan. Reason: Fixed a syntax error

[Daniel Leu]

To get the comparison to work, you need to get the numerical representation of the date. Additionally, the date object needs to set to the beginning of the day to properly detect a prior date.

function Delete_Expired_Docs() {

	var records = form.getRecords();
	var field_id = 'fld-xxx';
	
	// Get time in ms at beginning of today
	let now = new Date();
	now.setSeconds(0);
	now.setMinutes(0);
	now.setHours(0);
	now.setMilliseconds(0);
	const today = now.getTime();
		
for (var index = 0, count = records.length; index < count; index++){
    var aRecord = records[index];
    var field_value = aRecord.getFieldValue(field_id);

    if (field_value.getTime() < today) {
        //form.deleteRecord(aRecord);
        console.log("found record");
    } else {
    	console.log("record doesn't match");
    }
}

form.saveAllChanges();

}

Delete_Expired_Docs();

BTW, I don’t like deleting records… I would just mark a record as ‘expired’. But that depends really on your data model.

[Ian Wheeler]

Hi Folks,

I am trying to check via a script whether a Date field contains a date that is earlier than today. If it is then I want to delete the record. Ideally this would be a manually run script. I am very new to TF (and loving it, and am in the process of migrating over from Filemaker) and Javascript. Below is the script that I have which just doesn’t work. I suspect it is my Date comparison element that is the problem as the Console.Log isn’t detecting any errors, but I don’t know how to fix it. Any help would be greatly appreciated

function Delete_Expired_Docs() {

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

form.saveAllChanges();

console.log(field_value);
console.log(count);
console.log(Date());

}

Delete_Expired_Docs();

• This topic was modified 2 years ago by Brendan.
• This topic was modified 2 years ago by Brendan. Reason: formatted code

[Jake Gregory]

Thank you for your response Brendan, I decided to have a bash on my own – not much luck though lol

However I have managed to rather clumsily get it working from the code you provided.


function Update_Prices() {

var group_1_id = 'fld-0305473a4c7e41f69ee491266c99ec03';
var group_2_id = 'fld-93197576ce50421599626d9ccacff2f5';
var group_3_id = 'fld-ec93413e3880425da973d35d93b99595';
var group_4_id = 'fld-cd36a8501faf45408cd88e607e0bc4e1';

var records = form.getRecords();
var price_increase_percent = 0.12;

for (var index = 0, count = records.length; index < count; index++){
var rec = records[index];

var g1_current_price = rec.getFieldValue(group_1_id);
var g2_current_price = rec.getFieldValue(group_2_id);
var g3_current_price = rec.getFieldValue(group_3_id);
var g4_current_price = rec.getFieldValue(group_4_id);

var g1_new_price = g1_current_price + g1_current_price * price_increase_percent;
var g2_new_price = g2_current_price + g2_current_price * price_increase_percent;
var g3_new_price = g3_current_price + g3_current_price * price_increase_percent;
var g4_new_price = g4_current_price + g3_current_price * price_increase_percent;

rec.setFieldValue(group_1_id, g1_new_price);
rec.setFieldValue(group_2_id, g2_new_price);
rec.setFieldValue(group_3_id, g3_new_price);
rec.setFieldValue(group_4_id, g4_new_price);

}
form.saveAllChanges();

}

Update_Prices();


I’m sure there’s a much better way to do this, but I’ll get there.

I want to add a prompt box that asks for a user input for a percentage and then have a confirmation box ask them to confirm.

I’m currently exploring the prompt options and I’m sure I’ll have more questions later lol
Thanks again for your replies.

[Brendan]

Hi Jake,

I replied to your email about this topic, but I’ll reply here too with the code I wrote for you in case anyone else needs this type of code:

function Update_Prices() {

	var price_field_id = 'fld-.....';
	var records = form.getRecords();
	var price_increase_percent = 0.05;
	
	for (var index = 0, count = records.length; index < count; index++){
		var rec = records[index];
		var current_price = rec.getFieldValue(price_field_id);
		var new_price = current_price + current_price * price_increase_percent;
		rec.setFieldValue(price_field_id, new_price);
	}
	form.saveAllChanges();

}

Update_Prices();

[John Cranney]

It does make sense, thanks.

I was trying to get it to work using a script and I think I found some odd behaviour.

Say I have a linked form (stuff_link_id) and a table that I want to display a view of records in that table (stuff_table_id). If I click the checkmark button as you mention above, I get a copy of the selected stuff_link record in my table, with values magically populated.

However if I run the following (which I was hoping would populate the table with a view of whatever is linked in link) it deletes the record from link and moves it to table. Is that intentional? I expected it would do the same magic population. I’ve got a workaround which is to use duplicate() but I need to manually set all the columns.

var records=form.getRecords();

for (var rec of records){
for (var stuff_rec of rec.getFieldValue(stuff_link_id)) {
rec.addRecordToField(stuff_rec,stuff_table_id);
}
}

document.saveAllChanges();

[David Gold]

I’ve had a go at some Javascript but can’t get it to work. Basically I want it to take a search term off the clipboard, do a search and get the record ID for the found item and then copy that to the clipboard. It isn’t working so could someone tell me what I have done wrong?

var myForm = document.getFormNamed(‘Scopus RE’);
var records = myForm.getRecords();
var search_term = Utils.copyTextFromClipboard();
var result_count = 0;
var results = [];
var selected;

function search_records( haystack , needle ) {

var name_id = ‘fld-90984efc3585456ab56763adff0a5228’;
var rec;

for (const rec of haystack) {
if ( rec.getFieldValue( name_id ).toLowerCase().includes( needle.toLowerCase() ) ) {
results.push( { name: rec.getFieldValue( name )
} );
result_count++;
}

}

if( result_count == 0 ){
console.log( ‘No results found!’ );
}else if( result_count > 1 ){
multiple_results();
}else{
copy_record.getId( results[0] );
}

}

[Daniel Leu]

I would use something like this:

if (typeof search !== 'undefined'){
	var records = search.getRecords();
} else {
	var records = form.getRecords();	
}

This doesn’t address the corner case where search exists but doesn’t contain any records, eg records.length == 0.

I wouldn’t use record in var record = ... since that is a predefined value.

[David Schwane]

I have this script that returns a random record regardless if I’m on a form or a saved search. Works great but I know using try catch this way is not correct. What is a better way to resolve if I’m trying to get a form recordset or search recordset?

Random_Record();
try {
var records = search.getRecords();
}
catch (err) {
var records = form.getRecords();
}
var record = records[Math.floor(Math.random() * records.length)];
form.selectRecord(record);


[Brendan]

Try this:

function Diff_Calc() {

	let records = form.getRecords();
	let value_id = 'fld-ee301521ec6549aa9a657a0b457a3e5e';
	let difference_id = 'fld-816a0c97ff40447d9e13fb14d3dfc912';

	let first_rec = records[0];
	var prev_value = first_rec.getFieldValue(value_id);
	
	if (prev_value == undefined) {
		prev_value = 0;
	}
	
	first_rec.setFieldValue(difference_id, 0);
	
	for (var index = 1, count = records.length; index < count; index++){
	
		let rec = records[index];
		var value = rec.getFieldValue(value_id);
		
		var diff = value - prev_value;
		rec.setFieldValue(difference_id, diff);
		
		prev_value = value;

	}
	form.saveAllChanges();
	
}

Diff_Calc();

[Goutalco]

Hello everybody,

Since the number of dictionaries is significant the import of images for every page of the dictionary blows up my Tap Forms 5 document to a size > 40 GB. So I opted for the second option, i.e. searching for a root and opening the dictionary as a PDF file.

The solution suggested by Brendan by setting up a Website Address field which when clicked will launch the default PDF Viewer was not working for me. I got the following Error:

The application “Tap Forms 5” does not have permission to open “Test.pdf.”

So I found another solution which opens the PDF Viewer via a form script and tells the default PDF viewer to open the dictionary on the page that contains the term searched by the user.

In case anybody is confronted with a similar problem, i.e. searching for alphabetically arranged items in PDF files, I share my script.

var root;

var user_input = function Show_Page(ok) {
	if (ok == true) {		
		// Variable for the record searched by root
		var page_for_root;
	    
	        // Getting the pages (records) of the dictionary
		var pages = form.getRecords();
		
		// Getting the id of the field that takes the 
		// first root present on a page
		var root_1_id = 'fld-8ab2fc88535e49dbb60df845f4157590';

		// Getting the id of the field that takes the last root      
                // present on a page
		var root_2_id = 'fld-fa1b6724b80f4780b1779ab4ef098c75';

		
		// Looping through the pages
		for (var index = 0, count = pages.length; index < count;
                index++){
			
			// Getting the first root present the current page
			var root_1 = pages[index].getFieldValue(root_1_id);
			
			// Getting the last root present the current page
			var root_2 = pages[index].getFieldValue(root_2_id);
			
			// Compare root to root_1 and root_2
			var compare_1 = root_1.localeCompare(root, 'ar')
			var compare_2 = root_2.localeCompare(root, 'ar')

			// Try to get the first page that contains root 
                        // and leave the loop in this case
			if (compare_1 <= 0 && 0 <= compare_2) {
				page_for_root = pages[index];
				break;	
			}			
		}
		
		// If there is at least one page that 
                // contains root, show the first one
		if (!(page_for_root === undefined)) {
		      // Getting the URI for the dictionary
                      // file (with appropriate page number) 
                      // stored in DevonThink
		      var devon_id = 'fld-16ada2521a864aae8646222a6661a422';
    		      var devon = page_for_root.getFieldValue(devon_id);   		
    		      // Open the dictionary on the the requested page 
    		      Utils.openUrl(devon);
    		      // Go to the page (record) of the dictionary 
                      // in Tap Forms 5
    		      form.selectRecord(page_for_root);
  		} else {
  			console.log("Root not found");
  		}   
   } else {
          console.log("Cancel button pressed.");
   }
}

	
function Search_Dic() {
		
	let prompter = Prompter.new();
	prompter.cancelButtonTitle = 'Cancel';
	prompter.continueButtonTitle = 'OK';
	prompter.addParameter('Root: ', 'root')
        .show('Dictionary', user_input);
}

Search_Dic();

[Goutalco]

Thank you Brendan,

I converted all my dictionaries to images and using your hint I successfully imported these images to my document via a CSV file. This part was quite straightforward.

Now I like to create a search inside a form script via the Prompter class and since my understanding of JavaScript is at best rudimentary I really appreciate any comment to the script that I was able to generate using the help file.

var root;

var user_input = function printOut(continued) {
	if (continued == true) {
	
		var page_for_root;
	       // Getting the pages of the dictionary
		var pages = form.getRecords();
		
		// Getting the id of the field that takes the first root present on a page
		var root_1_id = 'fld-e724350c999d4fddafc12d258d407a3c';

		// Getting the id of the field that takes the last root present on a page
		var root_2_id = 'fld-a01b915c72904b2caba68eb8c2657055';
		
		for (var index = 0, count = pages.length; index < count; index++){
			// Getting the first root present the current page
			var root_1 = pages[index].getFieldValue(root_1_id);
			// Getting the last root present the current page
			var root_2 = pages[index].getFieldValue(root_2_id);
			
			// Compare the root the user is searching for to root_1 and root_2
			var compare_1 = root_1.localeCompare(root, 'ar')
			var compare_2 = root_2.localeCompare(root, 'ar')

		       // Try to get the first page that contains root and 
                       // leave the loop in this case
			if (compare_1 <= 0 && 0 <= compare_2) {
				page_for_root = pages[index];
				break;	
			}			
		}
		
		// If there is at least one page that contains root, show the first one
		if (!(page_for_root === undefined)) {
    		        form.selectRecord(page_for_root);
  		} else {
  			console.log("Root not found");
  		}   
   } else {
          console.log("Cancel button pressed.");
   }
}
	
function Search_For_Root() {
		
	let prompter = Prompter.new();
	prompter.cancelButtonTitle = 'Cancel';
	prompter.continueButtonTitle = 'OK';
	prompter.addParameter('Root: ', 'root').show('Enter a root', user_input);

}

Search_For_Root();

Thank you for your patience.

Cheers

• This reply was modified 2 years, 7 months ago by Goutalco.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

I think this is a case where the JOIN mode on iOS will render the table inline but the 1:M mode has a selector to go to a subview that has the table.

I feel the tangible feature request is to be able to control if the JOIN displays inline or as a subview (conversely one could consider the inverse for the 1:M/M:M modes).

In terms of fixes right now, you could use scripting to create the links for you. You’d start with a JOIN field so that Tap Forms does the heavy lifting for finding the child records and then you just need a script that iterates through each of the records and attaches the child records from the JOIN field to a 1:M field.

The script is pretty simple, this is from an earlier post:

record.getFieldValue('fld-joinfieldid').forEach(rec => record.addRecordToField(rec, 'fld-1tomanyfieldid'));

This will do just the currently selected record and whilst I (still) haven’t tested it but it should work though you need to replace the field ID’s to match. You could probably put this in as a field script as a test run but I’m not sure if the JOIN field triggers a script field update automatically (since the field isn’t edited directly) so I’d probably stick with a form script. Something like this as a form script might do it:

form.getRecords().forEach(parentRecord => parentRecord.getFieldValue('fld-joinfieldid').forEach(childRecord => parentRecord.addRecordToField(childRecord, 'fld-1tomanyfieldid')));
document.saveAllChanges();

Basically get all the records from the current form as parentRecord, then for each parentRecord get all of the records from the JOIN field as childRecord, then for each childRecord add it to the 1:M field in the parentRecord. Then save all the changes :D

Again, I’ve not tested it and you’ll have to splice your own ID’s in, running this more than one should be safe (TF generally dedupes multiple record links) and should allow you to relink everything after you do your own import. The document.saveAllChanges() makes sure that everything is saved properly so it’s important it’s there as well. You can delete the boilerplate you get when creating a form script and just put this in with the ID’s replace to match your field IDs.

[Sam Moffatt]

Ok, so some changes. I ditched the table field and made it a new form and added a link to form field to connect it. In the new form, creatively labelled “Verkocht”, I added a form script (or three): Aggregate by Date, aggregateForm and md5sum. Attached is a copy of the template that should upgrade an existing document with the appropriate fields and scripts though let me know if it’s missing something.

Aggregate By Date is the script you’ll want to run to generate a report in “Nieuw formulier” grouped by date, name and the sum of aantal. If you look at it’s implementation, it looks like this:

form.runScriptNamed("aggregateForm");

function Aggregate_By_Date() {	
	let fieldMap = {
		['fld-cf72e8f115344d2fa33889757f9f19f0']: 'fld-28cad0a0ea4d4177aecd20e6f63fe470', // naam
		['fld-ac527a6647d049869d5b3b26f8ef6d1d']: 'fld-4f2a8e2e7d974fc08f012a1889760397', // datum
		['fld-ecae6c80bd38432abaaace22806dfb25']: 'fld-30f5df230f0b44479e53a83df9295e38', // aantal
	};
	
	// 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.
	let dateFormat = { "day": "2-digit", "month": "2-digit", "year": "numeric"};
		
	let translationMap = {
		['fld-ac527a6647d049869d5b3b26f8ef6d1d']: (inputValue) => { return inputValue ? inputValue.toLocaleDateString("sv-SE", dateFormat) : ""; }
	}
	
	let targetForm = document.getFormNamed("Nieuw formulier");
	return aggregateForm(form, targetForm, 'fld-ecae6c80bd38432abaaace22806dfb25', fieldMap, translationMap, {'logcsv':true, 'hashedrecord': true});
}

Aggregate_By_Date();

Ok, so we’re loading in the script “aggregateForm” at the start, that’s the utility/library file. Then we set up a field map, this maps the fields from the source form (in this case “Verkocht”) to the destination form (“Nieuw formulier”). Add extra to add more fields to the key/rollup or change up the ID’s to match.

We want to rollup by date so we nee dto format the date, that’s what this dateFormat line does, specify the format for your date. Check out the docs to learn more but that’s pretty straight forward.

The translationMap basically says to take the field fld-ac527a6647d049869d5b3b26f8ef6d1d (which should be a date) and turn it into a string representation of the date. If we don’t do this then we get big long date strings, not fun.

Last lines say to get the Nieuw formulier form and then run this aggregateForm thing. It takes a source form (which is the current form selected; you could make this search too), it has a target form to create records in (or not if unset), the field ID of the field to aggregate, the two maps we talked about earlier and then the configuration options (logcsv creates a CSV in your console log and hashedrecord means that it creates and updates the same record).

The aggregateForm script looks like this:

form.runScriptNamed("md5sum");

function aggregateForm(sourceForm, targetForm, aggregateField, fieldMap, transformerMap = {}, options = {}) {
	for (let defaultKey in defaults = {
		'logcsv': false,
		'hashedrecord': false,
		'returncsv': false,
		'returnjson': false,
	}) {
		options[defaultKey] = options[defaultKey] !== undefined ? options[defaultKey] : defaults[defaultKey];
	}

	// check we have a source form, a field to aggregate and a mapping field.
	if (!sourceForm) {
		throw new ReferenceError("Unset source form");
	}
	
	if (!aggregateField) {
		throw new ReferenceError("Unset aggregate field");
	}
	
	if (!fieldMap) {
		throw new ReferenceError("Unset field map");
	}
	
	if (fieldMap.length < 2) {
		throw new ReferenceError("Field map must have at least two entries (aggregate field and key)");
	}
	
	let rollups = {};
	let destField = fieldMap[aggregateField];

	// iterate to all of the records in the form
	for (var rec of sourceForm.getRecords()) {
		//console.log(rec.getId());

		let keyFields = [];
		let aggEntry = 0;
		let keyEntries = {};
		
		for (let srcField in fieldMap) {
			//console.log(srcField + " => " + fieldMap[srcField])
			let value = rec.getFieldValue(srcField);
			if (transformerMap[srcField]) {
				//console.log("Transforming...");
				value = transformerMap[srcField](value, rec);
			}
			//console.log(value);
			
			if (srcField == aggregateField) {
				aggValue = value;
			} else {
				keyEntries[srcField] = value;
				keyFields.push(value);	
			}
		}

		var rollupKey = keyFields.join(",");

		// Rollup to this key, add to the existing value or set it if not set.
		if (!rollups[rollupKey]) {
			rollups[rollupKey] = {};
			for (let srcField in fieldMap) {
				rollups[rollupKey][fieldMap[srcField]] = keyEntries[srcField];
				rollups[rollupKey][destField] = aggValue;
			}
		} else {
			rollups[rollupKey][destField] += aggValue;
		}
	}
	
	let retval = [];

	// log to console the aggregated values.
	for (let rollupKey in rollups) {
		if (options['logcsv']) {
			console.log(rollupKey + "," + rollups[rollupKey][destField]);
		}
		
		if (options['returncsv']) {
			retval.push(rollupKey + "," + rollups[rollupKey][destField]);
		}
		
		if (targetForm) {
			let destRecord;
			if (options['hashedrecord']) {
				let targetKey = "rec-" + md5(sourceForm.getId()+targetForm.getId()+rollupKey);
				destRecord = targetForm.getRecordWithId(targetKey);
				if (!destRecord) {
					destRecord = targetForm.addNewRecordWithId(targetKey);
				}
			} else {
				destRecord = targetForm.addNewRecord();
			}
			destRecord.setFieldValues(rollups[rollupKey]);
		}
	}
	document.saveAllChanges();
	
	if (options['returnjson']) {
		return JSON.stringify(Object.values(rollups));
	}
	
	if (options['returncsv']) {
		return retval.join("\n");
	}
	
	return rollups;
}

Nothing to customise there, it’s all parameterised. I’ll put it up in the script manager over the weekend.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

It does seem a tad on the large side though there is a compact database option under preferences for the document. The links shouldn’t be too much and the scripts are generally tiny. If it’s just records, I don’t think it should be that large for 142 records. If you’ve got attachments or images embedded that can grow things. One thing to watch out for is that images in notes fields are stored inefficiently by Apple’s rich text control so if you have a lot of images in notes field internally macOS translates though to TIFF images with a very poor compression scheme.

Ok some tweaks to the form script to get the ID’s to align but this seems to work for the Verkocht table and Aantal subfield:

var firstForm_NaamFieldId = 'fld-f1bb4282fd05432b9d3205004508ee17';
var firstForm_tableFieldId = 'fld-b40eebf010de45f4ad4f2d0815cec963'; // was 'fld-4dde0c4712954541a69b18d602bfcb27'? 
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9'; 

var secondForm = document.getFormNamed("Nieuw formulier");
var secondForm_NaamFieldId = 'fld-28cad0a0ea4d4177aecd20e6f63fe470';
var secondForm_NummerFieldId = 'fld-30f5df230f0b44479e53a83df9295e38';

function Create_Summaries() {
	// get all of the records from the current form (should be first form)
	for (let currentRecord of form.getRecords()) {
		// create a new record for it in the second form
		let linkedRecord = secondForm.addNewRecord();

		// copy the name across
		linkedRecord.setFieldValue(secondForm_NaamFieldId, currentRecord.getFieldValue(firstForm_NaamFieldId));

		// create the total value field
		linkedRecord.setFieldValue(secondForm_NummerFieldId, currentRecord.getTotalOfLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId));
	}

	// save all changes
	document.saveAllChanges();
}

Create_Summaries();

Similarly adding a Link to Form from the first to the second form, ticking “Show Inverse Relationship” and hiding it linked it back appropriately:

var firstForm_tableFieldId = 'fld-b40eebf010de45f4ad4f2d0815cec963';
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9';
var secondForm_linkFromFormFieldId = 'fld-1950d775d8774c38b39535f97d4c40a2';

function TotalValue() {
	let linkedRecord = record.getFieldValue(secondForm_linkFromFormFieldId);
	return linkedRecord.getTotalOfLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId);
}

TotalValue();

Also attached a copy of the form template with the changes. You should be able to import it and it should update your document. The form script should create entries but the field script will need you to link an entry to get it to work.

Attachments:

You must be logged in to view attached files.