Forum Discussion

quincy_p's avatar
quincy_p
Icon for Advocate I rankAdvocate I
2 years ago

Pull a Max Date from Related Fields

Hello - I am trying to create a service deadline field - from my data we get a 'Parent' Work Order which has numerous associated Child Work Orders - these child Work Orders have a Deadline field already that is associated to their product, the Parent ones do not have this as their is technically no product on the WO, just the Child related to it.

 

So I need a column query that will find the max PM_Deadline from the CHild WOs and match it to the parent. To do so I have the following fields in the table 'Work Order ALL'

 

[Id] - every WO has a unique ID

[PM_Deadline] - only present on Child WO

[Work_Order_Quote] - only present on Child WO but this value provides the ID of the Parent WO.


So you can see in the tables below that in Tab 1, Id matches Work_Order_Quote__c in Table 2. Is there anyway to use this field in order to pull the date PM_deadline on to the Id in the top table (ideally the max date as the dates will not always be the same). 

 

Tabe 1:

IdParent_WOPM_DeadlineQParent_WOWork_Order_Quote__c
a4B3q0000047FOaEAMWO-05247934 WO-05247934 

Tabe 2: 

IdNameParent_WOWork_Order_Quote__cPM_Deadline
a4B3q0000047FOfEAMWO-05247935WO-05247934a4B3q0000047FOaEAM8/31/2022 0:00
a4B3q0000047FOgEAMWO-05247936WO-05247934a4B3q0000047FOaEAM8/31/2022 0:00
a4B3q0000047FOhEAMWO-05247937WO-05247934a4B3q0000047FOaEAM8/31/2022 0:00
a4B3q0000047FOiEAMWO-05247938WO-05247934a4B3q0000047FOaEAM8/31/2022 0:00
a4B3q0000047FOjEAMWO-05247939WO-05247934a4B3q0000047FOaEAM8/31/2022 0:00
a4B3q0000047FOkEAMWO-05247940WO-05247934a4B3q0000047FOaEAM8/31/2022 0:00
a4B3q0000047FOlEAMWO-05247941WO-05247934a4B3q0000047FOaEAM8/31/2022 0:00
a4B3q0000047FOmEAMWO-05247942WO-05247934a4B3q0000047FOaEAM8/31/2022 0:00
a4B3q0000047FOnEAMWO-05247943WO-05247934a4B3q0000047FOaEAM8/31/2022 0:00

 

Need Help
Tips and Tricks

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    quincy_p,

     

    Here are two calculated columns you can add to Table1.

     

    The first one uses FILTER and does not require a relationship between the tables.

     

    PM_Deadline FILTER = 
VAR vId = Table1[Id]
VAR vResult =
    MAXX (
        FILTER ( Table2, Table2[Work_Order_Quote__c] = vId ),
        Table2[PM_Deadline]
    )
RETURN
    vResult

     

    The second one uses RELATEDTABLE and requires a relationship between the tables. Use this one if possible due to better performance since it utilizes the relationship.

     

    PM_Deadline RELATEDTABLE = 
MAXX ( RELATEDTABLE ( Table2 ), Table2[PM_Deadline] )

     

     

     

    • quincy_p's avatar
      quincy_p
      Icon for Advocate I rankAdvocate I
      2 years ago

      Hi DataInsights - thanks for the quick reply! And sorry for the confusion but everything is in the same table called 'Work Order ALL' - I was trying to use the 2 tables of data to visualize the issue I was having.

       

      That being said, I was thinking that it just may be easier to do a separate table anyways so I will have a crack at it and come back if it works.