Discussions

 View Only
Expand all | Collapse all

Updating a field in a child record based on selecting parent summary value

  • 1.  Updating a field in a child record based on selecting parent summary value

    Posted 12-29-2020 13:10

    Not a very good title.

    Event (parent to Registration)

    Registration (parent to Ticket)

    EOTI registers for a specific Event date and creates a Ticket record (working as desired :) )

    Now I would like a different role, say MG, to be able to edit a field in Ticket based upon selecting a date (via calendar or pick list), seeing the list of people registered for that date, selecting the individual and updating their Ticket.

    I've played with a custom form on Registration which gets the date pick working as I'd like, then displaying a report with the limited fields I'd like the MG to either see or update but when I clicked the field to update, took me to a different report (I think, now that I think about it, maybe have been a form) but all the fields in Ticket were displayed, not what I wanted.

    I've played with a custom form on Ticket which kind of works the way I want but the date selection, when I click the dropdown shows me a pick list for reflecting every single Registration date (ie, 30 registrations should me 30 dates even if they are all the same).  I'd like to 'summarize' or group the dates.

    Still learning all that QB can do for me.  My thought process was form on the child (Ticket) and pull from the parent (Registration).  Is that part of my thought process correct?  Or do I want a form on Registration and use an embedded report to list the Tickets for that date?  Once I see the list of Tickets for that date, can I go back to the form to edit the desired remaining fields in Tickets?  (even asking this has made me think of things to check out and/or try).

    TIA!



    ------------------------------
    Katherine Oakey
    ------------------------------



  • 2.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 12-29-2020 13:12

    Mis-asked the last part.

    Form on Registration, embedded report for Tickets, select the Ticket record to be updated and resume the update to the Ticket fields?



    ------------------------------
    Katherine Oakey
    ------------------------------



  • 3.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 12-29-2020 14:53

    Katherine,

    Would you draw a picture of the relationships?  I am not following the architecture of the application.



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 4.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 12-29-2020 16:37

    Event - has unique date/time and associated marketing data.

    Registration - one record for every participant in an event including the date/time selected from Event- child of the Event>Registration relationship

    Ticket - one record for every participant in an event and includes problem statement.

    I've left out a Client table in the initial description.  A Client may have many Tickets, A Client may register for multiple events so actually:

    Events -> Clients

    Events -> Registrations

    Clients -> Registrations

    Clients -> Tickets

    Registrations -> Tickets

    parent -> child (for above reference)

    Registrations are driven by an EOTI form that gets the Ticket added and, if needed, a new Client record added.

    I would like to allow internal user to select the day of the event (I may be able to use use Today, have to think about that), display a list of all the Tickets specific to this Event (I can do that via a field in Tickets), view the associated problem and, optionally, update that specific Ticket record's solution field.



    ------------------------------
    Katherine Oakey
    ------------------------------



  • 5.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 12-30-2020 08:56

    Do you have these two relationships

    Events -> Registrations

    Registrations -> Tickets

    There for you can lookup the value for the Record ID# of the Event [Related Event] from the Registrations down to Tickets.

    Then create a Report Link field for Tickets on the Event  Table by matching Record ID# on the right with the lookup of [Related Event] (which I suggest called [Record ID# of Event].  That way the Tickets for the Event will be on the Event Record itself.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 6.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 12-30-2020 11:00

    Yes, I do have those two relationships.

    Thank you!  I'll fuss around with this to make sure I understand it :)

    Also had another thought this morning that I may give a try.



    ------------------------------
    Katherine Oakey
    ------------------------------



  • 7.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 12-30-2020 11:10

    Katherine,

    Thanks for the structure, you are in good hands with Mark's advice.



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 8.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 01-05-2021 11:49
    What am I missing?

    In the Event table I have added a Report Link to a report in the Ticket table that displays the ticket problem and solution (starting fairly simple).

    In the Registration table I added a field Event Record ID# which is a lookup from Event to Registration (the Reference Field is the Related Event in the Registration table), the Value field is the Record ID from the Events table.

    When I click on the Events table, I see a single row for each event (which I want) plus fields I don't wish to display (and not sure where they are coming from as they aren't in the List All for Events).  I do have a column for Tickets for Specific Events which is the Report Link field.

    When I click on the Report Link column value, I believe I am seeing a report from Registration (which is good because I see the client name) but I don't see the actual report from the Ticket table.

    Still suffering from not completely understanding all the basics :(

    TIA

    ------------------------------
    Katherine Oakey
    ------------------------------



  • 9.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 01-05-2021 12:22
    I have had somewhat of an 'a-ha' moment which helps and further confuses :)

    It appears the Report Link points to a table that has fields that may or may not be 'Reportable'.

    When I look at the fields in both Registration and Tickets, there are many default fields that I can't mark as non-Reportable (so I can pare down what columns are seen in both the Registration and Tickets tables when I click on an individual Report Link field from the Events table.  What I see when I am looking at the list of Events is, expected, the RecordID of the Event.

    When I click  on the Tickets for Specific Events value (which is the Report Link field; Record ID# from Events on the left; Event Record ID from Registration on the right), I get the list of registrations for that date (yay!) but more fields than I want to see from both Registration and Ticket tables, but again, more columns than I want to display to the user (and missing the Solution field) and most of these fields are default so I can't make them non-Reportable.

    ------------------------------
    Katherine Oakey
    ------------------------------



  • 10.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 01-05-2021 12:32
    Katherine,

    That's highly unusual in QuickBase to flag fields as being non-reportable. Having had the benefit of actually seeing your app and knowing your use case there is no reason to make any fields non-reportable.
    If you have a report link field there are two typical choices of how to use this on a form.

    The most common use of report links is to show the embedded table of child records directly on the form. The form will allow you to specify a report to be used for that purpose where you can control the fields being shown and the sort and grouping.

    But if you just want to show the link on a form to the children records where are user clicks then the users will land on the default report. If you go to any table and into the settings for reports and charts and then at the top right into the default report it will allow you to specify the default columns to show in a report and the default sort and the default dynamic filters.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Updating a field in a child record based on selecting parent summary value

    Posted 01-05-2021 13:34
    This helps (knowing that mostly using the Reportable isn't the correct direction.)

    What I envision happening is

    User selects an Event date and time from a drop-down (or clicking on a calendar).
    Dynamically update the form (new form?) with all the clients Registered for that event with their name and Problem (from the Ticket).
    User selects one of the Ticket records and updates the Solution field.

    Once the Ticket has been updated and saved, it would be ideal to go back to the form with the Event date already selected but I could live with having to reselect the event date.

    Where I think I am missing the 'connection' (literal and figurative) is what I want is data from two tables that have an 'intermediate' table (the Registration table).  Don't know how to get the dates to choose from at the same time I'm showing the tickets for that date (or shortly there after).

    I have messed around with forms on all 3 tables.  I can't seem to get the Event date as a 'distinct' list from the Event table when I have a form on the Ticket table.

    ------------------------------
    Katherine Oakey
    ------------------------------