- This topic has 7 replies, 3 voices, and was last updated 3 months, 3 weeks ago by
.
-
I can see from previous posts there isn’t an on-form-load trigger event in which I could attach a script to do a series of things or update calculated fields when I open my datbase, but I could make a script which I manually trigger or I manually click refresh records.
I wondered about an idea that might achieve the same thing but allow me a level of automation/scheduling via a backdoor sort of route.
I sync my database with CouchDb (hosted on my NAS), I wonder if I could issue a command to make a tiny edit in the TapForms CouchDb to my database, which would then cascade a refresh to TapForms, and this would then force the calculated fields or scripts to execute.
the Apple Script code to do something to couchdb would be based on:
— Define CouchDB URL and credentials
set couchdbURL to “http://192.168.1.240:5984/db-1a619b6d486c466c9fc32a1b4a494f00” — Replace with your CouchDB URL
set couchdbUser to “admin” — Replace with your CouchDB username
set couchdbPassword to “removed” — Replace with your CouchDB password— Create the shell command for the curl request
set curlCommand to “curl -u ” & couchdbUser & “:” & couchdbPassword & ” ” & couchdbURL— Run the shell command
do shell script curlCommand— Optional: Notify completion
display notification “CouchDB refresh request sent” with title “CouchDB Refresh”what I’m not sure, having looked in CouchDb is what or where i’d start to make a tiny tweak. i.e. i could create a dummy field in TapForms I don’t need, but I changed via AppleScript to cascade a refresh.
Thoughts anyone????
Cheers
I’ve a solution that’s a work-in-progress, but in essence I created a dummy field I was happy to sacrifice in TapForms, checked it was created in CouchDb as a Table, and then ran this AppleScript
— Define CouchDB URL and credentials
set couchdbURL to “http://192.168.1.240:5984/db-1a619b6d486c466c9fc32a1b4a494f00” — Replace with your CouchDB database URL
set couchdbUser to “admin” — Replace with your CouchDB username
set couchdbPassword to “your password” — Replace with your CouchDB password
— Define the document ID and the field to be updated
set documentID to “fld-0f69d93fa3bd48c99df27375911e2040” — Replace with your field ID
set fieldName to “name” — The field name to update
set fieldValue to “Test” — The new value for the field
— Step 1: Fetch the current document to get the _rev value
set fetchURL to couchdbURL & “/” & documentID
set fetchCommand to “curl -u ” & couchdbUser & “:” & couchdbPassword & ” ” & fetchURL
— Debug: Display fetch command
— display dialog “Fetch Command: ” & fetchCommand
set documentJSON to do shell script fetchCommand
— Debug: Display raw JSON response
— display dialog “Document JSON: ” & documentJSON
— Extract the _rev value from the document JSON using JSON parsing
— Use AppleScript’s
do shell scriptto execute a Python command for JSON parsing— Extract the _rev value using Python for robust JSON parsing
set pythonCommand to “python3 -c ‘import json, sys; doc = json.load(sys.stdin); print(doc[\”_rev\”])'”
set revResult to do shell script “echo ” & quoted form of documentJSON & ” | ” & pythonCommand
— Debug: Display extracted _rev value
— display dialog “Document Revision (_rev): ” & revResult
— Step 2: Create the JSON payload for the update
set jsonPayload to “{\”_id\”:\”” & documentID & “\”, \”_rev\”:\”” & revResult & “\”, \”” & fieldName & “\”:\”” & fieldValue & “\”}”
— Debug: Display JSON payload
— display dialog “JSON Payload: ” & jsonPayload
— URL to update the document
set updateURL to couchdbURL & “/” & documentID
— Create the shell command for the curl request
set curlCommand to “curl -X PUT -u ” & couchdbUser & “:” & couchdbPassword & ” -H ‘Content-Type: application/json’ -d ‘” & jsonPayload & “‘ ” & updateURL
— Debug: Display update command
— display dialog “Update Command: ” & curlCommand
— Run the shell command
set updateResponse to do shell script curlCommand
— Debug: Display update response
— display dialog “Update Response: ” & updateResponse
— Optional: Notify completion
display notification “CouchDB document update request sent” with title “CouchDB Update”
this works, i.e. when run it forces TapForms to update. The one issue I have is that I’ve killed that field so it’s no longer visible at all in TapForms, but is in CouchDb, I think because I didn’t PUT the whole Fields values back, but only the new name (test), but I think it’s a solution, all be it dirtly, and I could then at least creat triggers/schedules for my AppleScript outside of TapForms as/when needed.
update, somethings should be left alone…. but you know what it is, down the rabbit hole. anyway I have worked out how to do what I needed, which is update a calculated field outside of tapforms, here we go if anyone else wants to go down this road.
my situation. i have a days left until a given date calculated field (i.e. days until insurance due or MOT due), that I didn’t want to have to trigger a Formula Recalculation through pressing the button in TF manually, but wanted this to happen daily somehow. (the rest of the record in TF stays fairly static so nothing gets changed to often to force a refresh/update of the calculated fields).
my solution was to find a way to make the change to the calculated field value via CouchDb instead, which works ok.
the steps in summary are.
1. identify the id of fields in TF (for me:
fld-555b6e75e1f0483ead9ca9cc2c122569 is my calculated field, i.e. days left to go
fld-c09f1fbc054647859d811dfdd228abea is my date field I’m using to do the above
2. create a view document in CouchDB to find all documents (records) that contain that field id
3. create an update function/document in CouchDB to update the designated field value with the new value you want
4. create a script (for me AppleScript) to execute daily, to make those changes in CouchDb.
if you’re still reading, here’s some more details.
my search document in couchdb:
{
“_id”: “_design/search_view”,
“_rev”: “20-301796ba60a199c905367bf1fadefe83”,
“views”: {
“search_view”: {
“map”: “function(doc) {\n function searchValue(value) {\n if (typeof value === ‘object’ && value !== null) {\n for (var key in value) {\n if (value.hasOwnProperty(key)) {\n if (key === \”fld-555b6e75e1f0483ead9ca9cc2c122569\” || searchValue(value[key])) {\n return true;\n }\n }\n }\n } else if (value === \”fld-555b6e75e1f0483ead9ca9cc2c122569\”) {\n return true;\n }\n return false;\n }\n\n if (searchValue(doc)) {\n emit(doc._id, null);\n }\n}”
}
},
“language”: “javascript”
}create the update handler document in couchdb
{
“_id”: “_design/updates”,
“updates”: {
“recalculateDaysDiff”: “function(doc, req) {\
if (doc.values && doc.values[‘fld-c09f1fbc054647859d811dfdd228abea’] && doc.values[‘fld-c09f1fbc054647859d811dfdd228abea’].date) {\
var targetDate = new Date(doc.values[‘fld-c09f1fbc054647859d811dfdd228abea’].date);\
var currentDate = new Date();\
var timeDiff = targetDate – currentDate;\
var daysDiff = Math.ceil(timeDiff / (1000 * 3600 * 24));\
doc.values[‘fld-555b6e75e1f0483ead9ca9cc2c122569’] = daysDiff;\
}\
return [doc, ‘Updated the days difference’];\
}”
}
}now create the AppleScript to use both the view and update documents in couchdb
set dbName to “your-database-name”
set viewName to “_design/search_view/_view/search_view”
set updateHandler to “_design/updates/_update/recalculateDaysDiff”set userName to “your-username”
set userPassword to “your-password”
set baseUrl to “http://127.0.0.1:5984/”
set jqPath to “/opt/homebrew/bin/jq” — Use the full path to jq, tool to help split records returned— Create the URL to query the view for document IDs starting with “rec”
set queryUrl to baseUrl & dbName & “/” & viewName & “?startkey=%22rec%22&endkey=%22rec%5Cufff0%22”— Fetch the document IDs using the full path to jq
set docIds to do shell script “curl -s -u ” & userName & “:” & userPassword & ” \”” & queryUrl & “\” | ” & jqPath & ” -r ‘.rows[].id'”— Convert the result to a list of IDs
set docIdList to paragraphs of docIds— Loop through each ID and trigger the update handler
repeat with docId in docIdList
set updateUrl to baseUrl & dbName & “/” & updateHandler & “/” & docId
do shell script “curl -X PUT -u ” & userName & “:” & userPassword & ” \”” & updateUrl & “\””
end repeat— Notify the user that the script has finished successfully
display notification “Document updates have been processed successfully.” with title “Script Completed”when this runs it updates all the records in couchdb with my newly calculated field, and this then triggers the sync with TF and I see the updated values appear without me need to manually click any forms of Recalculate Formula’s button.
obviously this is for sharing and learning together, but please use with care if helpful.
You must be logged in to reply to this topic.