How to set up many-to-many cross app relationship?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I looked around here but couldn't find any answers to my question, or maybe I'm just not interpreting it right, anyways, here it goes:

I have a table: Manufacturing Requests, and embedded on that form there is a child table called MR Items. I also have a table on a different app called Vendors. I want to set up a cross app, many-to-many relationship that after creating a new MR Item, would allow a user to select up to 5 "Potential" Vendors using 5 dropdown fields.

I already set up MR Items < Vendors, and Vendors < MR Items. Now I'm stuck on creating 5 dropdown fields on the MR Items form that can be used independently from eachother.
I added a text and numeric reference field on the MR Items form to select "Vendor 1", and a second set of text/numeric reference fields to select "Vendor 2." But when I choose an option for "Vendor 1," it automatically changes all 4 fields to the same selection.


Thanks.

*Edit*
I'm not sure what the fields are on the MR Items form, because when I click on Customize this Form, and select the elements in the left pane, they show as:
"numeric (reference)' and "text (reference proxy)"
Then if I click the Properties button in the right pane, they show as:
"text (lookup)" and "numeric (reference)"
Finally, when I right click the field on the form and click Edit the properties for this field, they ALL show "numeric (reference)"


Photo of Matthew

Matthew

  • 60 Points

Posted 4 years ago

  • 0
  • 1
Photo of Laura

Laura

  • 20 Points
For each "selection" you will need a separate relationship.

Typically you would name it [Related XXX 1] and [Related xxxx 2] etc.

Each relationship will have its own set of the same Lookup fields to share the information into the Child record.
Photo of Matthew

Matthew

  • 60 Points
So i set up 5 relationships of Vendors < MRF Items, cool.
In that case do I still need the many-to-many relationship? or can I remove the MRF Items < Vendors from the other app?
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
If you built a many-to-many table



MFR  < MFR Vendors > Vendors

This would provide you with a parent-child situation of showing multiple Vendors for a single MFR, but in a separate table.


If you link 5 separate relationships from your Vendors to your MFR, then this does the same thing but is limited in that it only allows you to add up to 5 vendors per MFR (because to add more, you as admin would need to build more relationships).


Much depends on what you are trying to do, and what your reporting outputs need to be. If you need to discuss further to make the right decision; feel free to contact me offline.
Photo of Matthew

Matthew

  • 60 Points
My goal is to only have 5 Vendors per Item, I've yet to see our expediter use any more than that. And if needed, I can always add more in the future. Thanks!
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
Then the question here is how you want to be able to see that information in your Requests table.  By creating relationships, you will have 5 sets of Related Reference fields, 5 sets of "Firm Name" fields and 5 sets of anything else you bring down from the Vendors table.

If you ever want to count by Vendor how many Requests they are assigned to, under your structure, you would have to do this by counting across 5 separate fields and tallying them together.  If you keep your many-to-many join table; then this would be a single relationship and so therefore one single count-summary field.

If it is more effort for your User to "create" up to 5 records, than to simply select from 5 separate relationships, then go with the 5 relationship structure versus the many-to-many join solution.
Photo of Matthew

Matthew

  • 60 Points
Okay I see what you're saying. I don't think we would need to ever generate that type of report, because out of the 5 "Potential" vendors, only one will be selected in the end. And if I play my cards right, if we do end up needing that kind of information, I could generate # of requests per Vendor based on the vendor that won the bid. And the only field that I'm pulling from the Vendors table is the Vendor Name.

I do have one more question, I've created the 5 relationships, which generated 5 "Related Vendor" fields. Then I added a text-lookup field for the Vendor Name because the related field was only displaying the rid #. But now that I have the Related Vendor as well as the Related Vendor Name lookup field, they both are displaying the Vendor Name. So my question is, which field do I keep for data enrty? The numeric "Related Vendor" or the "Vendor Name" lookup? I haven't come across this before, usually QB give me the correct field, but I think by creating the lookup fields after the fact, I've just confused myself.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,760 Points 3k badge 2x thumb
Your Related Vendor is a Reference field, but you probably have set the Proxy field to be the Vendor Name.  On the Form, you only need the Vendor Name, and it will populate the Related Vendor field.  You still need both fields in the relationship though.  If your Related Reference field does NOT have a Proxy set; then do this:  Make your Related Reference field visible in Add/Edit mode only and make your Vendor Name lookup field visible in View mode only.