Getting parent records to be related to multiple child record

Question

There is a Master Copy and the Master Copy has a multi select with the master SOP names.

I have a document storage app which has a table named Manuals and a table named Supporting Documents. Each has a File Attachment field of the same name. Each Manual has multiple Supporting Documents. This is handled by a standard one to many relationship.

I found out today that each Supporting Document could potentially ?support? multiple Manuals. This could be handled by simply uploading the Supporting Document file multiple times, each record being related to a different Manual. Due to storage space and modifications of files I do not want multiple Supporting Document records that contain the same file attachment.

I tried to use a multi-select field in the Supporting Documents table that would check off the multiple Manuals, and through a report link field link the Supporting Document to the Manual records. This won?t work because there are over 250 Manual records, and the Multi-select can only handle 100 values and 20 selections. Otherwise it would have worked.

I could create a regular text field and manually type in the names of the Manuals that the Supporting Document supports, but I don?t want to do that either.

What?s the cleanest way to get one Supporting Document to be able to be related to multiple Manuals?

quickbasecoachd · Answer

This sounds like a classic many to many relationship situation.

One manual has many supporting documents but also one supporting document supports many manuals.

The way this is done Quick Base is to create a middle table called supporting document manual assignments.

One manual has many supporting document Manual assignments.

One supporting document has many supporting document Manual assignments.

One the manual record you put the report link field to show the supporting document Manual assignments right on the form as an embedded report. .

On the supporting documents you put the the report link field to show the supporting document Manual assignments.

There is also a way to migrate your existing data over to the new middle table.

When you are done you will delete the relationship where 1manual has many supporting documents.

heatherbryant2 · Answer

Thanks, Mark! I knew I was overcomplicating something.

So in the intermediary table, am I creating a record for each Manual to Document or Document to Manual instance?

How does this control only needing to upload a Supporting Doc File Attachment one time?

quickbasecoachd · Answer

Re:So in the intermediary table, am I creating a record for each Manual to Document or Document to Manual instance?

Answer: yes.

Re:
How does this control only needing to upload a Supporting Doc File Attachment one time?

Think of a more traditional situation. I like to use an Order. Imagine a table of Orders and a Table of Master Items. The order have lines items on them because an order may have several items on the order, each with a price and qty.

The line items on an order will be order lines, but really they are Order Item Assignments, because obviously 1 order has many order lines and obviously you don't sell an master file part # ABC just once, you hope to sell it many times on many orders.

But if something changes on the item master like a file attachment for an image or the selling price, you just maintain it once in the item master. You don't have to change it on all the order lines.

So same with you - the supporting document is only maintained once.

btw, once you get that working, you will then be asking me how to make a link so the user can click on a link on the middle join table to see the supporting document. I will post that soon.

rbuschmeyer · Answer

so I have a similar issue, just not the n:m...

We have a parent record (a Campaign) that may include multiple Opportunities (child table). The Opportunities table is already populated (and synchronized with Salesforce)...so we aren't creating a child record here, just linking it.

I think I have done this in an application in the distant past, but cannot recall the most elegant way to accomplish the multi-select of the child records (or a search for that matter)

markshnier__you · Answer

Ryan and I plan to Zoom to get this working with a "Quick Select" solutions which leverages the "User Focus" technique...Mark

Forum Discussion

Getting parent records to be related to multiple child record