Search Results for 'script'

[JB Be]

Thank you for your certainly very valuable suggestions. Unfortunately, scripting is beyond my capabilities :-(

[Brendan]

Hi Michael,

1. You could do this with a Script field. You would have to write a script that looks at Field A, and then sets some value onto Field B after Field A has had its value set.

2. Yes. There’s a Default Value option on many field types that you can specify. When you create a new record, the Default Value will be copied into the field.

3. On macOS there’s a custom layout feature where you can place fields anywhere you want. But on iOS, the label is on the left and the value is on the right.

4. The Mac version has an Access Controls function on a per form basis. So you can password protect a form and you can prevent people without the password from modifying it, adding, deleting, or updating records. The iOS version has a limited version of this feature where you can password protect a form and the form structure cannot be changed. But not all the other access controls.

Thanks,

Brendan

[Sam Moffatt]

Calculation fields will only let you do aggregate operations on fields that have multiple values (table fields, link to/form form on a many relationship). For numeric values there are more options but non-numeric fields generally results in a simple count.

Scripting will give you access to each record and let you do what ever operation you need. One of the other forum members has done a Tap Forms JavaScript Scripting 101 and the Scripting 102 has a section on looping through records. You can use Javascript template strings to relatively easily build strings with text field values.

[JB Be]

Thank you for the clarification. I successfully copied the values (all text values) from one form into the other form via a table field, as suggested. However, in the next step, using such values together with values in other fields of the receiving form in a Calculation field seems not to work (the calculation always seems to end up into a COUNT()-function), but is crucial to my use of Tap Forms. And no, I have no knowledge of scripting, but hope/expect that this should work out of Tap Forms as is (at least I didn’t find anywhere any restriction in this direction to the contrary). Did I miss something?

[JB Be]

Thank you for an incredibly swift response. Impressive!

To 1. above: This unfortunately doesn’t really reply to my question. is there a regular Boolean search possible (NOT, AND, OR, Parentheses ( ), Quotes ” ”)? The popup button on the Saved Search edit popover view only explains to AND and OR. Can I enter some advanced boolean query into the ‘Search term’ entry box? If yes, what would be the syntax for such advanced queries? What about truncated search (all instances that end on ‘….searchstring’? What about wildcards? Is there somewhere a comprehensive description of all search possibilities on the Mac (I have understood that the search functionalities on iOS are limited to some extent).

To 2. above: Ok, solves this issue. I am mostly interested in features on the Mac.

To 3. above: Understood. I am nevertheless unhappy with the way this works.

[Brendan]

You would need to use a Script to copy the data into fields of the parent form. When you have relationships between forms, it’s basically just a reference to the records stored in the other form. So if you delete the record in the other form, you’re deleting the reference too. The same goes if you make changes.

However, if you were to use a Table field and link the Table field to another form, when you select records from that other form, the values are copied into the Table field. So even if you delete the original record or make changes to it, it won’t affect the display of the records in the Table field. A Table field also has the added advantage that you can add additional sub-fields to it. This could be useful for example, if you had a Product form and an Order Items Table field. You could select items from the Product form that gets copied into the Order Items Table field and you could have an additional Quantity field which you can then specify what the quantity is.

Look for my Invoice Tracking sample document in the Template Exchange forum to see an example of this in action.

Thanks,

Brendan

[JB Be]

I am new to Tap Forms and I have no knowledge of Java Script (I am basically coming from early FileMaker).

1. Is there a more comprehensive explanation available of search capabilities in Version 5.3 than the little explanation provided in the online ‘Manual’, which is just not enough. E.g is there finally regular Boolean search possible (NOT, AND, OR, Parentheses ( ), Quotes ” ”)?

2. How do I search in Version 5.3 e.g. records in which Value A may appear in field 1 OR value B may appear in field 2, but in no case Value C appears in field C.

3. Is there a way to avoid that after every advanced search, the search is saved and populates the side bar to the left?

[T.L. Ford]

The JSON files are all small (old data set, with few records). It worked on one of the files, but not the others and then stopped working on the original. I’ll circle back around to the problem and play with it more eventually. Random other trivia when you mentioned pasting JSON into a note field: I started writing a script that generates action notices that have javascript in a hidden field to link back to the original record that generated the action notice, and then on the action form, buttons to run the javascript in the field to either open the correct record or perform a specific action on the record. I had it working but then broke it and haven’t circled back to it again yet. So many fun things to do and play with!!!

[Sam Moffatt]

There should be a Python 2.7 install and a Python 3 install contemporaneous with your MacOS release. The /bin/python was copy paste from the OP, /usr/bin/python or /usr/bin/python3 should be the default paths though in my example I used python directly to execute it so I didn’t really hit that issue.

Since Tap Forms is sandboxed, you need to make sure you any paths you use are accessible in the sandbox. If you’ve updated to the latest Tap Forms, there is a script folder access you can use to make sure that path is accessible inside of TF. Valid JSON should parse, I’ve never really had an issue with that so file access limits via sandbox is the only thing I can think of. That said a good chunk of my JSON importing that I do is by putting it into a plain text notes field. If you’re importing particularly large files I could see some sort of memory safeguard kicking in but I’ve not personally seen that nor can I find anyone mentioning it from a quick Google search.

[T.L. Ford]

I modified your script to accept an argument, to dump all of the tables in the data file, and to split the schema and data JSON into separate files for each table. Python is not in my personal language-set so there’s no argument verification, niceties.

I couldn’t get Tap Forms to import the JSON consistently (mostly returned undefined, even for one that previously worked). This could be a firewall/security issue on my machine for the file://. The JSON would still need the JavaScript to create the forms and parse the data into those forms.

#!/usr/bin/python
import json
import sqlite3
import sys

arg = sys.argv[1]

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

connection = sqlite3.connect(arg)
connection.row_factory = dict_factory
cursor = connection.cursor()

cursor.execute("select name from sqlite_master where type = 'table'")
tbls = cursor.fetchall()

for value in tbls:
	tbl=value.get('name')
	print "exporting",tbl

	# Grab the schema of docs
	cursor.execute("PRAGMA table_info('" + tbl + "')")
	results = cursor.fetchall()
	with open(tbl + '_schema.json', 'w') as outfile:
		json.dump(results, outfile, indent=4)

	# Grab the contents of docs
	cursor.execute("select * from "+tbl)
	results = cursor.fetchall()
	with open(tbl + '.json', 'w') as outfile:
		json.dump(results, outfile, indent=4)

connection.close()

As for Python installs… after some digging, I found several with varying versions. I routinely abuse my computer so this is unsurprising. I didn’t have a /bin/python.

[Sam Moffatt]

Python has shipped with MacOS X for an exceedingly long time, I think it might have been there in the initial releases, I have a recollection of it being there in Mac OS X 10.4 at least and available all the way to Catalina and it seems Big Sur. It also has had a JSON module for a while. The biggest problem is generally that the Python versions Apple ships are old. Apple is warning that it’ll remove it in the future release but it’s still there for the time being.

I did a quick search for “python sqlite json export” and the first result was a StackOverflow post with an elegant example but unfortunately for PostgreSQL. The third option was a blog post on “Generating JSON Documents From SQLite Databases in Python” which is actually pretty succinct too but generates not JSON but a Python tuple.

I did a quick modification to make it explicitly output JSON and I didn’t have a sqlite3 database of my own handy, so I borrowed a relatively empty Tap Forms document to test with. I ran this script on High Sierra, Mojave and Catalina without any issues.

As an extra thing, I did a quick addition to also dump the schema as well. Probably write it out to different files but this is just a quick example:

#!/bin/python
import json
import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

connection = sqlite3.connect("db.sqlite3")
connection.row_factory = dict_factory
cursor = connection.cursor()

# Grab the schema of docs
cursor.execute("PRAGMA table_info('docs')")
results = cursor.fetchall()
print json.dumps(results, indent=4)

# Grab the contents of docs
cursor.execute("select * from docs")
results = cursor.fetchall()
print json.dumps(results, indent=4)

connection.close()

Sample Output:

silversaviour:db-5c59efda9b344f77a4ec339ea468fdab.cblite2 pasamio$ python dump.py 
[
    {
        "name": "doc_id", 
        "cid": 0, 
        "dflt_value": null, 
        "notnull": 0, 
        "pk": 1, 
        "type": "INTEGER"
    }, 
    {
        "name": "docid", 
        "cid": 1, 
        "dflt_value": null, 
        "notnull": 1, 
        "pk": 0, 
        "type": "TEXT"
    }, 
    {
        "name": "expiry_timestamp", 
        "cid": 2, 
        "dflt_value": null, 
        "notnull": 0, 
        "pk": 0, 
        "type": "INTEGER"
    }
]
[
    {
        "docid": "rec-e82e27a32f984a8c92caab13591f0a52", 
        "doc_id": 1, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-790840877753424395d9eb37bdfcdfa9", 
        "doc_id": 2, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-749bd3b4775347ada2e8168d680f5228", 
        "doc_id": 3, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-0059f8f1688c4bd69140a296ce18930a", 
        "doc_id": 4, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "pik-59aa950c01ba41cc9e7014727d3019ee", 
        "doc_id": 5, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-1cab945d81a94c93bf8cf7128ac68601", 
        "doc_id": 6, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-ff84c90f17f64c388240fa259ee72d05", 
        "doc_id": 7, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-4daa3b4d14e74eca83870378701c798a", 
        "doc_id": 8, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-d124f2d5fb334385b2e68ac7cc1ad854", 
        "doc_id": 9, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-3ec681983f0a48c4b8ebe6289096ec40", 
        "doc_id": 10, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "frm-cdcfe7a8faed445daa5bcc530deba43a", 
        "doc_id": 11, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "-uncategorized-", 
        "doc_id": 12, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-06e0be082efa4a69b985b02f01c93e95", 
        "doc_id": 13, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-300e6a1188da4aafab279ad651560d4e", 
        "doc_id": 14, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-f4c98ab5dd1047d0be24100a764cff3f", 
        "doc_id": 15, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-30786d9db4c249858405ce784d2930ce", 
        "doc_id": 16, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-4ae324b620ca49fda0f0f32c1974a150", 
        "doc_id": 17, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-28809156390b4db186d6b2d64d255b9b", 
        "doc_id": 18, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-f076eaa7bd8c4b70bf2e5e4ea4e1d49c", 
        "doc_id": 19, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-97deda5465914308b7a7c5505a6aa3de", 
        "doc_id": 20, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-5281c855bc894e5989b1267bd23de0c3", 
        "doc_id": 21, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c64f201bf261499dbc9fdac6386b1246", 
        "doc_id": 22, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-4105ddd8730940e5a8475916732d6720", 
        "doc_id": 23, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-902e9ad25f30405a80850a483c04ebcb", 
        "doc_id": 24, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-fa075bcc9d6f474b84416ed28a74a563", 
        "doc_id": 25, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-b20dc28f75e046fa8dec24a61091cdfe", 
        "doc_id": 26, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-a60af3c801db4bb983429db6f6168421", 
        "doc_id": 27, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-27787e2a762d4fbc9b03fef2c92898fa", 
        "doc_id": 28, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-3ea05f2aa83e465ab736e1d3de25345a", 
        "doc_id": 29, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-e055d4a57d5646e1bb20fc35875b8da0", 
        "doc_id": 30, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-8dad1bce0bdd4d03a58d4a649b857ad9", 
        "doc_id": 31, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-34f7a17e08144979811de53023184fa3", 
        "doc_id": 32, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-0996ded4e01346c7a571f8743b9f3ae2", 
        "doc_id": 33, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-530e2513e4234c84a177347115ee580c", 
        "doc_id": 34, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-8e367aaac2cf46128bdb1bebf9fe3ca4", 
        "doc_id": 35, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-e8b9c23ea99e457f994745764eb33108", 
        "doc_id": 36, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-2d69dc37406944f8b0dd665fdb122377", 
        "doc_id": 37, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-f393cd0bab8348abbcfbafa58e17a2b3", 
        "doc_id": 38, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-1b44ff88a7ad4dd7976fbf3c90fa2aac", 
        "doc_id": 39, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-e34bb38f3fc04c85add78ea9c6fbdd4c", 
        "doc_id": 40, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-cff70ff1a0d3499a860bee1ce13b1132", 
        "doc_id": 41, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-792c3785c2d6474a96c39a43bfdbcd16", 
        "doc_id": 42, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-d3273f0018034b728882d508282e9401", 
        "doc_id": 43, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c0b6902289fa4c3790e971b59e689a14", 
        "doc_id": 44, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-94c9882b0c9747c0be8e6389b946a223", 
        "doc_id": 45, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-a5d71a75f15144a4807417765973a3b3", 
        "doc_id": 46, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-0efc63e234644ddfbd51dde6df095e5c", 
        "doc_id": 47, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-ebe591381a7541829a830ace10f38d69", 
        "doc_id": 48, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-bec8773fb11f42cdb1b085daca95a567", 
        "doc_id": 49, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-2abe8df081d04d66b70ab7dec0b6104e", 
        "doc_id": 50, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-dd471061f9b34ec987aa8bcf02eb1925", 
        "doc_id": 51, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-666c5a32bb1a4729b94c945699c88663", 
        "doc_id": 52, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-70a095c188f54f94a25a864f1a87c253", 
        "doc_id": 53, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-41c2c5ce3c55426e97bba6666f149b84", 
        "doc_id": 54, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-590625056ead4ae6a1be12c11babc473", 
        "doc_id": 55, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-f8880c1442cd41138864e80e9221d070", 
        "doc_id": 56, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c345fb822d9746eb8255b81d650a6efc", 
        "doc_id": 57, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-943c3e3c285e491383dd9acb8f59e14b", 
        "doc_id": 58, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-0273c42a3e0b41a5be230ff446a9697b", 
        "doc_id": 59, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-5ea6fc318ad6402993e585e31b479930", 
        "doc_id": 60, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-7365a14bdbda4d85a3d735d22a901b3d", 
        "doc_id": 61, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c35b278a5d194fe5bd84bf89a25d1801", 
        "doc_id": 62, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-2e016db126b94d489e2a295c069f9458", 
        "doc_id": 63, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-0a7621a6ad1f4d7097dc6cb9d3119a37", 
        "doc_id": 64, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-364dd2a97aa040bfb43ee5bfa4c86e04", 
        "doc_id": 65, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-98ac6534573744a58f6d58826407d185", 
        "doc_id": 66, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-ed26974d43f643b08832d09b87b65237", 
        "doc_id": 67, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-22301abadf184fa4bc6dc9787c554e5d", 
        "doc_id": 68, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c709985047d843909bcf260c2b45de78", 
        "doc_id": 69, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-934b2773975e4635ae00e1179bcb3fcc", 
        "doc_id": 70, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-9fc093e31c7247f0a161a81a44645ac8", 
        "doc_id": 71, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-a2fdd9cc7f4141b6955189564dabef45", 
        "doc_id": 72, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-8641f1651ee043ca825cf0d5e356fe70", 
        "doc_id": 73, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-59f14c6b87b04d0bbcef706f7e4325bc", 
        "doc_id": 74, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-8819b49fbd2f437481bba04f971403d8", 
        "doc_id": 75, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-81cef75d33cf4624a1232cf3e211f53b", 
        "doc_id": 76, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-67980b57facc461f8e3891c76cf3eb27", 
        "doc_id": 77, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-1eaf296525614e668ad798723dfe75e2", 
        "doc_id": 78, 
        "expiry_timestamp": null
    }
]

[T.L. Ford]

I considered Utils.getTextFromUrl() and parsing the text, but I was really hoping for a “no-code, just use this function” solution as the code has to exist for the Tap Forms menu option. Eventually the plan is to loop through fields and format the date fields in a custom SQL statement to create the CSV in a consistent format so it can be correctly/easily parsed on import.

I put a note on the page about using cat build_forms_js.txt | pbcopy.

The comment for addNewFieldNamedWithType was for a documentation update. I found date_time with some playing. SQLite3 data types are a strange application of prioritized affinities, so I have all I need for the basic data types.

JSON/Python – neither was available (on my older Mac OS X, so may be available on newer versions) without some additional installation (yes, I have things). My quick look for “sqlite3 to json” didn’t show a native export. There are many ways to tackle this data migration (my solution of choice would have been PERL), but I was going for “no additional installs; path of least resistance; easiest solution for non-programmers; source visibility for security”, which limited to “bash/sed/awk”, no gawk, no real script languages.

Forcing the Terminal use was debated. It tends to be hard/confusing for people who haven’t ever used it, but I secretly want everyone to know how to use it because it’s just so useful for so many things…

I considered building the JSON (I certainly could), but opted not to for “programming time vs. value”; I’m not sure how many people will actually need/use this script, so another day of coding isn’t justified yet. More code is pending “people are using this and need more functionality” OR “someone who wants to do it writes it for me” (I’m always happy with the later.).

– T

[Sam Moffatt]

If you outputted JSON instead of CSV, you could deserialise it directly via the built in JSON parser. Instead of using bash, I’d suggest something like Python that will output a properly formed JSON document for you. I’d still want to chunk it so you don’t had too big a file but that’s obtainable too. I’d also suggest writing direct to CouchDB if that’s an option as well but that’s a level more advanced.

Re: pbcoy; You don’t need to embed it in a script, it can just be another line in the terminal:

cat build_forms_js.txt | pbcopy

[Brendan]

I wonder if you could use the Utils.getTextFromUrl() function to import your data? You’d have to do the parsing within JavaScript of course, but it may be doable. Especially since I added the script folder access on the General Preferences panel. You can reference any files from that location.

The field types you can use in the addNewFieldNamedWithType command are all the field type names in lowercase with underscores between the words. Except for Date & Time, which is just date_time. But if you export a CSV file with field type tags enabled, you’ll see them in the header row.

There are a ton of properties on the Field object. In fact there’s about 100 or more. Some persistent, some non-persistent. Not sure if I should expose them all.

[T.L. Ford]

I still use Hungarian notation outside of Tap Forms. It makes database programming very easy; I don’t have to think about whether I need to enclose parameters in ”’s or not. (That odd “BOOK” table was a testing table of “randomly grabbed create sql from the internet”.)

I might add pbcopy as an option in a update – likely will start with it off as I don’t like things to auto-use the clipboard.

Yes – a JavaScript helper for importing CSV is in my list of desired useful functionality above. :)