Find record from Table A that is not linked to Table B

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I suspect this is fairly easy and possibly already has an answer in another thread, but I can't find one.

I have 2 tables (Table A and Table B) linked together through a third table (Link Table).  I am trying to find records from Table A that are not linked to Table B.  i.e. they don't exist in the Link Table.

How can I create a report that finds the records in Table A that aren't in my Link Table?  

Thanks.
Photo of Mike

Mike

  • 402 Points 250 badge 2x thumb

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
If you create a summary report of the # of link records, then the unlinked records are the ones which have the [# of Links] = 0
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Can you tell me the relationships between all your tables?
Photo of Mike

Mike

  • 402 Points 250 badge 2x thumb
Three tables:

Table A has people's Names
Table B has Activities people are involved 
Link Table links multiple people to multiple activities

Table A is One-to-many to Link Table
Table B is One-to-many to Link Table

I have created a test person that I haven't linked to any activities.  This record is only in Table A, with no link in the Link Table.  I am trying to produce some kind of report that will tell me that this record is not in the Link table (and any others that might be similar).
Photo of Mike

Mike

  • 402 Points 250 badge 2x thumb
I am brand-new to Quick Base (first use less than 2 months ago), so I might be looking at the wrong place when I created the Summary report you mentioned above.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Welcome to QuickBase.  It is a wonderfully flexible product.  I'm down at their Annual Convention now in Boston along with 800 other Quick Base crazed people.

It sounds like you have set it up the relationships correctly.  On the relationship between Names and the Link table, edit that relationship to create the Summary field.  It should count the number of link records for each respective Name.
Photo of Mike

Mike

  • 402 Points 250 badge 2x thumb
Thank you! I'm sure it is very flexible but for me I'm having to relearn everything after spending the last 15 years as a SQL Server admin and things here are done differently.

I have added the relationship Summary Field and it's now reporting the value I need, thank you very much.  Reports created and the missing people found. 

Thank you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Great, thx for letting me know it's working!