Forum Discussion

DavidHalter1's avatar
DavidHalter1
Qrew Member
3 years ago

Help with a formula query, please

I'm having trouble with a formula query. I can't get the output to match what I expect. Right now, all I want is the field to give a list of all my records that have a User Input Date on or before the User Input Date in that record. I hope to use method eventually to identify duplicate records, but right now even this simple query is not working right. Something is messed up. Can you help me find it?

I have [User Input Date] with field ID 18
[Record ID#] (the output currently) has field ID 3
I created a Date Formula field called [TodaysTheDay] where the equation is Today()
I created a Text Formula field called [TempDateCompare] where the formula is:
If([User Input Date]<Today(),"User Input Date IS before today","")

Today is May 4th and my app and environment is set to use DD-MM-YYYY

The problem query field is now called problemQueryDate and is set as a Formula-Text field. The formula is:

var text queryOne = "{'18'.BF.'04-05-2022'}";
var text queryTwo = "{'18'.BF.'"&Today()&"'}";
var text queryThree = "{'18'.BF.'"&[User Input Date]&"'}";
var text queryFour = "{'18'.BF.'"&[TodaysTheDay]&"'}";

"One: " & ToText(GetFieldValues(GetRecords($queryOne),3)) &
"\n Two: " & ToText(GetFieldValues(GetRecords($queryTwo),3)) &
"\n Three: " & ToText(GetFieldValues(GetRecords($queryThree),3)) &
"\n Four: " & ToText(GetFieldValues(GetRecords($queryFour),3))

The results I'm getting are in this screenshot:
queryOne, queryTwo, and queryFour should be exactly the same, but only queryOne, where I specify the date, is working.
queryThree should be blank for record 4, "4" for record 3, and "3 ; 4" for record 1

Yes, I only have 3 records total in this table, but even so this isn't working.

What am I missing?

------------------------------
David Halter
------------------------------
formula queries
Formulas and functions
  • DavidHalter1's avatar
    DavidHalter1
    Qrew Member
    3 years ago
    Well, after a lot more testing, I think I figured out part of the problem above. The text version of today's date, despite my app and profile set to use DDMMYYYY works as the rest of the equations do when I write it out in MMDDYYYY format. I still don't think the queries are working as expected, however, and in fact are giving very unexpected results. To that end, I opened a case with support. So far they agreed that their results are not the same as mine and they are going to check out my app.  Thanks for considering.

    ------------------------------
    David Halter
    ------------------------------
    • PrashantMaheshw's avatar
      PrashantMaheshw
      Qrew Captain
      3 years ago
      What irritates me the most is , formula query field names are not updated in formula like rest of QuickBase , so if you rename a field your formula query will stop working. I have gone back to creating child parent relationship to have stability.

      ------------------------------
      Prashant Maheshwari
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago
        @Prashant Maheshwari 
        I tried renaming a field which was used in a formula query and as I expected, the formula automatically updated to the new field name.   Can you explain further as to your perception of the problem?​

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------
  • DavidHalter1's avatar
    DavidHalter1
    Qrew Member
    3 years ago
    Back to the OP question (my own) after discussion with support, this is a known issue when apps are in DD-MM-YYYY format. Might be something to consider if you ever need to change the format in your app.  The issue ID is QBE016513.

    The proposed solution/workaround was to convert any date I want to compare into a number in its own field and then run my query based on that number field using GT, GTE, LT, LTE, EX, XEX and etc.  The proposed date to number conversion here:

    ToNumber(ToText(Year([User Input Date]))&
    PadLeft(ToText(Month([User Input Date])),2,"0")&
    PadLeft(ToText(Day([User Input Date])),2,"0"))

    Though to me, this sort of conversion makes more sense, since it always puts two consecutive dates as two consecutive numbers:

    ToDays([User Input Date]-Date(1970, 1, 1))

    Bit of a pain, really, to have to have both a Date field and a Number field for each date that I might want to compare later, but since it is really important for my potential users to have DD-MM-YYYY formats, it will be worth it. At least I'm not also dealing with hh:mm:ss!

    Is there a way we close these discussions? Or do they just remain forever open to be commented on whenever?

    ------------------------------
    David Halter
    ------------------------------
    • PrashantMaheshw's avatar
      PrashantMaheshw
      Qrew Captain
      3 years ago

      HI David ,

      Thanks for sharing the update from QuickBase Support. I too use DD-MM . I was trying to work with DATE(YYYY,MM,DD) format and failing (to some degree)

      @Mark Shnier , I just found my frustration from David Answer. 

      ​​​​​

      ------------------------------
      Prashant Maheshwari
      ------------------------------