Displaying matching records from a child table onto the parent table

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

What I'm trying to do seems simple enough, but I cannot figure a way to do it.

I have a parent table (School Assignments) which is associated to a child table (Employments). In addition, the School Assignments table is a child table of the Schools table. Therefore, School-->School Assignments --> Employments.

The Schools table is also linked to a child table separately called Certificates.

Ideally, I'd like to show on the School Assignments record any Certificate records where the Certificate Start/End date is between the Employment Start/End dates. Currently, I can bring a report onto the School Assignments table that lists all Certificates but is there a way to show only ones that meet the criteria in both Employment and Certificate tables??

Photo of Niraj

Niraj

  • 0 Points

Posted 5 years ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,812 Points 3k badge 2x thumb
Schools < School Assignments < Employments

Schools < Certificates

I will asume that your Employment Start/End dates are contracts which each period are renewed.

1. Create a Summary Field between the School Assignments and Employments table which captures the Maximum Start Date

2. Create a Summary Field between the School Assignments and Employments table which captures the Maximum End Date.

These two fields will tell you what the "latest" start/end dates are in the Employments table.

3. Between the Schools and School Assignments table, summarize the two fields you created in 1) and 2) so that they also exist in the Schools table.

4. Between the Schools and Certificates table, add the 2 fields created in 3) as Lookup fields in to the certificates table.

5. In the School Assignments table, create a Report Link field where the Field in the Table is [Related School] and the lookup field value is the [Related School] in the Certificates table

6. In the Certificates table build a report which shows all Certificates where the Date in that table is between the Maximum Start and the Maximum End dates

7. In the School Assignments table, place the Certificates Report-Link field in the Form Layout, and display the records and select the report created in 6).
Photo of Niraj

Niraj

  • 0 Points
Thank you so much Laura. Your technique worked like a charm!