Showing ideas with label formulas and calculated fields.
Show all ideas
Submitted on
Nov 07, 2020
05:10 AM
Submitted by
surveyorsoffice
on
Nov 07, 2020
05:10 AM

I know airtable can’t combine & or filters & I can get over it with a formula is there a reason why there could not be a " but "option ie xx is ff & bb is ee but cc i snot dd
... View more
Submitted on
Sep 21, 2020
09:32 AM
Submitted by
David_Roulin
on
Sep 21, 2020
09:32 AM

Hey there, In the group header cells (aka “Summary cell”) for numbers we have
Sum
Average
Median
Min
Max
Range
Standard Deviation etc
But it would be useful to also have “Product”
... View more
Submitted on
Sep 21, 2020
09:40 AM
Submitted by
David_Roulin
on
Sep 21, 2020
09:40 AM

The group header cells, or “Summary cells” have
Sum
Average
Median
Min
Max etc etc
What about adding a “Custom function” option that would allow to enter a function such as the ones you can find in C# Linq? (see for instance Enumerable.Aggregate or Enumerable.ForEach)
Admittedly this is a huge amount of work. But I thought I would suggest it, see who might need that too.
... View more
Submitted on
Aug 20, 2020
10:39 AM
Submitted by
Paul_Warren
on
Aug 20, 2020
10:39 AM

Is it possible to access the color of a single select field in a formula? This would be a huge help when creating complex color schemes. So far, simply coloring a view by the single select field has never achieved my requirements. I typically want to have the flexibility to add conditional colors on top of the single select colors. If I could directly access the colors of a single select field, that would significantly simplify the formulas I currently have to use to color these views. I became curious when I noticed automations are able to access the color property of a single select field. Below is an image of the color property. If this is not already available, please consider adding it!
... View more
Submitted on
May 27, 2020
03:14 PM
Submitted by
Daniel_Wolcott
on
May 27, 2020
03:14 PM

It would be nice to input formulas and or be less restrictive in the Filter function. I am trying to filter out anniversaries and birthdays by the current month so that I can send out mailers. Right now, I have to create a separate column with a function that pulls out the month and then I have to go into filter and change the filter to the respective month each month. It would be nice to just put a filter of "is…MONTH(TODAY()) and it would automatically update each month as well as I would be able to avoid having to create a separate column to accomplish this task.
... View more
Submitted on
May 24, 2020
07:08 AM
Submitted by
Michael_Morris2
on
May 24, 2020
07:08 AM

I didn’t know how to say this concisely in the subject line. It’s nice that groups have the options for sum, average, etc of that group. However, I’d like to be able to change the calculation of the entire view separately from the groups. Let me give an example as I know that wasn’t clear. I keep track of time spent and the associated hourly rate. I have 3 views that group my times by each day, each week, and each month. This way I can see at a glance if I’ve met my daily, weekly, and monthly goals. For example, in the monthly view, each week is grouped and I can see the total (sum) of my hours per group. At the bottom of the page, it also sums the total of all the groups combined. Makes sense. However, If I want to see the average time spent per month, when I change the bottom calculation to average, the individual groups change to average as well. So now the groups show my average number of hours per day and of course the bottom calculation averages my daily averages. I’d like to be able to keep my groups set to sum and only the overall calculation changed to average. This way my groups add up total number of hours, then I can see an average of these totals. Sorry for such a long post, I just couldn’t think of a better way to explain that.
... View more
Submitted on
May 16, 2020
10:25 AM
Submitted by
ScottWorld
on
May 16, 2020
10:25 AM

It would be awesome if Airtable’s formula engine could recognize “curly quotes” as the same thing as "straight quotes". When editing formulas on mobile web browsers, curly quotes are the default. And, when copying and pasting formulas that have curly quotes from other places — such as this forum, which changes quotes to curly quotes outside of a code block, or rich-text editing apps which change quotes — the formulas won’t work upon pasting them into Airtable. There have been multiple times where I have been scratching my head in confusion over a “seemingly-perfect formula” that doesn’t work properly, but it is actually failing because the quotes are curly.
... View more
Submitted on
Apr 30, 2020
09:26 AM
Submitted by
ScottWorld
on
Apr 30, 2020
09:26 AM

So this is strange… In Airtable’s formula field reference, several of the examples refer to fields that don’t exist anywhere within the example itself. For example, check out this example that was given for the WORKDAY_DIFF function: WORKDAY_DIFF({Assignment date}, {Due Date}, '2017-09-04, 2017-10-09, 2017-11-10') => 8 Okay, that’s great that the formula results in 8, but we can’t truly understand the formula if we don’t know what the values are for “Assignment Date” & “Due Date”. Interestingly, we also can’t tell whether the dates are in YYYY-MM-DD format or YYYY-DD-MM format. Yes, we can type up this formula in our own database and play with it ourselves, but if we’re just reading through the formula field reference, it leaves more questions than it answers. For example, my immediate question about this formula is whether this formula includes the start date & the end date as part of its “count”. Same problem with this function: IS_SAME({Date 1}, {Date 2}, 'hour') => 0 Huh? Why does this result in 0? What are Date 1 and Date 2? And this function: WEEKNUM({Date}) => 46 Why does this result in 46? What was the original date? And this function: WORKDAY({Launch date}, 100, '2017-09-04, 2017-10-09, 2017-11-10') => 6/20/2017 Why does this result in 6/20/2017? What was the Launch Date? But there are many more examples — just like these — in the formula field reference. We can’t truly understand the functions properly from the formula field reference unless we understand which inputs you’re putting into the functions. Yes, I get it that we can just play around with these functions ourselves in our own tables and figure this all out on our own, but it would be really nice to have a fully fleshed-out reference guide that we can fully comprehend.
... View more
Submitted on
Apr 29, 2020
09:55 AM
Submitted by
openside
on
Apr 29, 2020
09:55 AM

There are discrepencies with Rich Text content causing issues. If I copy/paste a rich text field into another rich text field, then create a formula that checks to see if they are equal: IF({Rich Field 1} = {Rich Field 2}, "SAME", "NOT SAME"), then it shows these as being equal. But if I take the content from {Rich Field 1} via the API, and perform an update via API to {Rich Field 2}, the formula comparison says they are NOT SAME. Even though if I compare them from the API they have the exact same values. So something not working when pushing via API to reflect same underlying content.
... View more
Submitted on
Mar 03, 2020
03:32 AM
Submitted by
kuovonne
on
Mar 03, 2020
03:32 AM

Currently, when you delete a field, it breaks any formulas that referred to that field. When the user views the broken formula, the field name is replaced by a non-human-readable string.
Thanks to the scripting API, we can now see that the non-human-readable string in the broken formula is a concatenation of a literal string and the field id of the deleted field
"column_value_" & fieldIdOfDeletedField
I suggest that the string also concatenate the name of the deleted field
"column_value_" &
fieldIdOfDeletedField &
"_" &
ENCODE_URL_COMPONENT(fieldNameOfDeletedField)
This would make fixing broken formulas much easier because the user could see what the original formula was.
The scripting API shows that the field definition for a formula field stores an array of referencedFieldIds. Could that array also somehow store referencedFieldNames? After all, the field definition for a multipleRecordLinks stores both ids and names.
And now for an example …
Say the field {dog age in human year} had the formula {dog age in calendar years} * 7
If the field {dog age in calendar years} is deleted, the broken formula now looks like {column_value_fldFRA5nV1QBtrtLi} * 7
I would like the broken formula to look like
{column_value_fldFRA5nV1QBtrtLi_dog+age+in+calendar+years} * 7
or {column_value_fldFRA5nV1QBtrtLi_dog%20age%20in%20calendar%20years} * 7
By the way, for all you dog lovers out there, I know this formula is not really accurate. I am just using it to illustrate a point.
... View more