Forum Discussion

Srishha's avatar
Srishha
Qrew Member
23 days ago

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 recordList overlappingTasks = GetRecords($QUERY);

var number conflictCount = Size($overlappingTasks);

If(
  $conflictCount > 0,
  "Not Available",
  "Available"
)

I have used the above query to configure any existing records but the comaprison is not done in the way expected like the number 9 gets compared not the value like that in everything.

getrecords

5 Replies

  • Mez's avatar
    Mez
    Qrew Assistant Captain
    23 days ago

    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.

    • Srishha's avatar
      Srishha
      Qrew Member
      22 days ago

      I used TV but actually the values is not getting assigned with the values but remains as number itself.

      • Mez's avatar
        Mez
        Qrew Assistant Captain
        20 days ago

        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