Attempting to calculate the value of formula columns from the API. Can you help?

monday Apps & Developers Feature Requests - API and Apps Framework
, , , , ,
1

I’m looking to get the value of a column of type formula via the API.

And I’ve seen that others have already asked similar questions, so I’ll summarise

If I query like so:

 query getItems {
  items (ids: [ "xxxxx" ]) {
    name
    id     
    column_values {
      id
      text
      type
    }
    
    board {
      id
      name
      columns {
        id
        title
        type
        settings_str
      }
    }
  }
}

I can see that column_values for formula columns are blank:

  {
    "id": "formula",
    "text": "",
    "type": "formula"
  },

Using the settings_str field in board columns, I see the formula:

{
  "id": "formula",
  "title": "Formula",
  "type": "formula",
  "settings_str": "{\"formula\":\"SUM({numbers},{numbers0})\"}"
}

When JSON.parse(d) we get:

{
  "formula": "SUM({numbers},{numbers0})"
}

Reading around…

…it appears that for formula columns, the column values are populated client side.

So somewhere there is some client-side code that knows monday’s formula language and given an item, can find all the formula columns within that item, and populate the correct formula values in the formula columns.

:point_right: Rather than each developer having to reverse engineer this logic, is there something that we could all share?

I’m particularly looking at people from monday.com for an answer here.

This really makes sense when monday add new formulae and then everybody else has to scramble to update/reverse engineer the new formulae. Just bumping to the new version of a library would be awesome.

:eyes: @Matias.Monday

2 Likes
2

I fully support this. It doesn’t make sense that each dev team needs to reverse-engineer the formula column to come up with a solution that then likely only works in 80% of the cases.

1 Like
3

+1
This has been a big issue for many of monday.com app developers. It would be helpful if someone from monday chimes in and share if its a WIP or anything planned. so, we don’t overcommit to the workarounds.

1 Like
4

I see that there is also an open feature request for getting the values in the api:

5

Hi, Formula columns values are not saved in Monday… they are calculated by the browser/front-end device when the user is viewing the data… this means you will not be able to get the value via API calls.
For the past couple of years there have been requests for Monday to move Formula in to columns that can be accessed, and this may happen at a later stage in the movement to the MondayDB architecture, but is currently not available to API.
*** best workarounds calculate the formula in your own code/Make.com processes… or use a Marketplace app like General Caster to calculate a formula and push the result to a text/number/date column etc.

6

Hi @MHaigh, yeah this is really the point I’m making here.

If it’s all client-side, then if monday can make the formula code available as a library, countless developers will not be doing “busy work” replicating this client side logic

7

Hello there!

I have sent this to our team to see if that is something that can be done (sharing the script):grin:

In the meantime I have turned this into a request (since it would be different from just getting the result via API). You can vote at the top left corner of the screen!

Cheers,
Matias

1 Like
10

Hello again @dvdsmpsn,

I checked this with the team and there are no plans of making that script publicly available.

Cheers,
Matias

11

Hi @Matias.Monday, thanks for the reply. It’s not what I hoped for, but hey.

Following on from this, I’m now looking at rolling my own formula support.

Would it be possible to get the documentation on formulas updated? As it looks very inconsistent.

Or is there another place that has the authoritative?

Dates

For example, dates appear to be displayed in a variety of formats, depending on the formula:

Formula      Description                                 Example                     Output
------------------------------------------------------------------------------------------------------
DATE         Returns the value for the given parameters  DATE(2018,5,30)             Wed May 30 2018
FORMAT_DATE  Returns a formatted date                    FORMAT_DATE("2020-02-16")   Feb 16, 2020
TODAY        Gets the current date                       TODAY()                     05.07.2018

Though it looks like FORMAT_DATE can specify the format as required.

  • What is the actual format that these produce?
  • Is it mean to be consistent between formulas?
  • Is it based off the end user’s locale?

Unfortunately, none of this is described in the documentation.

Booleans

Also, TRUE and FALSE appear to be up for interpretation:

Formula    Description                                   Example                     Output
------------------------------------------------------------------------------------------------------
AND        Checks if all the given logical conditions    AND(3>1, 4>2).              True
           are true and if so returns true
EXACT      Compares two values, returns true if they     EXACT({Status},{Status}))   TRUE
           are the same and false if they are different
           (Also, note the typo in the example)
FALSE      Logical false value                           IF(3>5 = “FALSE”, “a”, “b”) “a”

I’m seeing TRUE, True and true, as well as “FALSE” (a string, in speech marks).

Also, all the speech marks should be replaced with " for consistency.

That’s just what I’ve noticed. Are there any others?

12

Hey @dvdsmpsn,

I just submitted a request to have someone take a look at that article! :slight_smile:

Best,
Rachel

1 Like
14

@rachelatmonday See also ISOWEEKNUM:

Function: ISOWEEKNUM
Description: Returns the yearly week number of a given date according to ISO standards.
Example: WEEKNUM({created at}) => 42

Surely this should be:
Example: ISOWEEKNUM({created at}) => 41

…as I think that ISOWEEKNUM starts at zero.

One way to ensure that the documentation page is correct would be to build a test harness for the formulas, similar to what I’ve built here.

15

David, this has been a long standing issue I called out over a year ago to support and it got ignored because they can’t reproduce it, in Israel, because the issue only occurs if you’re in UTC-0 to UTC-12 time zones. Change Windows to use CET and it behaves as expected.

At least that’s how it was the last time I tested.

1 Like
16

Hey folks, just updating here that this feature is ON THE ROADMAP!

The big mondayDB changes that we made this year laid the foundations for bringing formula support into the API. Our current plan is to release it this year.

Of course timelines can shift – but we’re on it!!!

4 Likes
17

@dipro :raised_hands: :fire: :unicorn: :rainbow:

1 Like
19

Reading the formula column is now supported via the API!

You can get the calculated value by querying the display_value field on the FormulaValue type. This field is available on API version 2025-01 and later.

Here’s an example:

query {
  boards (ids:116807194) {
    items_page (limit:50) {
      items {
        id
        column_values (ids:["mins"]) {
          ...on FormulaValue {
            display_value
          }
        }
      }
    }
  }
}

Note that querying this field does have rate limits as it’s a computationally intensive request:

  • Up to 10,000 formula values per minute
  • Maximum of five formula columns in one request
  • Does not support formulas with mirror columns
1 Like
20

Formula PRO on the monday.com marketplace is seamlessly integrated with this new API to supercharge your native formula columns. It also fully supports all native formula functions, so you don’t need to learn new syntax to start using Formula PRO.

With Formula PRO, you can unlock powerful features like projecting and writing results from native formula columns to other columns (including some not supported by the native formula column), as well as to mirror columns and subitems.

Additionally, Formula PRO includes 40 automation recipes, with new ones added bi-weekly.

:rocket: Formula PRO: Advanced Automations for native monday formula columns, Mirror Columns & Subitems!