Query Question - search after date

Question

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

razid_ · Answer

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 · Answer

Razi,Good catch.That's why I always write them asvar text QUERY = "{6.EX."&amp;[CustomerID]&amp;"}AND{8.AF."&amp;[Activity Date]&amp;"}";
Size(GetRecords($QUERY, [_DBID_ACTIVITIES]))------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

edwardhefter · Answer

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 · Answer

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 · Answer

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

Forum Discussion

Query Question - search after date