Search Results for 'script'

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

[Sam Moffatt]

There is a command called pbcopy that will accept input from the CLI and dump something on the clipboard. I use it to reformat text from eBay listings into NVP and dump on my clipboard to paste into Tap Forms table fields. Could skip a round trip to TextEdit in the middle there for you.

A cool feature would be a Javascript helper to import CSV files from disk to skip the import wizard.

I also haven’t seen Hungarian notation in so many years! Blast from the past.

[T.L. Ford]

I put together a script to make the conversion from SQLite3 to Tap Forms easier. It is most useful for databases with multiple tables and many fields.

This BASH script is very rough – consider it BETA.

Information/download can be found here:
http://www.Cattail.Nu/tap_forms/tap_forms_sqlite3_port/index.html

Brendan:
Documentation: addNewFieldNamedWithType needs other types
Documentation: createNewFormNamed

Useful functionality I couldn’t find:
document.importCSV(fileURL, form, createFieldsIfMissing)
form.deleteFieldNamed(‘field’)
fld.required (and properties for other options)

[Philip Gibson]

Thanks, Daniel and Brendan. I’ve just taken a look. I think I’m going to need to learn how to write a script in FileMaker. I hoped I could copy and paste and tweak the code. Unfortunately it doesn’t work like that. Right now, I don’t have time to investigate further but I’ll put it on my to-do list. Thanks again and best wishes.

[Brendan]

Ah yes. Some sort of FM Script:

Go to Record/Request/Page [ First ]
Loop
	Set Variable [ $PATH ; Value: Get ( DocumentsPath ) & GetContainerAttribute ( Products::Container ; "filename" ) ]
	Export Field Contents [ Products::Container ; "$PATH" ; Create folders: Off ]
	Go to Record/Request/Page [ Next ; Exit after last: On ]
End Loop

[Michael Tucker]

I found this javascript example & tried to adapt it but seems I did not get it right.

I am trying to calculate a monthly payment into the field that is this script (shown below).

The number of years of the loan, the principal balance of the loan & the annual percentage rate are already inputted manually in the same form. The script references those data points & does math to find the monthly payment.

Perhaps it could be done without javascript with a calculation field but the calculation is not simple, and this existing javascript example (meant for a web form) seemed like it should be workable if only it were formatted for tapforms. A few parts included in the original example (commented out) I am not trying to get to work at this time, just the part that concerns the loan payment amount.

function calculatePayment() {

// Source: https://www.oreilly.com/library/view/javascript-the-definitive/0596000480/ch01s08.html
// Get the user’s input from the form. Assume it is all valid.
// Convert interest from a percentage to a decimal, and convert from
// an annual rate to a monthly rate. Convert payment period in years
// to the number of monthly payments.

// var principal = document.loandata.principal.value;
// var interest = document.loandata.interest.value / 100 / 12;
// var payments = document.loandata.years.value * 12;

var principal = record.getFieldValue(‘fld-a37b001914c9476e85ff6e72dc5f7e41’);
var interest = record.getFieldValue(‘fld-ad2e6a56376f462fa750fee0eca40902’) / 100 / 12;
var payments = record.getFieldValue(‘fld-3aaee77c789642dbb64633cac5b9b210’) * 12;

// Now compute the monthly payment figure, using esoteric math.

var x = Math.pow(1 + interest, payments);
var monthly = (principal*x*interest)/(x-1);

// Check that the result is a finite number. If so, display the results.

if (!isNaN(monthly) &&
(monthly != Number.POSITIVE_INFINITY) &&
(monthly != Number.NEGATIVE_INFINITY)) {

x = round(monthly);
// document.loandata.total.value = round(monthly * payments);
// document.loandata.totalinterest.value = round((monthly * payments) – principal);
}
// Otherwise, the user’s input was probably invalid, so don’t
// display anything.

else {
x = “”;
// document.loandata.total.value = “”;
// document.loandata.totalinterest.value = “”;
}
}

// This simple method rounds a number to two decimal places.

function round(x) {
return Math.round(x*100)/100;
record.setFieldValue(scr_TP_MortgagePayment,round(x));
form.saveAllChanges();
}

calculatePayment();

[cf]

Even though there already exist triggers for specific fields I think there’s still value in having a trigger for any record mutation, separate from field scripts. I have a few forms where fields depend on each other and need to be refreshed in the right order, if a trigger simply fired on any change I could just go through all fields and update them. Of course record. creation/deletion would be the most valuable of them all since there’s really nothing like it right now.

[Sam Moffatt]

Couldn’t you use a script field to refer to the date field and copy it to a hidden text field that is associated to the pick list? You’d probably still have to manually create the pick list though and assign the colours so it doesn’t save the colour assignment side.

Though perhaps as a feature for future could you have a sliding colour palette? A sorted autolist with an autopalette would work here and so long as you had a range to work with on the HSV scale it wouldn’t be too hard to implement or a set of predefined colour palettes. Random thoughts.

Another feature could be programatic manipulation of pick lists as well to set colour values. Or even just set it directly on the record like the record colour.