Scripts and searching

[Victor Warner]

I am trying to understand how to use the scripting commands to automate some searches, but not able to understand from the available documentation how to use them.

Example:

1. I have a form called Time spent, one of the fields is called ‘For duplication’. For some records the only entry is a “X”
2. I have created a saved search called “For duplication” which only function is search records in the ‘For duplication’ field which contains an “X”

Queries

1. Is it possible to create a form script which uses the saved search called “For duplication”
2. Also how would I create a search with a script to search a field for a value (such as search the “For duplication” field for a “X”.

Any help gratefully received.

[Daniel Leu]

1) Yes, it is. There is an API function that returns all the records for a named search: var records = getSearchNamed(name)

2) There is an API function as well to perform a search var records = form.getRecordsForSearchTerm("search term");, but it searches all fields.

But that’s not really what you want. But I have a little function for you that only searches a given field.

// Returns an array of record objects for the specified search term in the given field. All records of the current form are searched.
function getRecordsForSearchTermInField(searchTerm, fieldId) {
	
	let result = []
	for (r of form.getRecords()) {
		let field = r.getFieldValue(fieldId)
		
		if (typeof field === 'object' && field !== null){
			field = JSON.stringify(field)
		}
		
 		if (field.search(searchTerm) >= 0){
 			result.push(r)
 		}	
	}

	return result
}

You can use it like this
let records = getRecordsForSearchTermInField("X", 'fld-xxx'),
or use a regular expression as follows
let records = getRecordsForSearchTermInField(/x/i, 'fld-xxx').
More about using regular expression can be found here https://www.w3schools.com/js/js_regexp.asp.

[Brendan]

Oops…

var records = getSearchNamed(name)

should be:

var dupes_search = form.getSearchNamed('For duplication');
var records = dupes_search.getRecords();

[Victor Warner]

Daniel, Brendan,

Thank you for the replies. I must be missing something very fundamental as I do not seem to be able to be apply your replies.

For Brendan’s post:

var dupes_search = form.getSearchNamed('For duplication');
var records = dupes_search.getRecords();

Do I put this is in a form script? Is any other code needed so that what is returned are just the records which match the ‘For duplication’ saved search?

Having entered those two lines into a Form Script, nothing is returned.

[Daniel Leu]

With these two lines you get an array of records for your named search stored in a variable. Then you have to do something with them. What do you want the script to do?

A form script is used to perform an action such as to modify a record. A field script returns a value that is stored in the script field. So they serve a different purpose.

Brendan, good catch! Thanks!

[Victor Warner]

Daniel,

Thank you for the reply.

What I would like to do is simply create the equivalent of selecting by clicking on the saved search “For duplication” under the relevant Form in the Forms list, so that the only the records for the field called For duplication that contain a X appear.

Unless there is an option I have not discovered, saved searches are not selectable via a menu (or a shortcut key). A form script can have a shortcut assigned to it.

Is what I wish to do achievable with a form script?

To be clear what I am trying to create the equivalent of, attached is a video.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Something like this should do the trick as a form script:

var dupes_search = form.getSearchNamed('For duplication');
for(var baseRecord of dupes_search.getRecords()) {
  baseRecord.setFieldValue('fld-dupeflag', false);
  var dupeRecord = baseRecord.duplicate();
}
document.saveAllChanges();

First line gets the named search, the next line loops over all of the records in the search, the setFieldValue unsets your dupe flag field. The var dupeRecord line duplicates the baseRecord and hands you a copy of the record with the ever present document.saveAllChanges() to flag you want to save things.

You might want to swap the baseRecord.setFieldValue line to be after the duplicate() call and apply it to only the duplicated record (e.g. dupeRecord.setFieldValue('fld-dupeflag', false)) however I’m not entirely sure.

Haven’t tested this but I think this should do roughly what you need. It’ll take anything that matches the saved search ‘For duplication’, duplicate them for you and clear the dupe flag (useful if you don’t want to dupe them again).

[Victor Warner]

Sam,

Thank you for the detailed reply.

I can see that script duplicates the fields which have an X in the For duplication field.

I am sorry to be difficult, but this is not what I would like to achieve.

When I click on the ‘For duplication’ saved search under the relevant Form in the Forms list what happens is, in effect, Tap Forms shows (filters) only and displays the records for which in the For duplication there is a X.

That is all I am trying to achieve with a script (if possible). Tap Forms has no way of ‘reaching’ a saved search via the menu, a shortcut or pressing the tab key successively that I am aware of. There are other parts of Tap Forms which are also not reachable, but I have been able to get to them through the use of UI Browser, but even this is having difficulty with saved searches.

If of assistance, the example database I am using is attached.

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi Victor,

So what you’re looking for then is user interface automation? That is a form script that clicks on the “For duplication” saved search for you?

If so, then a script won’t do that. Right now the only user interface automation a script allows you to do is to select a specific record.

I’m just curious what the purpose of that would be though? To save a click? Or is there a larger form of automation you’re looking to integrate this into?

Thanks,

Brendan

[Victor Warner]

Brendan,

Thank you for the reply. Yes, it is part of a larger automation.

Here is the long explanation:

A bit of background, until 2 years ago I use Filemaker to maintain my records concerning my work as a Notary Public in England. FileMaker provided many tools / features not present in Tap Forms, but also is far more complex than my needs – and also when I set it up I did not deal with some of the links between (in Tap Form terminology) Forms in quite the right way, creating two Forms than I really needed.

When I set up (or rather re-created) the database Tap Forms I also corrected what was not working – but I have been able to go much further in automating what I need to produce for client with Tap Forms – which has made it much much much easier than with Filemaker (even though I have had to struggle with Javascript).

I am now exporting, record by record each of the Filemaker for records prior to 2018 – typically up to 7 forms for each client (client contact details, passport details, other id, notarial act, notarised documents, time spent, disbursements) – all which were linked (for example, the client contact form is linked to the passport form and the other ID form, and each of which might contain several records).

I have keyboard maestro macros to format the exported CSV files from FileMaker (add field names needed for the import (as the field names are different between FileMaker and Tap Forms), add a For Duplication field and a X for that field), import all the Forms into Tap Forms at one go rather than one at a time. I added an extra field (For Duplication) which just contains a X, and the saved search to find the records who with X for each Form, to easily pick out the those records and then recreate the links between the forms.

Again with Keyboard Maestro for each Form I can automatically add the links but to create the links for all the Forms at one go I need to be able to reliably access for each Form the saved searched For duplication.

If I was able to do so – than I can speed up the import.

As mentioned in my previous post for some parts of the Tap Forms it is just not possible to access them with a shortcut, menu or by tabbing. But it possible to do so with UI Browser finding the GUI code to do so. An example is attached. But the AppleScript GUI code it generates does not work for the saved searches in the Forms list.

So it is more than just saving a click. If you have any suggestions I would be very grateful to hear about them.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

I think a menu option for getting to the saved search could be useful. Perhaps in the form menu by creating a submenu under the form names would be useful. Then you should be able to use System Preferences to create keyboard shortcuts at least.

Might be good to in the future have the ability to navigate to any part of the UI via the link interface similar to how the record link works.

[Victor Warner]

Sam,

I agree.

My temporary solution (possibly): is to pause the Keyboard Maestro macro. Click on the saved search, and then resume the macro as the macro processes each form.

[Sam Moffatt]

Reading through your process a few times, I wonder if triggering a script might solve some of what you’re trying to do though I might be missing something. If you have data inside the records that can be actioned, you could use a script to parse that and handle it. There is a progress bar interaction and the run button turns into a spinner while a script is running (not sure if you can detect either of that with your automation but it might work).

I’m also wondering if your pain point is linking if using a JOIN Link to Form type to do that might help with that to automatically link records together. To use the JOIN would require a shared key between the two records which if you have that along the way somewhere might make things a little easier.

[Victor Warner]

Sam,

Thank you for looking through my process – thank you for suggesting the use of the Join link – it would certainly remove the need to create the links after import – as Tap Forms would be doing it automatically.

The consequence is that one set of records (after a certain date) will be linked by a Link to Form One to Many link type and then I would need to create a new field which is also set to Link to Form Join type for older records imported from FileMaker.

Are there any resource implications / limitations of having one than one Link to Form links to the same data in Tap Forms (or on the size of the database) or any downsides?

Thank again for the suggestion.

[Sam Moffatt]

There is an extra bit of linking metadata that would be tracked. 1:M/M:M Link to Form fields create an internal link record for each link you make and the JOIN records create a materialised search with similar data (not 100% on the internals though). There is a cost per record link but in general it’s in the order of a few hundred bytes per link and no more than a kilobyte. I think because of the underlying implementation details, the JOIN is probably a little more space efficient because some of the linking data is persisted in the records on each side.

In my own forms, I use a JOIN link to pull the grandchildren of that record into it. The two records share a common key field (actually a calculation that joins three fields together, see the screenshot) and then the JOIN field uses the calculation field on both sides. You could set up a calculation field that uses the value of another field or also uses the 1:M link to get values.

The other aspect is that once you have a JOIN field, you can use scripting to iterate through the records in the JOIN field and have the script add them to the 1:M field. Pseudocoding a little:

let joinRecords = record.getFieldValue('fld-joinfieldid');
for(let joinRecord of joinRecords) {
  record.addRecordToField(joinRecord, 'fld-1mfieldid');
}
document.saveAllChanges();

Basically once TF has done the heavy lifting to find the records to link, add in the links via scripting. An extra step could be a script that scans all of the records or perhaps a saved search to limit the candidate set (again, make Tap Forms do the work to find the records). Then all of your old records will look like new records :D You can keep the JOIN field, hide it so you don’t see it but when you do an import it’s there with what you need ready to go.

Attachments:

You must be logged in to view attached files.

[Victor Warner]

Sam,

Thank you for the explanation.

There are a number downstream issues with linking records through a Join.

Attached is an extract from the database with a couple of records to illustrate (Smith, a 1:M link; Kramer, a Join link)

Up to now all Forms were linked by Link to Form 1:M

So adding relevant fields and setting up Link to Form Join is fine. For records added so that there is a common field all join fine.

But some of the other fields which create totals or reference fields for other Forms no longer work. For example:

For the Form Notarial Act

**Client Name 2 field

1. the field Client Name 2 is a calculation field which picks up a calculation field from the Client contacts details Form (which determines if there is a company and if there is use that otherwise uses the last name of an individual ).
2. If the record in is linked by a 1:M link the Client Name 2 field is automatically filled in.
3. But if the record is linked by a Join link it is left empty

Is the answer a script field such as:

// ID of Form 'Client contact details'

var client_contact_details_id = 'fld-c53e48e48e3a40c7a5656ab92f39ecc9';

result = '';

// 

client_name_record = record.getFieldValue(client_contact_details_id);

var client_name_2_id = 'fld-0d5ab10b7374418bac721d6446c81b73';

var last_name = client_name_record.getFieldValue(client_name_2_id);

result = last_name;

result;

Number of documents

1. this is a calculation field it counts a field (Type of document) in the in the linked 1:M Notarised documents Form.
2. Bt if the record is linked by a Join link it is left empty.

Is the answer a script field? But I do not know that right JavaScript commands to create a total.

There are several other fields which do counts or sums, which will need new fields or be changed to scripts too in this Form.

Any help would be gratefully received.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

When you’re using a JOIN field, both sides are M:M relationships because it doesn’t know if it is singular or not. The calculation field there can’t handle the M:M relationship properly in the same way it it can being on the child side of the 1:M where there is only one entry. The solution for that is generally a script that just pulls out the first entry from the field.

I use this construct to gather all of the child entries and join them together:

var client_contact_details = record.getFieldValue('fld-beaf858dba7d4014b89722411ebcbd3f');
if (client_contact_details) {
	client_contact_details.map(r => r.getFieldValue('fld-0d5ab10b7374418bac721d6446c81b73')).join(' ');
}

First line gets the child records from the link to form, second line checks if we got a value back (avoid undefined error, mostly harmless but some defensiveness doesn’t hurt), the third line is a little bit to unpack though. The map function is like a short hand of a for loop to run a single function. In this case we’re going through each of the records in client_contact_details and “mapping” that value (a Tap Forms record) to the value of the field fld-0d5ab10b7374418bac721d6446c81b73. The last part of that line basically says to convert the array to be “joined” by spaces.

For the JOIN count for documents, I did COUNT( Notarised documents - Join::Type of document ) and that seemed to work properly for me giving me a count of two.

[Author]

Posts