Search Results for 'form.getRecords'

[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 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.

[Sam Moffatt]

At the moment it doesn’t look like you’ve got any links between the two?

The function doesn’t work because first nothing calls it (you need a getTotalofLinkedFieldForField(); at the end of the script) and second linkedRecord is not defined.

Since there isn’t a link between the two forms, we’ll automate populating the other form. We’re going to use form.getRecords to get a copy of all of the records in the current form (there are a bunch of other examples on the forum as well), get the total and populate that into the other form. We’ll create this script inside the first form since it’s the source. You’ll need to swap in the field IDs from the place holders:

var firstForm_NaamFieldId = 'fld-naamfield';
var firstForm_tableFieldId = 'fld-4dde0c4712954541a69b18d602bfcb27';
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9';

var secondForm = document.getFormNamed("Nieuw formulier");
var secondForm_NaamFieldId = 'fld-namefield';
var secondForm_NummerFieldId = 'fld-nummerfield';

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

Though ideally you’d set up a link to form field to keep them together. If you create a link to form 1:M from your first form to your second form and then tick “show inverse relationship”, you can create a script field in your second form that looks like this:

var firstForm_tableFieldId = 'fld-4dde0c4712954541a69b18d602bfcb27';
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9';
var secondForm_linkFromFormFieldId = 'fld-linkfromformfieldid';

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

TotalValue();

Though for that to work you will need that link to form field setup. Give that a spin and see how it goes.

[Daniel Leu]

It is not that complicated! This is a form script that loops through all records. It reads that value of your note field, modifies it, and then writes it back. Additionally, it checks that the note field content is defined, otherwise further processing is skipped.

The script currently replaces all line-breaks with a whitespace and removes all leading and trailing whitespaces. As you see, you can use your regex in JavaScript as well :)

The only change you need to make is define the note_id constant according to your form script. There are several ways to get the id. One way is to click on the field name in the script editor, then select and copy the ID string that is displayed underneath all the field names.

function Remove_Spaces() {

   const note_id = 'fld-xxx' // set note_id according to your form

   for (let rec of form.getRecords()){
	
      // get note field
      let note = rec.getFieldValue(note_id)
      console.log("Orignial note: " + note)

      // Check that note is defined
      if (note) {
         // modify note (replace line breaks with
         // whitespaces and remove whitespaces from
         // both ends of string
         let modifiedNote = note.replace(/\n/g, " ").trim()
			
         console.log("Modified note: " + modifiedNote)
	
         // save note field
         rec.setFieldValue(note_id, modifiedNote)
      }
   }
	
   document.saveAllChanges()
}

Remove_Spaces()

• This reply was modified 2 years, 3 months ago by Daniel Leu. Reason: replaced tabs with ' ' for better readability
• This reply was modified 2 years, 3 months ago by Daniel Leu.
• This reply was modified 2 years, 3 months ago by Daniel Leu.

[Sam Moffatt]

So the outer for loop to go through all of the records in the current form is required regardless of approach (first or second) though the inner for loop wouldn’t be required but the contents of the loop would still be required even if dealing with a single value. The variable names are slightly different between the two examples but you can see a similar pattern of form.getRecords then getFieldValue then creating the rec- prefixed ID, getRecordWithId to see if it exists, and if it doesn’t addNewRecordWithId to create the record and setFieldValue to set the value. The second one doesn’t use a JOIN so it needs to explicitly call addRecordToField to make the link to form field work properly.

I’ve added it to my todo list for the videos, let me see if I can sneak some time in tonight to work through the process in video form because it becomes a little easier I feel when you can see the pieces coming together and follow along (also why my videos are uncut to ensure that everything is visible step by step).

[Sam Moffatt]

The “OR” use case is a little different because you need to split out the individual values first. Thinking a little about this what might work is a form of email addresses and then a link to form M:M relationship. Each email address is split up and added to the other form and linked back. Since it’s M:M you can link multiple parent records and then count that way to unique email addresses.

I created a simple form called “Source Form” and added a name and email address field to it. I then added a second form called “Emails” and added a field called “Email” to it. In the “Source Form” I went back and added a Link to Form to the “Emails” form, set it to M:M and ticked “Show inverse relationship” (I do that last because there used to be a bug where if you changed the type from 1:M to M:M, the inverse field wouldn’t update properly, I think it’s fixed). Back in our “Emails” form, I added a script field that just returns the length of the Link from Field that was created (it’s as simple as this: record.getFieldValue('fld-2b0ad00a96bd4cf4b20dca95899a7a5a').length; where the field ID is the ID of the link from form field). Last piece is to grab the md5sum.js file and add it to the “Source Form” as a new form script and then create another form script that I called “Extract Emails” with this in the contents:

form.runScriptNamed("md5sum");

var source__email_id = 'fld-4e8071f8f3ce4a75a66954f6a3c636ef';
var emails__email_id = 'fld-7e880e79867345549fb04f377412fefd';
var emails_link_id = 'fld-f5d00c535c3c437a87a262f6d0f434e4';

var emailsForm = document.getFormNamed("Emails");

function Extract_Emails() {

	for (let currentRecord of form.getRecords()) {
		let emails;
		try {
			emails = currentRecord.getFieldValue(source__email_id).split(' ');
		} catch(e) {
			console.log("Error processing record: " + e);
			console.log(currentRecord.getUrl());
			continue;
		}
		
		for (let email of emails) {
			console.log(email);
			let recordHash = "rec-" + md5(email);
			let candidateRecord = emailsForm.getRecordWithId(recordHash);
			if (!candidateRecord) {
				candidateRecord = emailsForm.addNewRecordWithId(recordHash);
				candidateRecord.setFieldValue(emails__email_id, email);
			}
			currentRecord.addRecordToField(candidateRecord, emails_link_id);
			document.saveAllChanges();
		}
	}
}

Extract_Emails();

The first line imports the md5sum script we created. The next three var statement lines are the field ID’s for the “Email” field in the “Source” form, the field ID of the “Email” field in the “Emails” field and then the field ID of the link to form field in the “Source” form that links back to the “Emails” form. The emailsForm is getting the Tap Forms object representing the “Emails” form.

The function then iterates over all of the records in the current form (e.g. “Source” form), it then splits up the email field based on a space. You can change this to be a comma or any other character you want. If you need more than one separator you will need to use a regular expression but we’ll skip that. The try/catch is just in case there is something weird to trap the error there and keep processing since we’re dealing with a field value.

The second for loop is then iterating over the emails in the record and creating a new record ID using the MD5 of the email. This pattern is somewhat risky because we intentionally will create colliding document ID’s that generally Tap Forms will avoid. We do that here to be able to do the key lookup without having to build the index ourselves (TF will do that for us). We check to see if a record already exists using getRecordWithId and if it doesn’t we create it using that same ID using addNewRecordWithId and set he email address. We then have Tap Forms link the “Source” record we’re working with to the new email record and save all the changes.

The saveAllChanges is inside the loop because I’ve had issues with TF where the links behave weirdly if you manipulate too many of them via scripting without saving. There’s probably a bug somewhere but reproducing it is a challenge so this is my work around.

This should fill up the “Emails” form with the unique email addresses all linked to their relevant parent forms. I had to do a manual refresh of the form because the script field didn’t populate the links properly. Once I’d verified my duplicates were being detected, I created a saved search on the count of linked records being greater than one.

Attaching a form template with that in it as an example to play with. The earlier one I did a quick change based on an existing form so not so easy to share because it’s got a bunch of other fields in it but this is a relatively concise use case and should work to handle multiple email addresses as well.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Are you looking for exact duplicates (all fields identical) or duplicates on a primary or subset of key fields? If you want all fields identical, does this extend to link to form fields, table fields or photo fields and their contents?

I was working a while back on tooling to do merging of records through the scripting interface to handle when I end up with duplicate shipping records due to key mismatch (sometimes the tracking numbers are changed in transit) but I never got it finished because the UI experience wasn’t something I figured out.

If you’re after a subset of key fields, I did a quick POC where you create a new field in your source form with the key. I already had a composite key field built using a calculation field I use which looks like this (square brackets for field placeholders):

IFEMPTY([Marketplace];"No marketplace";
CONCAT([Marketplace], "/",[Store Name], "/",[Order ID]
))

This creates what should be a unique key for the record based on my own metadata (designed to handle direct sales and hosted marketplaces). I then created a new form called “Orders Dedupe” and put in it three fields: a *string* type field called “key”, *link to form* field called “Order Dedupe” and a script field which counts the entries in the order. The link to form field is configured as a JOIN type on the “key” field of the dedupe form and the calculation field in the original form. The script field looks like this (change your ID’s to match):

function Key_Match_Count() {
	var order_dedupe = record.getFieldValue('fld-fde68e7d2b384cb2a4452d3ae66bbab1');
	return order_dedupe.length;
}

Key_Match_Count();

In this form also create a new saved search that uses the script field and is set to look for values greater than one as those will be the duplicates.

Last step is to populate this form, go back to your base form and create a new form script. I wrote the script below to scan each record, use an md5sum implementation to create a hash of the key field and then look to see if that record exists in the dedupe form:

document.getFormNamed("Script Manager").runScriptNamed("md5sum");

var purchase_key_id = 'fld-3e49aaa5bc32429c8f0f0f234878356d';
var dedupfield__key_id = 'fld-c52906ee940142a0a54fac1a98346afd';
var dedupForm = document.getFormNamed("Order Dedupe");

function Extract_Purchase_Keys() {
	for (let sourceRecord of form.getRecords()) {
		let purchaseKey = sourceRecord.getFieldValue(purchase_key_id);
		if (!purchaseKey) {
			console.log("Missing purchase key for record: " + sourceRecord.getUrl());
			continue;
		}
		let purchaseKeyHash = "rec-" + md5(purchaseKey);
		let dedupRecord = dedupForm.getRecordWithId(purchaseKeyHash);
		if (!dedupRecord) {
			dedupRecord = dedupForm.addNewRecordWithId(purchaseKeyHash);
			dedupRecord.setFieldValue(dedupfield__key_id, purchaseKey);
		}
	}
	document.saveAllChanges();
}

Extract_Purchase_Keys();

This actually found me a dupe record that I hadn’t found in my orders form when I went back to look at the saved search. It’s a bit of a journey, might turn it into a video at some point when I get some more time.

[Sam Moffatt]

Purchase date to price to person doesn’t quite make sense to me but purchase date to person with price aggregated makes more sense, is that what you mean?

You have two options that you can do, one is to start building a composite key with each of the fields you want as commas, that gives you a flat list but if it’s in CSV outputting it looks somewhat transparent. The other is to continue to add another array but that means a bunch of work to validate it as we’ve got (basically the process we did to add marketplace).

I’ll do the first one because it lets you store an arbitrary number of roll ups at the cost of making it a little harder to extract later. Since you’re not using the roll up form and just the CSV this should be ok for you:

Adding extra fields should be a matter of creating the variable and adding them to the rollupKey line. This should work though I’ve not tested it:

function Aggregate_By_Date() {
	// 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.
	var dateFormat = { "day": "2-digit", "month": "2-digit", "year": "2-digit"};
	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var marketplace = rec.getFieldValue('fld-c163aba17ae64c4d93b5a53819a139dc');
		var person = rec.getFieldValue('fld-personid');
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.toLocaleDateString("en-AU", dateFormat);

		var rollupKey = [formattedDate, marketplace, person].join(", ");

		// Rollup to this key, add to the existing value or set it if not set.
		if (!rollups[rollupKey]) {
			rollups[rollupKey] = price;
		} else {
			rollups[rollupKey] += price;
		}
	}
	
	// log to console the aggregated values.
	for (var rollupKey in rollups) {
		console.log(rollupKey + ",$" + rollups[rollupKey]);
	}
}

Aggregate_By_Date();

[Brendan]

Ok, so then it would be like this:

var field_id = 'fld-807fc883160d41ea802f26b7b0bd5af5';

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 website_url = rec.getFieldValue(field_id);
		if (website_url != undefined) {
		   website_url = website_url.replaceAll(find_text, replace_with);
		   rec.setFieldValue(field_id, website_url);
		}
	}

	document.saveAllChanges();
	
	return;
}

findAndReplace('viewer', 'ViewerForm.aspx');

You have to use your own value for field_id.