Discussions

 View Only
Expand all | Collapse all

How to relate a child record from one table to two parent records in different tables?

  • 1.  How to relate a child record from one table to two parent records in different tables?

    Posted 03-28-2019 19:15
    I have three tables: Projects, Tasks and Documents. Projects is the parent table for both Tasks and Documents, and Tasks is a parent table to Documents. In practice users may add documents to the Project record or they may be completing a task for that project and so be in a Task record and want to add a document associated with that particular task. 

    I would like documents that are added to a Task record to also appear in the documents list for the Project record so that users don't have to drill down into the Task record to get/search for to documents added that way.

    I created an automation that copies the record ID in the Related Project field from the Task record to the Related Project field in the Document record, and that works - but it will only fire if some data changes in the Task record - not if the user simply hits save after adding a document to the Task record.

    So I then tried to find ways to force some data change in the Task record to force the Automation to trigger but so far nothing has worked.

    This seems like useful functionality so I thought for certain there would be instructions online already but I've been looking for quite awhile and haven't gotten anywhere. Any help would be greatly appreciated!

    AJ


  • 2.  RE: How to relate a child record from one table to two parent records in different tables?

    Posted 03-28-2019 19:22
    OK, so this is way simpler than your attempted solution.

    You already have a field on the Document for [Related Project] which may or may not have data.

    Do a lookup from Tasks down to Documents for [Related Project], but call it [Record ID# of Project From Task]

    Make a new formula field called [Record ID# of Project]

    The formula will be

    IF(
    [Related Project] >0, [Related Project],[Record ID# of Project From Task])

    Then make a new report link field on Projects (or re-configure the one you have already) and on the left side configure to [Record ID#] and on the right side navigate back to you same app and locate the field [Record ID# of Project] on documents, 

    Put the Report link field on the Projects form and and y'er done!



  • 3.  RE: How to relate a child record from one table to two parent records in different tables?

    Posted 03-28-2019 20:16
    Wow - way easier! Thanks very much!