Today/Tomorrow formula

[Jesper_Holmstro]

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.

[Andy_Lin1 · Accepted solution]

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.

[Jesper_Holmstro]

Thanks Andy!

[Jesper_Holmstro]

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"?

[Andy_Lin1]

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:

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

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

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]

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

[Jesper_Holmstro]

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.

[Andy_Lin1]

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 ).

[Jesper_Holmstro]

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.