Forum Discussion

EdwardHefter's avatar
EdwardHefter
Qrew Cadet
2 years ago

Query Question - search after date

I am trying to count how many records there are that are tied to the same customer and that come after the current record by date using the formula:
Size(GetRecords("({6.EX."&[CustomerID]&"}AND{8.AF.'"&[Activity Date]&"'})",[_DBID_ACTIVITIES]))

Where field 6 is CustomerID and field 8 is [Activity Date]. The formula should be looking for all records with the same CustomerID (that part works - I took out the second part to check) and with an Activity Date after the current one. If there are 3 activities for a customer on different dates, I would expect one to have a value of 2, another to have a value of 1, and the third to have a value of 0 (no records with a date later than the latest one).

Instead, I am getting a seemingly random result of 1s and 2s (no customer has more than 2 activities yet).

I don't know if it matters, but since this system is for use in Canada, it has the date format the "right" way, with "DD-MM-YYYY" rather than our American way of "MM-DD-YYYY"

What's wrong with my syntax or logic?

------------------------------
Edward Hefter
www.Sutubra.com
------------------------------

6 Replies

Sort By
  • RaziD_'s avatar
    RaziD_
    Qrew Cadet
    2 years ago
    It is maybe happening because of parenthesis you have for query.
    Try this:

    Size(GetRecords("{6.EX."&[CustomerID]&"}AND{8.AF."&[Activity Date]&"}",[_DBID_ACTIVITIES]))

    ------------------------------
    Razi D.
    Desta Tech LLC
    razi@destatechs.com
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Qrew #1 Challenger
      2 years ago
      Razi,
      Good catch.
      That's why I always write them as

      var text QUERY = "{6.EX."&[CustomerID]&"}AND{8.AF."&[Activity Date]&"}";

      Size(GetRecords($QUERY, [_DBID_ACTIVITIES]))



      ------------------------------
      Mark Shnier (Your Quickbase Coach)
      mark.shnier@gmail.com
      ------------------------------
      • EdwardHefter's avatar
        EdwardHefter
        Qrew Cadet
        2 years ago
        That is much easier to read - I think I'll start using the local variables like that, even if it is a simple query.

        ------------------------------
        Edward Hefter
        www.Sutubra.com
        ------------------------------
    • EdwardHefter's avatar
      EdwardHefter
      Qrew Cadet
      2 years ago
      Thanks for finding the parenthesis issue, which did need correction, but it didn't solve the problem.

      The next thing I tried was changing the date format of the App back to the US standard - MM-DD-YYYY. When I did that, everything worked fine. When I changed it back to the rest of the world standard - DD-MM-YYYY, it once again gave me strange results (some 0s, 1s, and 2s, when there were only 1 or 2 activities per person).

      So, my question now is, how do I search for the number of On or After (OAF) records when the date format is not the US standard? I did change the .AF. to a .OAF.

      ------------------------------
      Edward Hefter
      www.Sutubra.com
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew #1 Challenger
        2 years ago
        .... always happy to help a fellow Canadian ...

        My assumption would be that pipelines only understands the American date format. But this is no problem.  

        We can just make a new field called [Activity Date (US format)].  This will be a text field which looks American.

        not tested but it would look like this.

        var text DD = PadLeft(ToText(Day([Activity Date])),2,"0");
        var text MM = PadLeft(ToText(Month([Activity Date])),2,"0");
        var text YYYY = ToText(Year([Activity Date]));

        List("-", $MM,$DD, $YYYY)

        Then use this field in your Query.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------