Filter by date "on or before today"

[Frank Harrison]

Hi

I’ve just started with Tap Forms and hoping it will replace Bento. So far I’ve just bought the Mac version, and have hit one problem:

I need to be able to filter by a date field, using date <= today. So whenever I open it, I can see entries with dates set to today or in the past. Is there a way to do that? I can only seem to set it to on or before a specific date.

Also, any idea when your next update will come? I’m on Mavericks so looking forward to that!

Many thanks,

Frank

[Brendan]

Hi Frank,

I’m submitting version 2.0 to Apple for review at the end of this month. So in just a few days.

Thanks for the feature request for the <= today function. That is something I will put in for a future update, but it's not there now. You can search for all records that are today, but not <= today at the moment. Thanks! Brendan

[Frank Harrison]

Thanks Brendan. One other thing I used to use a lot in Bento was showing all entries that don’t have that date field set, which would be great to have too – could be just an “is empty” option on the date field search drop-down.

Just to give you some context, I’m a musician and have been using Bento to keep track of venues I need to contact. So I have one field for “contact on”. Then I can easily see who I’m due to contact by showing any entry that has “contact on” set to today or in the past. Then I had another saved search using “contact on”=empty, so I could see which venues I’ve not set a contact date for yet.

I’m guessing there are other users who might find this useful but thought I’d give you at least one use case!

Thanks, Frank

[Brendan]

Hi Frank,

This would definitely be useful. It’s a bit tricky for me because with Tap Forms I am using a search engine which makes it difficult to find out that something isn’t in the search engine’s tables. At the very least it would have to revert to querying the database itself and not the search engine, thus slowing down the query.

Thanks!

Brendan

[Frank Harrison]

Ah that’s a shame. Well at least being able to show date <= today would probably be enough for this to work for me. Hope you’re able to fit that in!

[John Martin]

There is a reasonably straightforward workaround.

You’ve already got a “Contact On” date field. Create an additional calculation field (named “Days to Contact”) for the number of days to the “Contact On” date, using this formula:

Days to Contact = ( [Contact On] – $now ) / 60 / 60 / 24

This calculates the difference between “now” and the “Contact On” date in seconds, and then converts it to days.

Use the calculation field for the criteria for the saved searches you need. When the “Contact On” field is empty, the “Days to Contact” field calculates as a very large negative number (-16,043 in my test form). So to find records with empty “Contact On” fields, create a saved search with a criterion of:

Days to Contact | is less than or equal to | -1000

(or some similarly large negative number).

To list due contacts, create a saved search with two criteria:

Days to Contact | is less than or equal to | 0
Days to Contact | is greater than| -1000

The second criterion excludes records with an empty “Contact On” field.

This seems to work pretty well in the small test form I created, and is actually pretty flexible. Hope it helps.

I am also a Bento convert, and find Tap Forms can meet all my needs with a few workarounds like the above. The only feature I miss is string concatenation.

— John Martin

[Author]

Posts