Searching inside a table

You basically just gave me the code solution to my backup plan of having a separate checkbox field that’s toggled by script. Thanks! Particularly since I’m not sure I’d have very quickly made the connection from the description in the docs between the record.getTotalOfLinkedFieldForField() method and the sub-fields in tables.

That said, it’s only kind of working. Here’s my code:

var todosTableId             = 'fld-cacbfd1e4cac47b085ef2c1e9e076ef2';
var todosTable_doneCbFieldId = 'fld-b94f8f9b9e8e48168f844c03b0f5810d';
var todosTable_ofFieldId     = 'fld-4f82e1aeef90484582e9d8cce49e3369';
var openTodosFieldId         = 'fld-f1892a93ce12498abbced166e497256d';


function areThereOpenTodos() {
 	var countDoneChecked = record.getTotalOfLinkedFieldForField(todosTableId, todosTable_doneCbFieldId);
	var countOfChecked = record.getTotalOfLinkedFieldForField(todosTableId, todosTable_ofFieldId);

	return (countDoneChecked + countOfChecked) > 0;
}

// record.setFieldValue(openTodosFieldId, areThereOpenTodos());

if (areThereOpenTodos()) {
	record.setFieldValue(openTodosFieldId, 1);
	console.log('set to 1');
} else {
	record.setFieldValue(openTodosFieldId, 0);
	console.log('set to 0');
}

The docs say a script is executed when a field it references with record.getFieldValue() is changed. Logical enough. But when I click the run button in the editor, this is the output:

set to 0
set to 1
set to 0
7/24/22, 11:50:13 / Recurring meetings / Are there open todos?
set to 1

One thing of note there is that TF’s own message about the script being run isn’t the first line of output.

More notable is that the script appears to be run four times, and I can’t figure out why. Experimentally, I can’t connect it to anything I’m doing in the script. In fact, if I simplify the script to just this:

console.log('set to 1');

The output still suggests four runs:

set to 1
set to 1
set to 1
7/24/22, 12:06:30 / Recurring meetings / Are there open todos?
set to 1

Outside the script, four runs also doesn’t match the number of records the form has, or really anything else about my tiny database that I can see. So what’s happening here? And while it’s a secondary issue, the changing 0/1 value in the first output is weird since the fields being tallied aren’t changing during the script’s run.

Also, a possible related bug: when I first wrote the script and ran it, I was getting syntax errors about one of my opening vars being defined more than once (“Can’t create duplicate variable”). Strangely, the given var changed from run to run (wasn’t consistently the first one), so I really don’t know what to make of it. But it didn’t happen every time, and after a dozen or so runs, it simply stopped happening.

Finally, while the script ultimately seems to do the right thing in the editor when I either click the run button or hit the Save button, it doesn’t seem to run at all outside the editor. Adding/removing rows from the table, toggling the checkboxes in the table off/on, switching records, and even closing and reopening the file all fail to trigger a run. Maybe it’s running but producing an incorrect result in those contexts, which is certainly possible, though I don’t think so since Scripts > Show Console Log shows no output in those cases. It appears the only way for me to get it to run is through the editor. (And just to be clear, I am using a script field, not the form scripts feature.)

Ah, I hadn’t seen the form.saveAllChanges() method. With that added, however, the script still doesn’t seem to be running. I’ll send it to you.

On the multiple runs front, I noticed that if I turn off the “Update records when saving” option in the editor and hit run, the output is exactly what I’d expect.

With the option turned on:

set to 0
set to 0
set to 0
7/24/22, 13:20:19 / Recurring meetings / Are there open todos?
set to 0

With it turned off:

7/24/22, 13:20:26 / Recurring meetings / Are there open todos?
set to 0

So it got the TF identifier line moved to the top and made it run just once.

I thought about that, but it was running four times and I only had three records. With a little more experimentation along those lines, however, it looks like it runs n+1 times for n records. That’s with the run button, so I’m guessing it’s running for each record plus once for the manual run.

Thanks for clarifying.