How to conditionally show a report on a form
I have a table that populates every day a person is scheduled from form entries. The goal is to show a report on the schedule entry form, so that when a person's name is selected from the dropdown ...
Forum Discussion
To update a report link, the record must be saved. You could make a button that just saves and refreshes the page and title it 'Click here to update information.'
How many scheduled dates are there usually? If not a ton, you can also use a formula query to get all the dates in a list that matches that persons name. That would update immediately. You can also use a formula to see if the currently selected date is contained within that list of dates and fire off a warning if so.
------------------------------
Michael Tamoush
------------------------------
How many scheduled dates are there usually? If not a ton, you can also use a formula query to get all the dates in a list that matches that persons name. That would update immediately. You can also use a formula to see if the currently selected date is contained within that list of dates and fire off a warning if so.
------------------------------
Michael Tamoush
------------------------------
- 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
------------------------------- 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
------------------------------- 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
------------------------------
