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....
Forum Discussion
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
------------------------------
Try this:
Size(GetRecords("{6.EX."&[CustomerID]&"}AND{8.AF."&[Activity Date]&"}",[_DBID_ACTIVITIES]))
------------------------------
Razi D.
Desta Tech LLC
razi@destatechs.com
------------------------------
- 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
------------------------------- 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
------------------------------
- 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
------------------------------- .... 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
------------------------------- Worked like a charm! My friends up north thank you (and I do, too).
------------------------------
Edward Hefter
www.Sutubra.com
------------------------------
