Discussions

Expand all | Collapse all

Need Last Primary, Secondary and Tertiary Approver in Parent Table

  • 1.  Need Last Primary, Secondary and Tertiary Approver in Parent Table

    Posted 09-17-2020 13:22
    Edited by Paul Agnew 09-17-2020 13:39
    I need to get the latest (max) approvers from a Status Log child table into the parent JE table to facilitate monthly reporting.  There are 3 approval/approver levels: 1) Primary, 2) Secondary and 3) Tertiary.  Each level contains the name of the person who can approve at that level.  As each approver approves a specific JE record that approval is recorded in the Status Log table.  Since there can be some back-and-forth in the approval process there can be multiple approvals/approvers at each level and different people can approve at same level in subsequent approvals.  I need to get the MAX or Latest approver name for each level ... Primary, Secondary and Tertiary back into the parent JE table to facilitate a report that shows the latest actual approver name for each JE record.

    Parent JE (parent table)                         Status Log (child table)
    ------------------------------------------              ---------------------------------------------
    JE record #1​                                             JE submitted by Bob
                                                                        Primary Approval - Paul receives/reviews Bob's JE and approves
                                                                        Secondary Approver - Jane reviews the JE and kicks it back to Bob for more info
                                                                        Submitter - Bob attaches additional info and re-submits
                                                                        Primary Approver - Mary approves (not Paul this time)
                                                                        Secondary Approval - George approves
                                                                        Tertiary Approval - Rosa reviews and kicks the JE back to Bob to collect more info
                                                                        Submitter - Bob attaches additional info and re-submits
                                                                        Primary Approval - Paul
                                                                        Secodary Approval - George
                                                                        Tertiary Approval - Charles

    I need to capture the names in red and push them back to the corresponding record in the JE table.
                                                                             


    ------------------------------
    Paul Agnew
    IT Manager
    Peak Properties
    CA
    9494070409
    ------------------------------


  • 2.  RE: Need Last Primary, Secondary and Tertiary Approver in Parent Table

    Posted 09-18-2020 10:12
    Edited by Paul Agnew 09-18-2020 10:13
    Any tips and recommendations?

    ------------------------------
    Paul Agnew
    IT Manager
    Peak Properties
    CA
    9494070409
    ------------------------------



  • 3.  RE: Need Last Primary, Secondary and Tertiary Approver in Parent Table

    Posted 09-18-2020 11:36
    Yes i do.  While this can probably be done, it will require a a log amount of code to parse out the pieces and then do the logic.  It's gunna be ugly.

    A far more flexible solution would be to write out child records for the approvals.  Then it will be way easier get at the various approvers.

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



  • 4.  RE: Need Last Primary, Secondary and Tertiary Approver in Parent Table

    Posted 09-18-2020 13:17
    Thank-you Mark.  The approval child records are stored in the Status Log (child table).  This is more-or-less working now via an automation that calculates the "latest" Primary, Secondary and Tertiary approver.  I'll pose additional questions as I run into any problems.

    Again, thanks so much.

    Regards,
    Paul

    ------------------------------
    Paul Agnew
    IT Manager
    Peak Properties
    CA
    9494070409
    ------------------------------