Forum Discussion

KimCameron2's avatar
KimCameron2
Qrew Cadet
7 months ago

Related Records in the Same Table

Hello!  I need to "connect" multiple records within the same table that have the same phone number.  Each record is a customer with a membership.  Sometimes a customer will have a membership and it gets cancelled and then they will rejoin later with a new membership number or even within a different club and this will then cause me to have two records with the same phone number. (And I even have some with 3 or 4 records).  I would like to connect them, but what I would like to know is when I go to search using the global search field and it brings up all of the records, is there a way for me to have some sort of indicator of the "good" account?  

An example:

Record 1  Jane Doe    501-999-9999   Club XYZ   Mbr# 123456  Status: CXL

Record 7  Jane Doe    501-999-9999   Club ABC   Mbr#78910     Status: INACTIVE

Record 9  Jane Doe    501-999-9999    Club XYZ    Mbr#246810   Status: ACTIVE

I would like to be able to link/connect/relate all 3 records, but show that Record 9 is the ACTIVE record.  I currently use color coding but that does not show when using the global search option and that is what my reps use the majority of the time.

Suggestions - I am open to any and all.

Thank you,

Kim

Relationships

5 Replies

  • DonLarson's avatar
    DonLarson
    Qrew Deputy
    7 months ago

    I would solve this with a different architecture.  For this part of the application there would be two tables

    Contacts

    Memberships 

     

     

    RID 1  Jane Doe    501-999-9999   

    • RID 100  Club XYZ   Mbr# 123456  Status: CXL
    • RID 275 Club ABC   Mbr#78910     Status: INACTIVE
    • RID 650 Club XYZ    Mbr#246810   Status: ACTIVE

    Now when I search for a telephone number, I get the one and only Jane Doe with that telephone number and her three membership records.

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    7 months ago

    When you use the search bar, you cannot control the color of the report.  

    I suggest you put a search box on the dashboard of your App.  I don't know if you were using homepages or dashboards, but either way you can make a search element and have it search against records on a report. Then you can colour code the results.

  • KimCameron2's avatar
    KimCameron2
    Qrew Cadet
    7 months ago

    Since I already have customers with memberships and payments in the Customer table and the child Payments table, can I move the membership to another table without losing the link and I think I would need to move the payments with the memberships  as well.  Is that possible?  Also, how do I create the relationship where one record can have multiple membership records?  I apologize for all of the questions, but this would be ideal if I could accomplish.

     

    Thanks,

    Kim

  • DonLarson's avatar
    DonLarson
    Qrew Deputy
    7 months ago

    Kim,

    For this part of your application I would build this:

    I suspect you need more than just three tables to meet all your requirements, but this should solve the problem described here.