New Formula Field Functions

[Jeremy_Oglesby]

There are several requests floating around the forums related to the need/desire for more formula field functions - mostly related to arrays, linked records, or javascript like functionality.

I’m going to create this #feature-requests post with some of the initial ones that I have posted myself, or have seen in the forums, and I will endeavor to keep it updated with new ones that I find or see posted; and, I will even endeavor to cross them off the list as Airtable implements them :winking_face:

To keep the format clean, I’m going to make a section that lists just the function requested and a brief description of functionality - I think the intention for use and the use-cases should be clear by the structure of the function. This will be followed by a section with links to some of the posts I have seen addressing the need/desire for some of these functions.

Airtable users - If you have an idea for a function that is not currently in the list, post it as a reply here and I will try to add it to the list in this OP if the intention is clear and the functionality seems feasible.

@Airtable_Support, @Airtable_Team, @Katherine_Duh, @Howie, @Kasra - please consider :grinning_face_with_big_eyes:

Functions List

LOOKUP(table, field, linked-records-field)

• Lookup field functionality, but can be evaluated within a formula field

ROLLUP(table, field, ROLLUP-FUNCTION())

• Rollup field functionality, but can be evaluated within a formula field

ARRAY_DIFF(array 1, array 2)

• Takes two arrays (or strings of objects with a separator that can easily be converted to an array) - returns {array 3} which contains all objects from {array 1} that do not also exist in {array 2} (ie, subtract {array 2} from {array 1})

ARRAY_EACH(array, variable, FUNCTION())

• Takes an array and a compatible Function - loops over the array and for each object, saves the object in the variable, and allows the user to use the variable in the chosen, compatible, string function (such as SUBSTITUTE(), or FIND()) or in logical Functions (such as IF(), OR(), AND()); works like a JS for loop

ARRAY_INTERSECT(array 1, array 2, array 3, ...)

• Takes any number of arrays (or strings of objects with a separator that can easily be converted to an array) - returns an array with only objects that exist in every array provided to the function

Links List

Formula Fields: Values Based On Javascript Functions Product Suggestions

This one might be a little bit “out there,” but I think that if it were possible it might address a number of requests for additional functions supported by Airtable’s Formula Fields… I’m wondering if it might be possible support Formula Fields whose values are based on the result of some ad-hoc Javascript function. For example, the user might specify other Airtable fields as the Javascript function’s params, specify the JS function itself, and have the Formula Field’s value reflect the result. …

Array intersect formula Product Suggestions

I have 2 tables, and I need a result that shows only inputs that are the same in both tables. Hence, to compute the intersection of a number of arrays. array intersect ( array1, array2 [, array#… ] ) Returns an array containing all the values of array1 that are present in all the arguments. Where: array1 The array with master values to check. array2 An array to compare values against. … A variable list of arrays to compare. Any workarounds at this point?

Comparing two arrays and remove values that appear in both Product Suggestions

Hi airtable! I have a list (an array) of items in two columns. Each row represents two different lists (one in each column). I’d like to be able to compare the two columns and remove what is common in both. For example Row 1: Column 1: Apple, Orange, Pineapple, Kiwi Column 2: Apple, Kiwi Result: Column 3: Orange, Pineapple Row 2: Column 1: Banana, Orange, Grape, Kiwi, Pineapple, Passion Fruit Column 2: Orange, Grape, Pineapple Result: Column 3: Banana, Kiwi, Passion Fruit etc. The …

Look-Up from a Look-Up Field Product Suggestions

A very useful feature to have would be the ability to “drill-down” another level when working with Look-Up fields. We use AirTable to manage our company E-mail accounts and Distribution Lists (Distribution Lists are email addresses that contain members and distribute messages to all who are assigned to it, i.e. info@company.com can go to John, Mary, and Paul). Here is the basic set-up between 3 tables: Table PEOPLE contains a list of all employees, and links to EMAIL which contains all email a…

How can I make a lookup field show only unique results? Ask the community

The result I am looking for is similar to the ARRAYUNIQUE function for a Rollup field. However, the results are a link to another table, and it seems that using a rollup can only show text results as opposed to clickable records. How can I make lookup field show only unique results, or make the unique results of a rollup field clickable?

Dynamic (calculated) link to a record in a linked table Ask the community

How to create a dynamic (calculated) link to another table? There is a Table#1 (Daily Expenses) [ID, Date, Sum, subject,MonthNo]. Where MonthNo=MONTH({Date}) There is a Table#2 (Months of the year) just as follows: MonthNo MonthName 1 January 2 February 3 March etc. Linking Table#1 to Table#2 will allow me to calculate monthly expenses directly in a Table#2 by means of Lookup(Sum) function. How can I do this? If I choose a standard Link field then every time later I will have to select…

COUNTIF AND COUNTIFS Formulas Product Suggestions

It would be great if the COUNTIF and the COUNTIFS formulas were implemented in Airtable. I use them in most of my excel spreadsheets, and it is a little frustrating not being able to use it here. Are there any plans to add more formulas?

Support lookups and references/links within formulas Product Suggestions

First off, Airtable is great! One thing though: I’d really like to not have to create additional columns in order to simply do something like the following: Formula: if(lookup(row_10,"column_1"),lookup(row_12,"column_4"),lookup(row_53, "column_2")) Also, I have a lot of varied data and I’m trying to use one big table for everything with lots of different views rather than use lots of different tables with aggregate tables and views based on those smaller tables. I can link to a row within the…

[W_Vann_Hall]

Jeremy_Oglesby:

ROLLUP(table, field, ROLLUP-FUNCTION())

• Rollup field functionality, but can be evaluated within a formula field

Remember you can essentially write a formula within the rollup aggregation function window; simply use the keyword 'values' to reference the rolled-up field. The one relatively constraining limitation is that you can’t address directly more than one rolled-up value, which presumably your suggested enhancement could.

[Jeremy_Oglesby]

Exactly - the ability to reference multiple Rollups within a formula would be nice, but also, I think that to make it compatible with the ARRAY_EACH() function being proposed, you’d have to be able to address the Rollup within the ARRAY_EACH() function itself - I’m not sure it would work if you just referenced a distinct Rollup field. Maybe I’m not thinking properly about that.

Also, I’ve always had trouble with writing formulas in Rollup fields - perhaps I haven’t been thinking about using 'values' in the right way

[Jeremy_Oglesby]

I’ve lost the ability to edit this post, sadly.
(explanation here)

Until (hopefully) I can edit it again, here’s another addition to the list:

Function

Formula Field Version

PROD(value 1, value 2, value 3, etc...)

Rollup Field Version

PROD(values)

• Takes any number of numerical values and multiplies them together to obtain the product of those values

Reference Link

PRODUCT rollup aggregation function Product Suggestions

I’d like to be able to take the product (i.e. multiply all numbers together) of all associated values in a Rollup field. I need this because I’m trying to aggregate a bunch of independent probabilities together. Is there some way to do this already that I’m missing?

[Jeremy_Oglesby]

@Airtable,

I just got done writing a concatenation formula to create every date between a User entered {Start Date} and {End Date}, with support for up to 1 year (365 days) difference between start and end… :confounded:

IF(
  AND({Cancelled On},{Cncltn Recorded By}),
  BLANK(),
  DATETIME_FORMAT({Start Date},'l') &
  "," &
  DATETIME_FORMAT({End Date},'l') &
  IF(
    {Length in Days} > 1,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        1,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 2,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        2,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 3,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        3,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 4,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        4,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 5,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        5,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 6,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        6,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 7,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        7,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 8,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        8,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 9,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        9,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 10,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        10,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 11,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        11,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 12,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        12,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 13,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        13,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 14,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        14,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 15,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        15,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 16,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        16,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 17,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        17,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 18,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        18,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 19,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        19,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 20,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        20,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 21,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        21,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 22,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        22,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 23,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        23,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 24,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        24,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 25,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        25,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 26,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        26,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 27,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        27,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 28,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        28,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 29,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        29,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 30,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        30,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 31,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        31,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 32,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        32,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 33,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        33,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 34,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        34,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 35,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        35,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 36,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        36,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 37,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        37,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 38,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        38,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 39,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        39,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 40,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        40,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 41,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        41,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 42,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        42,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 43,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        43,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 44,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        44,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 45,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        45,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 46,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        46,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 47,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        47,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 48,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        48,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 49,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        49,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 50,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        50,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 51,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        51,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 52,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        52,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 53,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        53,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 54,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        54,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 55,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        55,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 56,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        56,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 57,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        57,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 58,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        58,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 59,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        59,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 60,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        60,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 61,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        61,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 62,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        62,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 63,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        63,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 64,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        64,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 65,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        65,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 66,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        66,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 67,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        67,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 68,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        68,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 69,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        69,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 70,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        70,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 71,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        71,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 72,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        72,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 73,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        73,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 74,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        74,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 75,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        75,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 76,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        76,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 77,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        77,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 78,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        78,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 79,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        79,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 80,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        80,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 81,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        81,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 82,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        82,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 83,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        83,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 84,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        84,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 85,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        85,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 86,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        86,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 87,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        87,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 88,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        88,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 89,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        89,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 90,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        90,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 91,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        91,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 92,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        92,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 93,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        93,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 94,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        94,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 95,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        95,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 96,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        96,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 97,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        97,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 98,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        98,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 99,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        99,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 100,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        100,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 101,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        101,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 102,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        102,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 103,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        103,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 104,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        104,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 105,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        105,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 106,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        106,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 107,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        107,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 108,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        108,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 109,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        109,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 110,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        110,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 111,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        111,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 112,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        112,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 113,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        113,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 114,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        114,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 115,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        115,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 116,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        116,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 117,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        117,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 118,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        118,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 119,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        119,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 120,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        120,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 121,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        121,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 122,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        122,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 123,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        123,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 124,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        124,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 125,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        125,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 126,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        126,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 127,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        127,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 128,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        128,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 129,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        129,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 130,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        130,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 131,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        131,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 132,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        132,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 133,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        133,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 134,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        134,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 135,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        135,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 136,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        136,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 137,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        137,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 138,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        138,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 139,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        139,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 140,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        140,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 141,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        141,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 142,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        142,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 143,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        143,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 144,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        144,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 145,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        145,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 146,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        146,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 147,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        147,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 148,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        148,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 149,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        149,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 150,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        150,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 151,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        151,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 152,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        152,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 153,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        153,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 154,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        154,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 155,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        155,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 156,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        156,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 157,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        157,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 158,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        158,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 159,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        159,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 160,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        160,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 161,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        161,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 162,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        162,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 163,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        163,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 164,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        164,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 165,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        165,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 166,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        166,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 167,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        167,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 168,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        168,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 169,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        169,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 170,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        170,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 171,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        171,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 172,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        172,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 173,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        173,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 174,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        174,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 175,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        175,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 176,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        176,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 177,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        177,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 178,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        178,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 179,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        179,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 180,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        180,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 181,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        181,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 182,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        182,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 183,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        183,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 184,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        184,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 185,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        185,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 186,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        186,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 187,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        187,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 188,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        188,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 189,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        189,
        'day'
      ),
      'l'
    )
  ) &
  IF(
    {Length in Days} > 190,
    "," &
    DATETIME_FORMAT(
      DATEADD(
        {Start Date},
        190,
        'day'
      ),
      'l'
    )
  ) &
  IF(
   ... and on to 364; truncated due to post character limit

fellow forum users, feel free to steal this and reuse if it can help you…

The use case here is a booking system that needs to allow for future reservations of a particular site to be checked against all dates for which that site has existing reservations, to avoid double booking.

I would have killed to have had either one of:

• a function for checking a date against a span of time, given the start and end of the span; perhaps something like:

DATE_IS_IN(dateToCheck, startOfSpan, endOfSpan)

• an ITERATOR and VARIABLES of any sort to be able to automate the formula I had to write by hand

Thank you for considering! Blessings :grinning_face_with_smiling_eyes:

[Jeremy_Oglesby]

Functions

POP(array)

• Returns the last value out of an array (the array most likely being a reference to a Rollup field)

SHIFT(array) .... or ... PULL(array)

• Returns the first value out of an array (the array most likely being a reference to a Rollup field)

Link

Retrieve Specific Value from Most Recent Linked Record Ask the community

I’m wracking my brain trying to figure out how I can make this work. I have an Items table. I have a Locations table. I have a Location Records table that joins the two. An Item has many Location Records, and belongs to a Location through its most recent Location Record, and it has a history of all its past Locations through the sequence of all its Location Records. In the Items table, I can Rollup Location Records–>{Date}–>MAX(values) to pull the {Date} out of the most recent Location Reco…

[Jeremy_Oglesby]

Function

CURRENCY(number, 'currencySymbol')

• Takes a number as either integer, decimal, or field reference, and a currency symbol ('$', '€', '£', etc…); returns the number rounded and formatted as currency

Link

Add a CURRENCY() function for Formula Fields Product Suggestions

Right now, to format values pulled from a Currency field in a Formula field, users have to use something akin to the following formula to get “$65,756” to display as such. Otherwise, AirTable displays it as “65756”, no commas, no currency symbol: IF( LEN(CONCATENATE({Trip Budget}))<4, {Trip Budget}, IF( AND(LEN(CONCATENATE({Trip Budget}))>3,LEN(CONCATENATE({Trip Budget}))<7), LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-3)&","&RIGHT(CONCATENATE({Trip Budget}),3), IF( …

[Andrew_Enright]

Adding live conversation rates to a CURRENCY function would be killer.

[Kamille_Parks]

I would love some sort of DATE_RANGE({Start Date},{End Date},'unit') function to list all the days, months, years, etc. between two given dates. I have a Vacation Planner base where I’m planning several week-long events across a year, and a function like this would come in handy.

[Sean_io]

Jeremy_Oglesby:

LOOKUP(table, field, linked-records-field)

Wow. NEED this so badly.

:frowning:

Is there no way to automatically get the value of a linked records field automatically now? An API populates my table at the moment from orders on my online store and I need to look up shipping costs per shipping method on a linked table. Right now I have to manually copy the shipping method value to the linked record field to get the lookup to work. It’s a pain.

[Justin_Barrett]

Sean_io:

An API populates my table at the moment from orders on my online store and I need to look up shipping costs per shipping method on a linked table. Right now I have to manually copy the shipping method value to the linked record field to get the lookup to work. It’s a pain.

I haven’t yet played with the API, but using Integromat (which uses the API under the hood) to edit Airtable data, I can pass text into a linked record field and it will make the link if it finds a record with a matching name in the primary field. Not sure how much extra work it would take directly with the API to do something similar, but perhaps @Bill.French can offer some insight on this.