Help

Today/Tomorrow formula

Topic Labels: Formulas Integrations
Solved
Jump to Solution
1630 8
cancel
Showing results for 
Search instead for 
Did you mean: 
Jesper_Holmstro
6 - Interface Innovator
6 - Interface Innovator

Hey everyone, have a question about a formula I can't get working correctly.

I only have a date field (Date) and a formula field, with the formula 

Date=TODAY()

The formula returns a 1 for today and 0 if it's another date. So far so good.

What I want is if there is no date that is today, I want it to return 1 for tomorrow and 0 for the rest of the dates until a Today date turns up again.

Is this possible?

Its just for a simple API filtering that shows what rows to read from.

1 Solution

Accepted Solutions
Andy_Lin1
9 - Sun
9 - Sun

The problem you're running into is that Airtable formula fields are not "aware" of what happens in other records – you would need to use a rollup field (which would itself require that you link a single record to every record in the view).

If you're working with the API, I'd probably use

DATETIME_DIFF({Date}, TODAY(), 'days')

Then you filter by >-1 and <2 (you can do this in the API query, set up a view for it, or modify the formula to only return a value if those conditions are true, etc.), getting you records for today and tomorrow.

See Solution in Thread

8 Replies 8
Andy_Lin1
9 - Sun
9 - Sun

The problem you're running into is that Airtable formula fields are not "aware" of what happens in other records – you would need to use a rollup field (which would itself require that you link a single record to every record in the view).

If you're working with the API, I'd probably use

DATETIME_DIFF({Date}, TODAY(), 'days')

Then you filter by >-1 and <2 (you can do this in the API query, set up a view for it, or modify the formula to only return a value if those conditions are true, etc.), getting you records for today and tomorrow.

Thanks Andy!

Do you now how to set up that filtering in the API query? Now we just send the filter by formula 

filterByFormula=Date=TODAY()

How do I write the query that it should only read "0" or "1"?

 

I'm not sure what you're using for your API call, so you might need to escape the formula. I'm using an OR function here with "=0" and "=1", since we're only looking for those two results.

filterByFormula=OR(DATETIME_DIFF(Date, TODAY(), 'd') = 0, DATETIME_DIFF(Date, TODAY(), 'd') = 1)

Here's the full help page (it might not skip you directly to the correct section, but if you search for it on the page, you'll see the entry):
https://support.airtable.com/docs/formula-field-reference#date-and-time-functions

Alternatively, you could retrieve records from a view set up with your criteria (note that you'll want to lock this view to prevent accidental edits). For example, in my table I have a field {Business Day Diff}, which has the formula WORKDAY_DIFF(Date, TODAY(), [a list of holidays]). I can filter on this field, like so:

Andy_Lin1_0-1692199706785.png

However, you can filter directly on your date field, in my case {Publication Date}:

Andy_Lin1_1-1692200062935.png

And you can add a sort so that the entries from today are on top:

Andy_Lin1_2-1692200271411.png

Once you've customized your view to your requirements, you can access it with the API query:

view=viw...

Where the argument is the last part of the view's URL, starting with "viw" and ending before "?blocks=hide".

It comes in handy for cases where you have a more complicated query and are running into the string limit.

Jesper_Holmstro
6 - Interface Innovator
6 - Interface Innovator

Thank you agan @Andy_Lin1 , works like a charm with the API filtering.

Jesper_Holmstro
6 - Interface Innovator
6 - Interface Innovator

Hi again @Andy_Lin1 , do you know if there is a way to get the data from the API in the same order as its shown in Airtable, the only thing I found is sorting by a field? For example If I have fields in the order in Airtable, Name, adress, Phone, but the API returns ion a different order.

Hmm, I've never thought about that. The API should return an object, so it's hard to guarantee an order. If you're using Javascript, you could try a destructuring assignment. I haven't tried it, but it's possible if you specify the fields in the API call, it'll return the fields in the order given in the API call (you can see an example here: https://support.airtable.com/docs/enforcement-of-url-length-limit-for-web-api-requests ).

Yeah I have tried that one with specifying which fields but it still returns the data in another order, I'm not using Javascript, its just a url api call.