Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Interface Number Element - Field Summary Not Available

Solved
Jump to Solution
2613 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Joanna_Parker
6 - Interface Innovator
6 - Interface Innovator

So, I have some columns in one of my bases that are complex formulas working something out based on year. These particular formulas are necessary as there is no quick workaround via rollups etc., it's not pretty - and there is something better involving linked records in the works - but they are doing the job for now. 

Each of these columns show the data for one calendar year, and on an interface I used a number element to sum the column. This way a member of my team has a tidy way of seeing numbers for previous years (each year column has its own number element).

They are all exactly the same column, with the same formula (adjusted for dates), with no options for formatting. I've been able to make four number elements using these going back to 2018.

At the start of the year, I duplicated the 2021 column (which has a number element), and adjusted the formula to cover 2022. But this new column is not available to select within the number element Field Summary field list.

Does anyone have any clue why this would be? 

This is still needed to plug the gaps until the linked record solution is up and running, so any thoughts would be much appreciated.

1 Solution

Accepted Solutions

Yeap sure, I've pasted it below!  I basically just modified the last two lines of "", "Error - Check" to show `0` instead, making that formula field output only numbers

I believe you can also duplicate the base to access the formulas too if you'd like

 

IF(
  AND(
    YEAR({Start Date})=2019,
    YEAR({End Date})=2019
  ),
  DATETIME_DIFF({End Date},{Start Date},"days"),

  IF(
    AND(
      YEAR({Start Date})<2019,
      YEAR({End Date})>2019
    ),
    DATETIME_DIFF(
      DATETIME_PARSE("31 Dec 19"),
      DATETIME_PARSE("01 Jan 19"),
      "days"
    ),

    IF(
      AND(
        YEAR({Start Date})=2019,
        YEAR({End Date})>2019
      ),
      DATETIME_DIFF(
        DATETIME_PARSE("31 Dec 19"),
        {Start Date},"days"
      ),

      IF(
        AND(
          YEAR({Start Date})<2019,
          YEAR({End Date})=2019
        ),
        DATETIME_DIFF(
          {End Date},
          DATETIME_PARSE("01 Jan 19"),
          "days"
        ),
        IF(
          OR(
            YEAR({Start Date})>2019,
            YEAR({End Date})<2019
          ),
          0,
          0
        )
      )
    )
  )
)

 

 

See Solution in Thread

7 Replies 7

Intruiging! Any chance you could share some screenshots of the formula columns, as well as the available columns to select from when setting up the Interface Number element?

Joanna_Parker
6 - Interface Innovator
6 - Interface Innovator

The formula columns are using the formula I mentioned in this post here: Solved: Conditional DATETIMEDIFF - Airtable Community

In terms of formatting, they all show this:

Joanna_Parker_0-1673349582968.png

The available columns to select show all of the previous four years except the most recent duplicate column. But they're all exactly the same.

That's really interesting.  With the formula you provided in the other thread, I'm actually unable to use "Sum" for the Number element in the Interface unless I modify your formula to only output numbers

Screenshot 2023-01-12 at 2.48.05 PM.png

I was also able to select the year 22 for the number element

Here's a link to my the base where I was able to select 22 but not able to sum it up unless I modified your formula; perhaps it'll be useful in some form for comparison?

Wish I could help more!

So weird! Are you able to paste your modified formula please?

I think I may just have to chalk this up to a random error... motivation to get the other solution up and running pronto!

Yeap sure, I've pasted it below!  I basically just modified the last two lines of "", "Error - Check" to show `0` instead, making that formula field output only numbers

I believe you can also duplicate the base to access the formulas too if you'd like

 

IF(
  AND(
    YEAR({Start Date})=2019,
    YEAR({End Date})=2019
  ),
  DATETIME_DIFF({End Date},{Start Date},"days"),

  IF(
    AND(
      YEAR({Start Date})<2019,
      YEAR({End Date})>2019
    ),
    DATETIME_DIFF(
      DATETIME_PARSE("31 Dec 19"),
      DATETIME_PARSE("01 Jan 19"),
      "days"
    ),

    IF(
      AND(
        YEAR({Start Date})=2019,
        YEAR({End Date})>2019
      ),
      DATETIME_DIFF(
        DATETIME_PARSE("31 Dec 19"),
        {Start Date},"days"
      ),

      IF(
        AND(
          YEAR({Start Date})<2019,
          YEAR({End Date})=2019
        ),
        DATETIME_DIFF(
          {End Date},
          DATETIME_PARSE("01 Jan 19"),
          "days"
        ),
        IF(
          OR(
            YEAR({Start Date})>2019,
            YEAR({End Date})<2019
          ),
          0,
          0
        )
      )
    )
  )
)

 

 

Woohoo, it worked! Had to find all of the "" that I had done.. but is doing the job now. Thanks so much 🙂

Glad I could help!