Searching inside a table

[Bob Williams]

I’m creating a database to track my meetings. As part of the record definition for each meeting, I have a table with a couple of checkbox fields and a text field that I’ll use to quickly add todo items during the meeting. Then later as I either complete the todos or copy them to my separate todo tracker, I’ll tick off one of the two checkboxes to indicate as much.

I’ve managed to get that all set up.

What I’d like now is create a saved search that lists all the meeting records that have one or more todo table items where both the checkboxes are unticked. In other words, I want to find all the meetings that still have unresolved todo items.

The problem is, I can’t seem to figure out how to do the saved search part. The table field isn’t even listed in the search rules menus, so it seems I can’t reach into it. Am I missing something?

If table search just isn’t supported in search (in which case, consider this a feature request), a backup plan I came up with is to add a “has open todos” calculation field that I can search, or maybe make it a checkbox field that can be toggled by a script. I’ve not gotten as far as touching the scripting interface yet (I suck at JS), so I’m only guessing that that’s possible and have no idea yet how to do it if it is. If anyone knows of something similar out there to either of these solutions that I could review and adapt, that would be great. Or if there’s some other solution that I’m totally missing, that’s also appreciated.

On a separate note, I’m just starting to use TF, still on trial actually, and I’ve got a bunch of questions and feedback. I was thinking I could just post each of them in a separate thread as I got around to it so as to make those threads easier to find in the future. But, I don’t want to break any cultural rules here… is it better to do that or to minimize the number of messages by just posting a bunch of questions in one?

[Brendan]

Hi Bob,

The general search will actually search the records within Table fields. But the Saved Search function does not.

What you can do though is add a Script Field and then pick out the information from your Table fields. That would expose the result to the parent form and then you can search on that. In your case, a simple script to count the number of checked items in your Table field and return the count value. Then you can search on that. So then you’d know which records had outstanding todos.

If you have questions that are all related, then a single post would be good. But if they’re all separate questions, then it might be better to separate them. But really it’s up to you. Don’t worry about any cultural rules. There aren’t any other than be respectful and polite :)

Oh, and I’m just one guy building Tap Forms, so I can’t always implement everyone’s feature requests, but I do my best :)

Thanks!

Brendan

[Brendan]

Oh, the script wouldn’t be that complicated. Just use this function to get the total of the checked Checkmark fields in your Table field:

var total_checked = record.getTotalOfLinkedFieldForField(table_field_id, checkmark_field_id);

A Checkmark field is stored in the database as either 1 or 0. So getting the total would add up all the 1’s to give you the total and that would tell you how many todo’s are done.

[Bob Williams]

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

[Brendan]

You don’t really need to set the totals into another field. Just return the total from the field script itself.

But if you want to set the value on a field, you need to call form.saveAllChanges(); at the end of your script.

If you’d like to email me a Tap Forms Archive of your form, I can take a look at it for you. support@tapforms.com

Thanks,

Brendan

[Bob Williams]

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.

[Brendan]

That option just executes the script for every record in the form. That’s why you see it running multiple times.

And I responded to your email separately.

[Bob Williams]

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.

[Gregory]

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”).

the reason you’re getting this error is because your variables are being declared in the global space. if you’ve declared one or more of these variables in other scripts that have already run, you’ll get this error.

any variable declared outside of a function block will be global. to minimise these situations, I would reformat the script so that none of the variables are global:

function areThereOpenTodos() {

   var todosTableId = 'fld-cacbf';
   var todosTable_doneCbFieldId = 'fld-b94f8';
   var todosTable_ofFieldId     = 'fld-4f82e';

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

   return (countDoneChecked + countOfChecked) > 0;
}

function run() {

   var openTodosFieldId = 'fld-f1892';

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

run();

[Author]

Posts