display a value for a summary record instead of a number in the parent report

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have three tables with the following relationships

LOCATIONS one to many DETAIL

SYSTEMS one to many DETAIL

DETAIL contains drop downs for the related location and the related system with additional Start and End Dates and a formula text field call Status.  Status will equal  "current" or "inactive".  Only one of the LOCATION's related child records will = "current".

My issue is that I need to create a reportable field in the LOCATIONS Table that that pulls the name of the related system from the DETAIL record where the  status = "current"

I tried creating a summary field on the Locations table that is the max of the record id where the status = current.  My problem is that this only displays the record ID number and I need the actual word or name of the system to use in various reports on the LOCATIONS table. 

Any suggestions?   Thank you,

Photo of Tamara

Tamara

  • 366 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
You are in luck.
1. You get to be my last Answer of 2015.
2. In fact you are already half way to the answer.

Make a new "Reverse Relationship" where 1 Detail is related to Many Locations. Yes, that seems backwards. Hence it's called reverse relationship.

On the right side of the Relationship for the "reference field" don't use the default, instead use that summary field you created. A good name for that field, btw, would be Record ID of Current Detail. Don't initially have a lookup field when the relationship is created. Then do a lookup of any fields on the Detail record such as System Name " down" to the Location.
Photo of Tamara

Tamara

  • 366 Points 250 badge 2x thumb
Solved it perfectly.  Thanks!