Forum Discussion

DanPark1's avatar
DanPark1
Qrew Cadet
9 years ago

Struggling with Many to Many...

I have the following tables..

Customers
Surveys
Sales

A Survey is a child of both a Customer and Sale.

When I go to add a Survey from a Customer.. the related customer is automatically selected. The related Sale needs to be selected from a dropdown and vice versa.

Is there a way to have the related Sale be automatically selected when adding a Survey from a Customer? and vice versa.

The Sale record has a field called Related Customer Full Name which is the same as the Customer Full Name.

If not automatically selected at the very least have the drop down only display Sales where the "Related Customer Full Name" is the same as Customer Full Name?
  • ChrisChris's avatar
    ChrisChris
    Qrew Assistant Captain
    9 years ago

    hi,

    If Customer and Sales are locked to one another, you may want a relation that is Customer>>Sales>>Surveys.

    The setup you have now allows Customers and Sales to have the many-to-many relationship by way of the survey table. The Customer records can have many different Sales associations, and Sales can have many different Customer associations.

    If selecting a Customer in the Survey record should also cause a particular Sales record to be brought in as a lookup, then it seems, you're wanting the top down relationship hierarchy where Customer has many Sales, and Sales has many surveys.

    Let's see what others may say.

  • DanPark1's avatar
    DanPark1
    Qrew Cadet
    9 years ago
    Yes, but I want to be able to create records the other way as well. I would also like to be able to create a Survey from a Sale record which would Auto Populate/attach to the appropriate Customer record
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    Here is something you could try:

    On the Sales Table, you want to change the 'Add Survey' button that probably looks something like this:

    URLRoot() & "db/" & [_DBID_SURVEYS] & "?a=API_GenAddRecordForm&_fid_13=" & URLEncode ([Record ID#])& "&z=" & Rurl()

    (where field # 13 on the surveys table is your 'related sales' field / the reference field from the sales table and [record id#] is the key field on the sales table)

    to something like this:

    URLRoot() & "db/" & [_DBID_SURVEYS] & "?a=API_GenAddRecordForm&_fid_13=" & URLEncode ([Record ID#])
    & "&_fid_11=" & URLEncode ([Related Customer])
    & "&z=" & Rurl()

    (where field # 13 on the surveys table is your 'related sales' field / the reference field from the sales table and where field # 11 on the surveys table is your 'related customer' field / the reference field from the customers table and [record id#] is the key field on the sales table)

    From the customer table, the add survey button wouldnt be an ideal place to add a survey from because a customer might have many sales. If there is only one sales record, or if you would always want to populate the most recent sales record into the survey, you could use a summary field for the maximum of the record id# on the sales table, and pass that through to your 'related sales' field in the method I described above.

    If you need help - my contact info is in my profile.

    Good luck!