How to use GetRecords()
var text QUERY = "{9.EX.'" & ToText([Person Assigned]) & "'} AND {3.NE." & [Record ID#] & "} AND {11.LE." & [End Date] & "}AND{10.GE." & [Start Date] & "} AND{13.NE.'Completed'}"; var recordL...
Forum Discussion
Have you tried to compare field 9 using 'TV' instead of 'EX', since it appears that is a user field? And what does the formula checker have for the execution of this?
https://helpv2.quickbase.com/hc/en-us/articles/4418287644308-Components-of-a-Query
TV | True Value (compares against the underlying foreign key or record ID stored in relationship fields). When you use tv, you are comparing against the unique value of the user which is either the hashed UID like 123456.abcd OR the email address OR the screen name.
| Also used for queries on User fields. |
I used TV but actually the values is not getting assigned with the values but remains as number itself.
Can you describe what you're query is trying to accomplish?
Try using TV with your user field, 9, and with Record ID, 3 (or XEX), and what type of field is 13?
In looking at your original query, you mention you used it before, yet I'm not sure 'NE', 'GE', or 'LE' are supported. If you're trying to use a "not equal" to, then use 'XEX'; if greater than or equal to use 'GTE'; and less than or equal to 'LTE'.
If you work your query in chunks, say starting with Dates, do you get records? And then if you add the status check, do you still get records?
If you're comparing dates, have you tried using the BF, OBF, AF, OAF (these are specific to date fields)?
Assuming I know what you're trying to accomplish, I might rewrite like this:
var text QUERY = "{9.TV.'" & UserToEmail([Person Assigned]) & "'}AND{3.XEX." & [Record ID#] & "}AND{11.OBF." & [End Date] & "}AND{10.OAF." & [Start Date] & "} AND{13.XEX.'Completed'}";
*edited: you'll need to convert user field to email, added screenshot for comparison
// Define the query for overlapping tasks
var text QUERY = "{9.TV.'" & UserToEmail([Person Assigned]) & "'}AND{3.XNX." & [Record ID#] & "}AND{11.OBF." & [End Date] & "}AND{10.OAF." & [Start Date] & "} AND{13.XEX.'Completed'}";
// Fetch conflicting tasks
var recordList overlappingTasks = GetRecords("Tasks Assignment", $QUERY);// Return availability status
If(
Size($overlappingTasks) > 0,
"Not Available",
"Available"
)
Though i used like how u told i am not getting desired output my intention is to see whether is there any existing records already with specified condition so that i won't consider that concerned person can be assigned with any task further.Thank you
