Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
3 years ago

Formula Query to check for users already on PTO and display on a form

Hi everyone,

I am relatively new to formula queries and love the power of them but am finding them super complicated to use....  I am trying to create a rich text field that will display employees that are on PTO the same day when I am entering PO for another user.

So lets say I have Bob Smith and Mike Watts.  I put in PTO for Bob Smith into my PTO calendar for 12/31.  If Mike is already on PTO that day once I fill in the dates I want to have a rich text field that shows a table showing the name of the person, start of PTO, and end of PTO.  This is the code I have so far based on a lot of googling and searching for videos but I am struggling here

//Get a copy of all records in which the start date of PTO is on or BEFORE the new request AND the end date of PTO is on or AFTER the new request

"<table>" &
"<tr><td>Record ID</th><th>PTO Start Date</th><th>PTO End Date</th></tr>" &
SearchAndReplace(ToText(GetCellValues(GetRecords("{8.OAF.'"& [PTO Start Date] & "'} AND {9.OAB.'"& [PTO End Date] & "'}"),)), ";", "") &
"</table>"

From what I can find on google I need to use the GetCellValues, and somewhere in this (I am missing it now) I need to reference another field that has my "query text" in it.  But I cannot figure out how to do that.  The other thing is unless I am missing something this is not going to get the person's name but only print the record id.

------------------------------
Ivan Weiss
------------------------------
formula queries

2 Replies

Sort By
  • SeanConnaughto1's avatar
    SeanConnaughto1
    Qrew Cadet
    3 years ago
    "<table>" &
"<tr><td>Record ID</th><th>PTO Start Date</th><th>PTO End Date</th></tr>" &
SearchAndReplace(ToText(GetCellValues(GetRecords("{8.OAF.'"& [PTO Start Date] & "'} AND {9.OAB.'"& [PTO End Date] & "'}"),)), ";", "") &
"</table>"​

    FYI--You are going to need to change the "GetCellValues" to "GetFieldValues" as that was changed recently. Maybe try this:

    "<table>" &
"<tr><td>Record ID</th><th>PTO Start Date</th><th>PTO End Date</th></tr>" &
SearchAndReplace(ToText(GetFieldValues(GetRecords("{8.OAF.'"& [PTO Start Date] & "'} AND {9.OAB.'"& [PTO End Date] & "'}"),[QUERY FIELD YOU WANT TO CAPUTRE HERE])), ";", "") &
"</table>"​


    ------------------------------
    Sean Connaughton
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      3 years ago
      Thanks Sean, so what is meant by query field you want to capture here for the GetFieldValues?  I am not quite sure what field that is supposed to reference.  For some reason I am having a mental block on that one....

      I am looking to "loop" through all of the records to get the full name, PTO Start Date, PTO End Date and display them on a table

      ------------------------------
      Ivan Weiss
      ------------------------------