Omni Automation and Airtable

What is Airtable? “At its core, Airtable allows you to easily create a database that holds the information that matters for your work, then use it to power the visualizations, processes, and integrations that make up a custom application that’s truly unique to you.”

One of the most basic, yet most functional representation of Airtable databases, is as a standard table of data, similar to what you may see in a spreadsheet application like Numbers from Apple.

The following documentation details how to use Omni Automation with Airtable to implement a standard publishing solution involving OmniGraffle.

DISCLAIMER: Mention of third-party websites and products is for informational purposes only and constitutes neither an endorsement nor a recommendation. OMNI-AUTOMATION.COM assumes no responsibility with regard to the selection, performance or use of information or products found at third-party websites. OMNI-AUTOMATION.COM provides this only as a convenience to our users. OMNI-AUTOMATION.COM has not tested the information found on these sites and makes no representations regarding its accuracy or reliability. There are risks inherent in the use of any information or products found on the Internet, and OMNI-AUTOMATION.COM assumes no responsibility in this regard. Please understand that a third-party site is independent from OMNI-AUTOMATION.COM and that OMNI-AUTOMATION.COM has no control over the content on that website. Please contact the vendor for additional information.

Tagged-Container Publishing

Tagged-Container Publishing is an often used technique to publish information, by using automation to connect a data source with an application that creates layouts. In this scenario, the receiving containers in a design layout (ex: text or image shapes) are marked or “tagged” with some sort of unique “identifier” that it used by the source database to represent a specific record or data set. For an example, in the case of real estate, an MLS (Multiple Listing Service) number, or in the case of individuals, a Social Security or employee number.

Once a container is “tagged” with a unique identifier, another script or automation tool can extract the assigned identifier from the container and use it to locate the corresponding record in the source database, and then extract and download the data in the record’s fields, to populate the receiving tagged container with the extracted data.

In such a scenario, Omni Automation script URLs embedded in a source Airtable database, are triggered by the user to target a selected graphic shape in an OmniGraffle layout, in order to assign or tag them with name of the source Airtable table and the record ID (identifier) of a specific product whose information and image are to be transfered to the selected shape.

Once the receiving graphic shapes (containers) in the design layout are “tagged,” an Omni Automation OmniGraffle plug-in is executed that will iterate the selected graphic shapes, and one-by-one, use their assigned identifiers to locate, download, and assign the data from their corresponding Airtable record to the metadata storage of each OmniGraffle graphic shape, including each related product image file.

A second plug-in is then executed that extracts the locally stored product description and creates a similar sized text box, containing the description text, to the right, left, or below of each selected graphic shape.

Here’s a video demonstrating the process:

Tagged-Container Publishing with OmniGraffle and Airtable.

Graphic Tagging Function

The first step in developing this tagged-container solution, is to create an Omni Automation self-executing function with passed-in data, that can be converted into a “security-approved” function that will tag a selected OmniGraffle shape with the identifying metadata. Such a function will later be encoded for use as a script URL that gets assigned to a field in the source Airtable database table as a formula.

The following code snippet is a self-executing function that will tag the selected graphic shape with the tags and values provided as an argument to the function.

Self-Executing “Approvable” Tagging Function


(function setKeyAndValueForSelectedGraphic(argObject){ graphics = document.windows[0].selection.graphics if (graphics.length == 1){ objKeys = Object.keys(argObject) objKeys.forEach(key => { graphics[0].setUserData(key, argObject[key]) }) } else { alert = new Alert('SELECTION ERROR','Please select a single graphic.') alert.show() throw new Error('No selected item') } })(argument)

Using the encoding tools provided by this website, the function is converted into the following Omni Automation script URL:

Encoded Script URL


omnigraffle://localhost/omnijs-run?script=%28function%20setKeyAndValueForSelectedGraphic%28argObject%29%7B%0A%09graphics%20%3D%20document%2Ewindows%5B0%5D%2Eselection%2Egraphics%0A%09if%20%28graphics%2Elength%20%3D%3D%201%29%7B%0A%09%09objKeys%20%3D%20Object%2Ekeys%28argObject%29%0A%09%09objKeys%2EforEach%28key%20%3D%3E%20%7B%0A%09%09%09graphics%5B0%5D%2EsetUserData%28key%2C%20argObject%5Bkey%5D%29%0A%09%09%7D%29%0A%09%7D%20else%20%7B%0A%09%09alert%20%3D%20new%20Alert%28%27SELECTION%20ERROR%27%2C%27Please%20select%20a%20single%20graphic%2E%27%29%0A%09%09alert%2Eshow%28%29%0A%09%09throw%20new%20Error%28%27No%20selected%20item%27%29%0A%09%7D%0A%7D%29%28argument%29

Note that the schema of the script URL targets the OmniGraffle application (omnigraffle://). This type of schema is supported by Airtable.

This encoded function will eventually be adapted into a field formula that is used in the Airtable table as the formula assigned to a specific field.

Passed-In Argument for Tagging Function

In the example scenario, the passed-in argument will consist of a JavaScript object containing two table identifiers:

Both of these elements will be associated with user-defined tags, in this case: AT-TB-NAME (Airtable table name) and AT-REC-ID (Airtable table record identifier)

Here is the argument object with placeholders used for the values. (Object keys are green and placeholder values are orange)

Argument Object to be Appended to Script URL


{"AT-TB-NAME":"XXXXX", "AT-REC-ID": "YYYYY"}

Using the provided encoding tools, the argument object is encoded to enable it to be appended to the previous encoded script URL:

Encoded Script Argument Object


&arg=%7B%22AT-TB-NAME%22%3A%22XXXXX%22%2C%20%22AT-REC-ID%22%3A%20%22YYYYY%22%7D

Note that during the encoding process, the encoded object is preceded with the script URL header of: &arg= This is done in preparation for the encoded argument to be appended to the encoded script URL.

Converting Encoded Script into Table Formula

The next step is to adapt the encoded script URL into a formula used by table cells in the Airtable database.

After appending the encoded argument to the previously encoded script URL, perform the following actions with the resulting URL:

The resulting formula will look similar to this:

Encoded Tagging Function as Table Cell Formula


("omnigraffle://localhost/omnijs-run?script=%28function%20setKeyAndValueForSelectedGraphic%28argObject%29%7B%0A%09graphics%20%3D%20document%2Ewindows%5B0%5D%2Eselection%2Egraphics%0A%09if%20%28graphics%2Elength%20%3D%3D%201%29%7B%0A%09%09objKeys%20%3D%20Object%2Ekeys%28argObject%29%0A%09%09objKeys%2EforEach%28key%20%3D%3E%20%7B%0A%09%09%09graphics%5B0%5D%2EsetUserData%28key%2C%20argObject%5Bkey%5D%29%0A%09%09%7D%29%0A%09%7D%20else%20%7B%0A%09%09alert%20%3D%20new%20Alert%28%27SELECTION%20ERROR%27%2C%27Please%20select%20a%20single%20graphic%2E%27%29%0A%09%09alert%2Eshow%28%29%0A%09%09throw%20new%20Error%28%27No%20selected%20item%27%29%0A%09%7D%0A%7D%29%28argument%29&arg=%7B%22AT-TB-NAME%22%3A%22") & ENCODE_URL_COMPONENT("REPLACE-WITH-TABLE-NAME") & ("%22%2C%20%22AT-REC-ID%22%3A%20%22") & RECORD_ID() & ("%22%7D")

Once added to the Airtable tabel as a field formula, simply clicking or tapping the script URL in the Airtable interface will automatically trigger the process of transferring the identifying tags and value to the selected OmniGraffle shape.

NOTE: For security purposes you’ll need to enable the execution of remote scripts in OmniGraffle and provide a one-time approval of the initial script execution. This topic is detailed completely in the Script Security section of this website.

Plug-In for Retrieving Airtable Data for Tagged Shape

This publishing example incorporates the use of the following plug-ins (DOWNLOAD):

The Update Metadata for Shape plug-in uses the metadata of the selected graphics to identify, download, and store metadata from related Airtable table records. It incorporates the use of the URL.FetchRequest and URL.FetchResponse classes (documentation) to communicate with Airtable via the internet.

This plug-in also incorporates the Preferences class (documentation) to store and retrieve settings and the required Airtable API and Airtable Database (App) keys, which you enter when you first use the plug-in. NOTE: if you wish to change stored keys, select the plug-in from the Automation Menu while holding down the Control key.

Retrieve and Store Metadata for Tagged Shapes
 

/*{ "type": "action", "targets": ["omnigraffle"], "author": "Otto Automator", "identifier": "com.omni-automation.og-at.update-selected-graphics", "version": "1.0", "description": "Uses the metadata of the selected graphics to identify, download, and store metadata from related Airtable table records.", "label": "Update Metadata for Graphics", "shortLabel": "Update Metadata", "paletteLabel": "Update Metadata", "image": "square.and.arrow.down.on.square" }*/ (() => { const preferences = new Preferences() const action = new PlugIn.Action(async function(selection, sender){ try { if (app.controlKeyDown){ //PREFERENCES var errorID = "A" APIKey = preferences.readString("APIKey") var APIKeyInput = new Form.Field.String( "APIKey", "API Key", APIKey, null ) AppID = preferences.readString("AppID") var AppIDInput = new Form.Field.String( "AppID", "App ID", AppID, null ) inputForm = new Form() inputForm.addField(APIKeyInput) inputForm.addField(AppIDInput) formPrompt = "Enter Airtable API key and App ID:" formObject = await inputForm.show(formPrompt, "Continue") newAPIKey = formObject.values["APIKey"] newAppID = formObject.values["AppID"] preferences.write("APIKey", newAPIKey) preferences.write("AppID", newAppID) } else { var errorID = "B" var APIKey = preferences.readString("APIKey") if(!APIKey || APIKey === ""){ throw { name: "Undeclared API Key", message: "An Airtable API key has not yet been provided for this plug-in.\n\nRun this plug-in again, while holding down the Control key, to summon the preferences dialog." } } var AppID = preferences.readString("AppID") if(!AppID || AppID === ""){ throw { name: "Undeclared App ID", message: "An Airtable App ID has not yet been provided for this plug-in.\n\nRun this plug-in again, while holding down the Control key, to summon the preferences dialog." } } const shapes = document.windows[0].selection.solids const docView = document.windows[0].selection.view // TABLE AND RECORD IDENTIFIERS const MetaKeyRecID = "AT-REC-ID" const MetaKeyTblNme = "AT-TB-NAME" // Iterate without forEach(…) which is not asynchronous for (const shape of shapes){ docView.select([shape]) // RETRIEVE TAGGED METADATA TO CONSTRUCT Airtable URL metaData = shape.userData itemTblNme = metaData[MetaKeyTblNme] if(!itemTblNme){ throw { name: "Missing Metadata", message: `The selected shape has not been tagged with an Airtable table name.` } } itemRecID = metaData[MetaKeyRecID] if(!itemRecID){ throw { name: "Missing Metadata", message: `The selected shape has not been tagged with an Airtable record ID.` } } // GENERATE Airtable URL itemTblNme = encodeURIComponent(itemTblNme) ATURL = `https://api.Airtable.com/v0/${AppID}/${itemTblNme}/${itemRecID}?api_key=${APIKey}` // CREATE NEW FETFCH REQUEST var fetchRequest = URL.FetchRequest.fromString(ATURL) fetchRequest.method = 'GET' fetchRequest.cache = "no-cache" // WAIT FOR THE RESPONSE FROM Airtable response = await fetchRequest.fetch() statusCode = response.statusCode if(statusCode >= 200 && statusCode < 300){ tableJSON = JSON.parse(response.bodyString) tableID = tableJSON["id"] tableDate = tableJSON["createdTime"] shape.setUserData("AT-REC-DATE", tableJSON["createdTime"]) // POPULATE NAME, NOTES, and FIELDS fieldsObj = tableJSON["fields"] shape.name = fieldsObj["NAME"] shape.notes = fieldsObj["DESCRIPTION"] shape.setUserData("AT-FLD-MAN", fieldsObj["MANUFACTURER"]) shape.setUserData("AT-FLD-SKU", fieldsObj["SKU"].toString()) shape.setUserData("AT-FLD-PRC", fieldsObj["PRICE"].toString()) shape.setUserData("AT-FLD-SUM", fieldsObj["SUMMARY"]) // POPULATE IMAGE METADATA imageDataArray = fieldsObj['IMAGE'] imageObject = imageDataArray[0] shape.setUserData("AT-IMG-FNME", imageObject['filename']) shape.setUserData("AT-IMG-URL", imageObject['url']) shape.setUserData("AT-IMG-ID", imageObject['id']) shape.setUserData("AT-IMG-HORZ", imageObject['width'].toString()) shape.setUserData("AT-IMG-VERT", imageObject['height'].toString()) shape.setUserData("AT-IMG-SIZE", imageObject['size'].toString()) shape.setUserData("AT-IMG-TYPE", imageObject['type']) // IMPORT IMAGE FILE imageURL = URL.fromString(imageObject['url']) imageURL.fetch(function(shape, data){ shape.image = addImage(data) }.bind(this,shape)) } else { throw { name: "Data Retrieval Error", message: `A ${statusCode} status code was returned from the query for record ID: ${RECID}` } } } docView.select(shapes) shapeCount = shapes.length shapeOrShapes = (shapeCount === 1) ? "shape":"shapes" hasOrHave = (shapeCount === 1) ? "has":"have" alertMessage = `The metadata for ${shapeCount} tagged ${shapeOrShapes} ${hasOrHave} been updated.` new Alert("Update Completed", alertMessage).show() } } catch(err){ if(errorID !== "A"){ new Alert(err.name, err.message).show() } } }); action.validate = function(selection, sender){ return (document.windows[0].selection.solids.length > 0) }; return action; })();