Changing Record Focus & Sorting

[Larry Stoter]

Hi,

I’m afraid I’m new to Tap Forms, although I am familiar with several other database applications – mainly ProVue’s Panorama.

I’m also a beginner at JavaScript. I’ve done quite a bit of coding in the past with languages like Basic and Fortran, and in Excel. I have used JavaScript at a very basic level in web page designs but that’s it so sorry if some of my questions are very basic.

How do I move the focus of a script between records? That is, what is the scripting equivalent of the menu commands Goto … Next Record/Previous Record/First Record/Last Record? I want to get a field value from one record and add/subtract to a different field in a previous record. I’m aware there is an issue regarding how the records might be sorted, which leads me to my second question …

How do I script sorting of records?

Thanks,

Larry

[Brendan]

Hi Larry,

You can’t directly access the Go To functions in Tap Forms from the Scripting engine.

But you can tell Tap Forms to select a record. However, it’s generally only used once in a script.

But instead of having the UI select different records, you can just get the records directly from your script.

Sorting is controlled by the Forms and Saved Searches. So whatever sort order you’ve specified on your Form or Saved Search is what will be used within the Script engine.

If you want to find out what your previous record is, you need to first get the list of records. Then you’ll need to pick out which record you want.

For example:

function Previous_Record() {
	
	var records;
	var previousRecord = record;

	try {
		if (search != undefined) {
			records = search.getRecords();
		} else {
			records = form.getRecords();
		}
		
		var indexOfCurrent = records.indexOf(record);
		
		if (indexOfCurrent > 0) {
			previousRecord = records[indexOfCurrent - 1];
		}
	
	} catch (e) {
		// no search selected, so just don't do anything
	}
	
	return previousRecord;
}

Previous_Record();

I had to stick in the try/catch there because search generates a ReferenceError when you don’t have a Saved Search selected if you try to reference it.

Anyway, so without having to manipulate the user interface, you can get the previous record using the above technique.

[Brendan]

Oh, and if there’s no previous record because you’ve already selected the first record, then it just returns the selected record to prevent an out of bounds exception. But maybe you want to return something else if there’s no previous record.

And of course this is all dependent upon how the records are sorted.

[Larry Stoter]

Hi Brendan,

Thanks – very helpful.

With regard to sorting, I would like to change the records sort order within the script. Or, more specifically, I would like to have a conditional branch within the script which returns different sort orders depending on the result of the conditional test.

I was thinking I could do this with form.getRecords() and then sort the array but there doesn’t seem to be an API function to write the sorted array back to the database. Instead, it looks as though this would need to be done with record.setFieldValues() for every record in the database …?

I guess part of the problem I have is that I see records and fields as more fundamental than forms and that forms are just a way to enter and display fields values for each record? So, I’m used to setting up forms/layouts as the final step in putting a database together, after I’ve got everything else working.

Perhaps the TapForms way to handle this is to set up several different forms, each with a different sort order and then use the scripts to move between forms ?

Happy New Year,

Larry

[Brendan]

You can setup different sort orders using Saved Searches for the same form, even if your Saved Search has no Search Rules defined. Then you can ask a form for a specific search if you like:

var search1 = form.getSearchNamed("Alpha Sort");

var search2 = form.getSearchNamed("Number Sort");

and so on.

But the sorts have to be defined outside of your script. When you create a Saved Search and specify the sort order, the database engine I use (Couchbase Lite) creates an index table that contains references to the records sorted by the order you’ve specified. So this is why the Saved Searches need to be created beforehand.

[Author]

Posts