How to conditionally show a report on a form

JasonStricker
Qrew Trainee
2 years ago
I want to show up to two months of dates for the selected person, is that a ton?
I could bring that down to 31 days to make the formula route work.
The formula query and followup formula would be the ideal situation, that warning is what I'm after.

I'm new to formula queries, I've watched a video and done the official Quickbase post readings in Help. I think I can figure that out.

What would the formula look like to search the dates that match the person's name and alert?
Does the query create a report?

Thanks!!

------------------------------
Jason Stricker
------------------------------
- MichaelTamoush
  Qrew Captain
  2 years ago
  Quickbase Junkie has some great videos
  https://www.quickbasejunkie.com/blog/formula-query-functions?utm_source=qb&utm_medium=r&utm_id=0
  
  So yours might be something like:
  var text Query = "{15.EX.'" & [User Name] & "'} AND {23.OAF.'" & [Date] & "'} AND {23.OAF.'" & [Date + 60days] & "'}";
  
  ToText(GetFieldValues(GetRecords($Query,"bgrdgy7gg"),23)))
  
  So the query above matches the user name in your current record you are scheduling, the field 15 (whatever the field is in the schedule table for user name) and says that field 23 (date field in schedule table) is On or After the Date in your current record, and field 23 is also on or before the date plus 60 days (you can just make this field as well).
  
  Then it gets all the values in your foreign table (bgrdg...) from field 23 (your date field).
  
  This will just give you a string of dates (2-2-22;2-3-23) and you can use formulas to make that in a prettier list, and another formula to see if your current date is contained within that string.
  
  Somethings I am not sure of and dont have time to test: Not sure if you can compare user fields. You might have to make the user fields text (which would be emails) and compare those. Also, not sure if you can return a list of dates. You might have to ToText your date field and return a string of dates that are in text format.
  
  ------------------------------
  Michael Tamoush
  ------------------------------
  - JasonStricker
    Qrew Trainee
    2 years ago
    This is a great example and explanation, Thanks!
    Since I'm only capturing First day onsite (FDOS) and Last day onsite (LDOS) I wasn't able to filter all the dates by User then search these to see if they matched my entered dates on the form, I needed all the dates in between as well.
    To do that I created a new table "Dates", loaded 30 yrs of dates in it. I then use the following query:
    
    //Fid6 = First Day Onsite
    //Dates table = bs2n4hd8k
    var text Query =
    "{6.OAF.'"&[First Day Onsite]&"'}AND{6.OBF.'"&[Last Day Onsite]&"'}";
    (GetFieldValues(GetRecords($Query,"bs2n4hd8k",6))
    
    This returns all the dates the User is scheduled based on the User entry "FDOS" and "LDOS" on the form. I included this field, "Dates Between FDOS and LDOS" on the form to check it and it shows these dates as soon as the FDOS and LDOS selections are made.
    
    The problem now is in searching the table for these dates. Here is the formula I'm trying to make work, it always returns 1 since the FDOS is is always in the list of dates for that record, and the resource name always matches the form entry. I had a version of this formula working, but changed a field or two and now I'm stuck.
    
    Formula Text Field: "Is FDOS Already Scheduled"
    //Fid52 = Resource Name - record picker from "Team Members" table
    //Fid78 = Dates between FDOS and LDOS - Formula Multi Select Text
    //Fid82 = ToText FDOS - converts the date field FDOS to text
    var text Query =
    "{52.EX.'"&[Resource Name]&"'}AND{85.CT.'"&[First Day Onsite]&"'}";
    
    ToText(
    Contains(
    ToText(
    GetFieldValues(GetRecords($Query),82)),[TOTEXT FDOS]))
    
    The plan is to use this field as a trigger for an alert field, setup in Dynamic Form Rules
    
    ------------------------------
    Jason Stricker
    ------------------------------

Forum Discussion

How to conditionally show a report on a form