Forum Discussion

JasonStricker's avatar
JasonStricker
Qrew Trainee
3 years ago

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 it shows a report of their currently scheduled dates on the form.
This is help prevent double booking.

The embedded report filtered for:
1) Filtered by the person's name selected from a dropdown

I know how to display data from the child table, but I'm stuck on the report.
This entry form is in the same table as the scheduled dates
Can this be done or will the form need to be saved before any information is populated?

Thanks!

------------------------------
J. Striker
------------------------------
Forms
reports and charts
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    3 years ago
    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
    ------------------------------
    • JasonStricker's avatar
      JasonStricker
      Qrew Trainee
      3 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's avatar
        MichaelTamoush
        Qrew Captain
        3 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
        ------------------------------