URL--Text to Display Feature

In FileMaker, the formula field is actually called a “calculation field”, and they refer to its logic as the “calculation engine”.

At the beginning — for the first 19 years of FileMaker Pro, this “calculation engine” (“formula engine” in Airtable’s terms) was restricted to ONLY the “calculation field” (“formula field”).

So, for 19 years, we only had one field type (“formula field”) that could perform formulas with the “formula engine”.

But then, they start rolling out the “formula engine” out to many other parts of the product — and it even evolved alongside a “scripting engine” (FileMaker’s version of a scripting language). So now, the “formula engine” and the “scripting engine” power all sorts of amazing things in FileMaker. You can do nearly anything in the product with both the “formula engine” and the “scripting engine”.

But keep in mind that FileMaker is a product with a 35-year history — it came out in 1985. So it took a while to get there.

So I think that in due time, we will see Airtable’s “formula engine” get rolled out to other parts of the product. We’re already seeing this now! :slight_smile:

How exciting that we now have a brand new part of the system — the button field — that taps into this “formula engine”! :slight_smile:

And soon, we wil probably see this “formula engine” rolled out to other parts of the product as well! And I’m even hoping that they introduce a simple “Airtable scripting language” that doesn’t require learning JavaScript, which is daunting to many.

So, in my opinion, rolling out the formula field to other parts of the system is just the beginning of great new things that are in store for Airtable! :slight_smile:

But these things happen slowly over time.

Yep, I recall this vividly. I was at the 2012 FileMaker DevCon in Miami when they finally understood and announced that formulas should not be tightly bound to a special type of field. It took them until 2012 to fully unwind this design choice.

At best, I only have two decades left on this planet; they need to step it up. :wink:

Yeah, but no company should feel like they have 35 years to figure out anything and despite it’s long and storied heritage, FileMaker has and continues to struggle. Apple is likely part of the problem. I have a good friend - employee #5 at Claris so the memories, while distant, underscore why it’s good to learn from history.

What would be less daunting?

True, I was referring to that REST API. I seldom see the Scripting block’s API referred to as “the API” or with any “API” reference at all, so I typically interpret any reference to Airtable’s “API” to mean the REST version.

I don’t think that they are — or ever have been — struggling. FileMaker is perhaps the most powerful cross-platform desktop & mobile database at its price point — with an almost unlimited ability to do nearly anything you want within its programming language. FileMaker’s sales grow every year. And they keep expanding its capabilities every year. And they continue to add tools to make it easy to communicate with other platforms as well. It’s insanely powerful.

Anything easier than writing lines of code from scratch! Airtable knows how to make things easy — they’ll figure it out. FileMaker has a beautifully simple & easy scripting language — yet its power can be unleashed if you want to unleash it.

We’re probably boring these poor forum readers to death by now, so I’ll move this to a message.:wink:

Shucks. I never find your posts boring.

But, it would be nice if someone with moderator privileges could spilt this thread in two as the discussion has taken quite a shift from the original intent.

1 Like

I think that learning any computer language can be daunting for someone who has never coded before.

Airtable already has one computer language that it has to support–formula functions. Even though formula fields have a small vocabulary and simple grammar, you feel that the documentation is inadequate. A another full blown scripting language would require even more documentation and training material.

I think that sticking with an existing computer language, such as JavaScript is a smart thing to do. There are tons of free resources for learning JavaScript. There are also tons of resources for coding in JavaScript, such as editors that already know the JavaScript grammar. Plus lots of people already know JavaScript.

If Airtable wants a model for how to bridge the gap between no code and code, I would rather see different tools instead of a new language. For example, a tool could ask a few questions about what the user wants to do, then spit out code that performs that function. Another tool could record a user’s actions and then translate those actions into code. Then the user could run, examine, and adapt the resulting code.

I would be highly surprised if Airtable wasn’t working on their own scripting/automation processes for their platform — at least for some of the most common use-cases. This seems like what the “button field” is leading towards in the future — building automations into the platform in a very user-friendly manner. I’m super-excited about this new button field, and what it means for the future.

The Airtable people are smart enough to realize that 99% of their users don’t know how to write JavaScripts & don’t want to learn JavaScript. Their users are not coders, which is why the entire Airtable platform is based on simplicity & ease-of-use. Airtable’s user-friendliness is why so many people love Airtable so much, and what makes Airtable so much fun to use!

Airtable is a joy and a delight!

1 Like

@kuovonne, @ScottWorld - I’ve tried to respond to your comments but apparently I’ve been banned from the community - everything I send is rejected as inappropriate, even direct messages.

I think that posts getting hidden or “waiting for moderator review” is an automated thing that happens occasionally… not sure why… maybe some keywords or external URLs that trigger it. Those posts almost always seem to get approved by a moderator later.

I’ve seen those - this one seems different:

Your post was flagged as inappropriate : the community feels it is offensive, abusive, or a violation of our community guidelines.

Sorry to beat this dead horse, but… @ScottWorld asked for examples and yet another - one that I did not anticipate - has emerged in the past 24 hours. :wink:

Throughout this thread, many have questioned my disdain concerning the nature of formula fields (and now button fields). Here’s another example of dysfunction where a user who is blocked from a natural and intuitive approach for placing a link into a PDF document that is actually clickable.

If formulas could be expressed as attributes of [real] fields, this challenging issue would not exist and the user would not be forced to write script to achieve his objectives.

There are many examples in the forum where roadblocks like this one have been documented and in almost every case, they stem from the underlying design choice concerning hybrid fields.

The pattern we see in forum threads like this one is common and almost always related to the unanticipated constraints of formula fields. Soon we may see a similar pattern emerge with button fields as we encounter the unpredictable roadblocks that hybrid fields make possible.

Where would you put formulas that rely on the values of multiple fields?
Such formulas need record level scope, and cannot be stored in the field definition of one of its underlying fields.

I would like to see more robust formatting options, which might be similar to want you want, but from a different perspective. For example, URL fields could have two values: a label and a url, then it could have formatting options to display the label, the url, both (in markdown format), clickable button, or any combination of the above.

You sort’a answered your own question. :wink:

A field with a formula [attribute] is architecturally no different than a field with a formatting attribute. The formula would have the ability to reference other fields and in the context of the current record - i.e.,

thisRow.Value = thisRow.{fieldName1} + thisRow.{fieldName2}

With that, you should also be able to envision …

thisRow.Value = getRecord(<recordID>).{fieldName1} * thisRow.{fieldName2}

And by logical extension …

thisRow.Value = getTable(<tablelName>).lookupRecord(<fieldName>, thisRow.{fieldName1}) * thisRow.{fieldName2}

Formulas applied to any given scope – column or record should be possible.

Indeed, and that’s why any formula defined at the field level must also have dynamic access to record-level data.

A formatting option doesn’t have to be aware of any values other than its own. Plus, I’m okay with Airtable controlling all the formatting options in the field definition. (For example, more field formatting options could include cell color/border, text color/size/font, etc all controlled by formulas. But, the user interface could get ugly really fast if Airtable gives users too much control.)

I’m having a hard time wrapping my brain around this, can you walk me through it?

Could you give an example of where you would store this formula? Would it be in the field definition for {fieldName1}, {fieldName2}, or somewhere else? (Under the current architecture, it is stored in the definition of a formula field.)

Where would you put the formula thisRow.{fieldName1} + thisRow.{fieldName2} in the user interface? (Under the current architecture, it is accessed by customizing the formula field.)

Where would you put the output of the formula, thisRow.Value, in the user interface? (Under the current architecture, it displayed in a cell in the grid view.)

How would you access them in the API’s? (Under the current architecture, only the output of the formula is accessible, and it is a read-only value that can be read like any other field. Well, whether or not the formula is valid is accessible, but that’s a pretty tiny bit of info.)

Right-click (field name) -> Edit Formula

In each cell for the same field; just the same as a current Formula Field. The only difference here is that a field doesn’t need to be a special “formula field” for it to perform formulaic computations. This makes it very easy to perform intelligent computations such as if there’s a value in this cell, don’t change it, but if empty, apply the formula. Most important, it’s a data field not unlike any other field except that it can be populated through formulaic methods.

No, but here’s three observations that might help…

  1. We’re not Airtable engineers so any attempt to design the underlying architecture for something that is likely a distant thought for the team is presumptive on my part and largely wasted energy for all of us.
  2. Hybrid [formula] fields have well-documented constraints and they should probably be addressed [eventually]; how exactly, is anyone’s guess. But, continuing with a design pattern that has caused an equal amount of anguish as it has happiness, is probably not wise.
  3. Take a close look at the status-quo of spreadsheet formulas - array-formulas specifically - in Google Sheets and Microsoft Excel vernaculars. Also, play with a table and the formula architecture over on rhymes with Hoda. :wink:

But which field name would you right-click? {fieldName1}, {fieldName2}, or something else?

I can see how this would be useful and would reduce the number of fields in a table. However, I’m not sure that I would like this in the basic grid view. It could easily cause user confusion.

The array formulas in the spreadsheets you linked to are intriguing. I’m not sure how that translates to a relational database model, though. I need to take a look at Hoda, but I just don’t have nearly enough time right now.

I think that the lines between data and business logic are getting very blurry.

In the recent examples presented, field1 and field2 are not the fields where the formula exists; rather, they are fields referenced BY the formula.

Wherever there is a field, I think it makes sense to have the ability to apply attributes, and attributes should include many things that fields are capable of including but not limited to:

  • Field name
  • Description
  • Data type
  • Conditional and default formatting
  • Grid validation rules
  • Form validation rules
  • Security rules
  • Formula

Formulas - like all field attributes - should affect the field whose attributes they are defined in. Ergo, while you might use a syntax such as:

thisRow.thisCell = [expression]

You might also want a shorthand version of the syntax:

= [expression]

Ha ha - yes, given the current limitations of field formulas, it is very different. But I think you’re getting hamstrung by my lack of expressing this idea more simply.

Let’s try these words to express my idea …

Imagine everything that Formula Fields do now - but in the context of actual fields. Why is that confusing to users? They write a formula that populates every cell in the column. It’s a real field with a real value whose value is [OPTIONLLY or CONDITIONALLY] determined by an expression. What is so difficult to understand about this concept?

In the case of this example where the user simply wants to construct a cell value containing a fabricated URL - AND HAVE THAT VALUE WORK IN A PDF EXPORT - (and ALL OTHER CONTEXTS where fields now work) the solution would be this simple:

= {DomainName} & "/" & {docPath}

And to be thorough, the user did exactly this and expected the formula field to participate in the exported PDF document just as all other fields can participate and function as intended. Instead, he must go around the barn to effectuate a solution - he has to become a script author. That’s just dumb.

My observation is that hybrid fields (such as formula fields) are discriminated against by the underlying platform for reasons that we don’t understand nor should we care. That discrimination seems to occur because they aren’t really fields; they’re something other than a field in the truest definition. And because of this, they are barred from enjoying the first-class citizenry of actual fields. This - to me - is far more confusing for new Airtable users than the idea that actual fields could be influenced by a formula.

And this is typically one of the first stunning revelations for new users who come to Airtable from a spreadsheet background because it requires - wait for it - more fields than have ever been required in any other data-centric solution. It prohibits users from taking an existing column (i.e., field) and embellishing it with data through expressions.

This is about freeing formulas to exist in any field context. Button fields – I assert – are simply an extended pattern of this architectural discrimination. :wink:

[2ND TO LAST POST: Yet another reason formula fields create issues and confusion and some added cost]

[LAST POST: Thanks to all who have endured this narrative. Will someone in power, please - for the love of humanity - close this thread!] :upside_down_face:

Thank you for the detailed explanation. I think that I finally understand. Thanks for holding out until I could reach this point A text field could have an underlying formula. A url field could have an underlying formula. A number field could have an underlying formula. Thus, there would not need to be a separate formula field (at least in theory). I don’t completely agree with you, but at least I think I understand now.