Associating records with multiple child records

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
  • (Edited)
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.  
Photo of Stephen

Stephen

  • 760 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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.
Photo of Stephen

Stephen

  • 760 Points 500 badge 2x thumb
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.
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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)