View Only
  • 1.  Help with a formula query, please

    Posted 05-04-2022 13:57
    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

  • 2.  RE: Help with a formula query, please

    Posted 05-04-2022 18:53
    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

  • 3.  RE: Help with a formula query, please

    Posted 05-04-2022 19:45
    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

  • 4.  RE: Help with a formula query, please

    Posted 05-04-2022 21:32
    @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)

  • 5.  RE: Help with a formula query, please

    Posted 05-08-2022 07:52
    One Problem I did find in formula query is for lookup it asks for FID in correct case - 6 and not variable , if I delete this particular field (i've done so by mistake being a n00b , there is no error showcased in formula, on other hand if formula query .


    Prashant Maheshwari

  • 6.  RE: Help with a formula query, please

    Posted 05-06-2022 09:46
    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

  • 7.  RE: Help with a formula query, please

    Posted 05-06-2022 14:23

    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