Filter last 3 months of record entries

Viewing 7 reply threads
  • Author
    Posts
  • September 16, 2017 at 3:04 PM #24636

    Lorenz Ehrsam
    Participant

    I would like to do a saved search that shows me the records of the last 3 months, (or any number of days really). How do I do that? I know there is an option for last week, last month, last 2 months, but not more than that.

    Thanks,

    Lorenz

    September 16, 2017 at 3:42 PM #24637

    Brendan
    Keymaster

    Hi Lorenz,

    You would have to create a Saved Search that spanned a specific set of dates to do anything more than the Last Month “in the period” comparison operator. You can put in the same date field twice but using “greater than” and “less than” (or the “or equal to” variations), but with different dates. A low date and a high date to bracket the search results by those dates.

    Thanks

    Brendan

    September 16, 2017 at 5:38 PM #24638

    Lorenz Ehrsam
    Participant

    Hi Brendan,

    I did that – but it’s static, in other words I would have to change the period consistently. It would be great to have a function that could specify the last/future x number days and it would dynamically update the search every day.

    Please let me know if that’s possible in the future.

    Thanks,

    Lorenz

    September 16, 2017 at 7:36 PM #24639

    Brendan
    Keymaster

    Everything is possible in the future :)

    September 17, 2017 at 10:14 PM #24653

    Jose Monteiro
    Participant

    Hi Lorenz,
    Hi Brendan,

    I found a really simple solution for what Lorenz need, :)
    Brendan will correct me if I say something wrong or less correct.

    I’ve attached a few files to illustrate what I mean.

    1.
    TapForms keep date data internally in Unix Epoch date format, which is the number of seconds elapsed since January 1, 1970 00.00.00 GMT until the date you have.
    So, for today it will be the number of seconds elapsed until today.

    2.
    On your form you surely have some date field for your records.
    For instance:
    – if an invoice, it will be the date of the invoice;
    – If a track of your spendings, the date you spent that amount of money;
    – …

    3.
    We will be talking about days, since that’s what you asked in your post.
    In the example I’ll show you, I have:
    – A form with 10 records (File 01)
    – A Saved Search for the last seven days. (File 02)

    4. WHAT YOU HAVE TO DO
    Is just to create a Calculation Field with the number of last days you need.
    Suppose:
    -Your date field has the name of MyData.
    -Your calculation field has the name “Last 7 Days records”.
    The formula you need will be (attached file 03).
    You will have to subtract from MyData the number of days in seconds for your search.
    There:
    – 7 is the number of days (for the 3 months it would be 90 days)
    – 86400 is the number of seconds in a day.
    Your Saved Search will be (attached file 04).

    Hope this helps.

    Jose

    Attachments:
    You must be logged in to view attached files.
    September 18, 2017 at 12:34 AM #24660

    Brendan
    Keymaster

    TODAY() is a beta version feature. Use NOW() instead in the production version.

    September 19, 2017 at 6:19 AM #24693

    Lorenz Ehrsam
    Participant

    Thanks so much Jose (and Brendan) for taking the time to clearly explain. That works very well indeed! And this solves any date related calculations for me.

    Thanks again,

    Lorenz

    September 19, 2017 at 9:28 AM #24696

    Brendan
    Keymaster

    It’s always great when customers find things out about Tap Forms that even I didn’t know! :)

    Thanks for this cool tip Jose!

    September 19, 2017 at 9:55 AM #24697

    Jose Monteiro
    Participant

    My pleasure. :)

Viewing 7 reply threads

You must be logged in to reply to this topic.