Forum Discussion

ToddHorn's avatar
ToddHorn
Qrew Member
6 years ago

Child to Child Lookup / Relationship

In a project management app a Project (parent table) has many Milestones (child table).

Typically each Project has four Milestones associated with it, "Milestone 1", "Milestone 2", "Milestone 3" and "Milestone 4". 

Each Milestone record has has two date fields, "Should be Completed by Date" and "Actually Completed by Date" and a "Completed?" yes/no field.

I have a Milestone Table Report showing all "Milestone 4s" that have not been completed.  On that table, I would like it to list per row (ie each Milestone 4) the related Milestones 1, 2 and 3 dates ("Should be competed by" and "Actually completed") in separate columns so that managers can review all uncompleted Milestone 4s and see the historical timeline/runway leading up to it.  This table report is printed for review.

A report link works well to display related Milestones and their dates on the Milestone 4 record, but doesn't work to have them listed in a table report.  I tried a Milestone to Milestone relationship but didn't get far..

Any guidance would be appreciated.

------------------------------
Todd Horn
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    I think, I understand.  You seem to need to be able to flat up text information from children up to the Parent to either use on a Report on the parent table or to look up down to children.

    You can use the relatively new Combined Text Summary field for this.

    Make a single formula field on the Milestone Table  and be sure to make it sort in the correct sequence like this

    List(" ", "Milestone: " & ToText([Milestone#),
    [Milesone Name],
    "S/B date: " & ToText([Should be competed by]),
    "Act date: " & ToText([Actually completed]))


    Then use a combined text summary field to float up that text to the Parent Project.   You may want to have the filter omit Milestone 4.    Call the field  [Combined Text Summary Milestones 123]

    Then you will see that the fields appears in a bubble format (although it is clickable as a link).

    If you want nicer looking text  you can do this as a formula text field.

    SearchAndReplace([Combined Text Summary Milestones 123]," ; ", " \n")

    Then you can look that up down to your milestone table and use it on reports.

     

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