Many to Many Relationship - 3+ Tables

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
  • (Edited)
Scenario:
(will be using terms from Criminal Minds)

Table: Intake

Intake collects up to
  • 5 Phone Numbers
  • 5 Email Addresses
  • 5 Websites
  • 1 Name
  • 1 Alias
Names are not Unique
Alias Must be

Table: Info
Each Verified Phone Number has a new record with the Phone and Alias
Each Verified Email has a new record with Email and Alias
Each Verified Website has a new record with Website and Alias


Table: Alias

Contains All known Aliases
Uses a Report Link to pull
  • Every Phone associated to the Alias from Info
  • Every Email Associated to the Alias from Info
  • Every Website Associated to the Alias from Info
  • Every Name associated to the Alias from Info
Table: Unsub
Links multiple Alias's together based on what our goal state is.



Goal:
We want to from the Alias table see any other Alias that a given Phone,Email, or Website may be linked to.


Any given piece of the contact info could show up on more than one Alias


Current State:
Any attempts I've made to run reports or build relationships from the Alias table:
  •  only return data that matches the Alias
  • Does not return related data to the given phone, email, or website regardless of how I've built the Report Links. 


Example:
Intake record could have:

Record 1:
Alias:  BAU
Name: JJ
Phone 1: 202 555 5555
Phone 2: 555 867 5309

Record 2
Alias: BAU 1
Name: Reed
Phone 1: 555 123 4567
Phone 2: 202 555 5555



When I look at the Alias table for BAU, I want to see that 202-555-5555 also showed up in BAU1








Photo of JessicaD_QB

JessicaD_QB

  • 90 Points 75 badge 2x thumb
  • Frustrated

Posted 3 months ago

  • 0
  • 1
Can you explain what relationships you have in place now are what what the key field is of your Alias table? is it [Record ID#] or [Alias].
Photo of JessicaD_QB

JessicaD_QB

  • 90 Points 75 badge 2x thumb
Key Fields:
Intake: Record ID#
Info: Data ID# (renamed Record ID)
Alias:  Alias (text field)
Unsub:  Unsub (text field)


Relationships:
INTAKE:
Many Intake Reports belong to a Single Alias
Many Intake Reports belong to a Single Unsub

INFO:
Many Data Points Belong to a Single Unsub

ALIAS
Many ALIAS belong to One Unsub

UNSUB
has the three references prior.
i believe I can get this working for you, but I would have to work with you and the app directly for an hour or two. Please contact me at QuickBaseCoch.com if you would like to engage for one on one Assistance.