Forum Discussion

StephenStephen's avatar
StephenStephen
Qrew Assistant Captain
8 years ago

Associating records with multiple child records

1. Application is called "submissions"
2. each submission gives my agent a chance to submit various pieces of data.
3. I want this submission to be related to anywhere between 1 and many of the products in my catalog which are currently in a table, with record ID of the SKU of that product. 

I want each submission to be related to multiple products, between 1-??.  But the problem is that multi-select fields are limited to 50 products. So I can't figure out how to create a multi-select picker that lets the user pick from the products, and I want to avoid manually populating a bunch of fields each time my products table changes.

I can't use a synced table of products and a relationship because obviously each product record (as a child) could only be associate with one related parent (submission) record.  
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    I'm leaning towards using a joined table, but there are some things you can do to make the "manual" part of it seem as if you are doing it all in one spot.

    While in the same breath giving you the reporting and insights to your data.

    Have you experimented with an "editable" child table on the main submissions form?  This would be your joined table, but you basically be a grid edit child list.  Makes it really easy to add multiple records at once.

    Your app set up would look something like this:

    Submissions --> (have many) Product Assignment
    Product Catalog --> Product Assignments

    Then you can create many child records from the Submission form, while using a drop-down from your catalog table.

    Give you the same affect as a multi-select table without the hassle of maintaining the list.
  • StephenStephen's avatar
    StephenStephen
    Qrew Assistant Captain
    8 years ago
    Many-to-Many. I've done this 10 times before. I can't believe I didn't think of that. Matthew, you are super kind to have shaken me out of my slumber...that's perfect.
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    Just to clarify, its not many-to-many.

    You will have a table between the 2, correct?  (I guess in a round about way, its similar to a many-to-many)
    But you are not connecting the submissions to the catalog, they are only connected to the joined table (Product Assignments / Selections)