Discussions

Expand all | Collapse all

Many to Many Relationship - 3+ Tables

  • 1.  Many to Many Relationship - 3+ Tables

    Posted 04-04-2019 20:21
    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










  • 2.  RE: Many to Many Relationship - 3+ Tables

    Posted 04-05-2019 20:43
    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].


  • 3.  RE: Many to Many Relationship - 3+ Tables

    Posted 04-08-2019 18:26
    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.


  • 4.  RE: Many to Many Relationship - 3+ Tables

    Posted 04-09-2019 11:31
    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.