Forum Discussion

BaileyDesormeau's avatar
BaileyDesormeau
Qrew Member
5 months ago

Getting a field from a great grandparent table

I have the following tables. (Drivers) have many (BOL) have many (BOL Assignments) have many (Inventory Tracking)

I am trying to create a status for a driver based off of the deployment status of the assets that he has on his truck. The deployment status is in the inventory tracking table. The inventory status is updated based off of some check out/in fields. So based off of the statuses of the assets on his/her truck i can know what the drivers status is. If he/she is busy or not busy. Is it possible to get this status from a table that is so distantly related?



------------------------------
Bailey Desormeaux
------------------------------

4 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    5 months ago

    If its a straight line relationship as you describe the summary fields can do it. You can do something like role up into BOL Assignments something like 'Driver Assigned' - then summarize that field into BOL by the same name and then roll it into Drivers to make your status. Your summary field can be the 'Has any records' boolean value and just keep in your grandparent / great grandparent you're just summarizing where that child summary field is checked. 

    Functionally you won't have any issue - you may just want to make the fields in BOL / BOL Assignments non searchable / reportable since that value doesn't make sense in that data context. 



    ------------------------------
    Chayce Duncan
    ------------------------------

    • BaileyDesormeau's avatar
      BaileyDesormeau
      Qrew Member
      5 months ago

      I made a mistake in my initial question. The relationship betwen BOL and Inventory Tracking, so the great grandparent and the grand parent. Each (Inventory tracking) has many BOL Assignments. So i cant do the summary all the way down. The relationships are this:

      Each Inventory Tracking which is an asset mission assignment has many BOL assignments. Then Each BOL has many BOL Assignments, Then Each driver has many BOLs. Maybe the picture below will help? I was trying to use an API that chatgpt created haha but it wasnt working. It was trying to get the record from the start of the relationship and bring it down through the tables somehow.



      ------------------------------
      Bailey Desormeaux
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain
        5 months ago

        Same process would apply in practice, honestly a little easier. You would add a lookup field from Inventory Tracking into BOL Assignments if you don't already have it for the Deployment status of the Inventory Tracking. 

        Then in the relationship between BOL and BOL assignments - summary field of any records where Deployment Status (lookup field) is whatever status(s) you consider deployed. 

        Then in the relationship between Driver and BOL - summary field of any records where the previously created summary field is checked. 



        ------------------------------
        Chayce Duncan
        ------------------------------