DavidHalter1
3 years agoQrew Member
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
------------------------------
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
------------------------------