I am trying to relate two tables: Companies and Orders. Orders can have multiple companies and companies can have multiple orders. For example companies are split into four types: Customer, Vendor, Freight, and Storage. One order can have all four types of companies. Can I repeat the same table to table relationship between Companies and Orders 4 times? I've looked into a join table for many to many relationships but that doesn't solve the issue. I would still need to select up to 4 companies within one join table. Any direction is appreciated.
A "standard" many-to-many relationship with a join table would work as follows:
- Company-Order Assignment (join table)
1) 1 Company has many Company-Order Assignments
2) 1 Order has many Company-Order Assignments
Say you have:
- Companies: Customer C, Vendor V, Freight F, Storage S
- Orders: Order 1, Order 2
For Order 1:
- create 4 Company-Order Assignments
1) Order 1 - Customer C
2) Order 1 - Vendor V
3) Order 1 - Freight F
4) Order 1 - Storage S
For Order 2:
- create 2 Company-Order Assignments
1) Order 2 - Customer C
2) Order 2 - Freight F
Within each Company-Order Assignment - you're only selecting 1 Order, and 1 Company.
But you can assign as many Company-Order Assignments as you want - for a particular Order, or for a particular Company.
Here's the online help for Many-to-Many Relationships - which describes the process above (and which I think you looked at).
Would this work for your situation? Maybe I misunderstood what you're trying to do.
You may always be concentrating on one side or the other (on Company or Orders) - but the fundamental setup is STILL a many-to-many relationship, and the join table is the standard QuickBase way to handle that.
I'd suggest perhaps creating a new test app - and try setting up the join table as described. Then you can try out a few scenarios and see how it works. There are good reasons to do it that way - and we can discuss the pros and cons. It's easier to have that discussion with a working example in front of you.
EXAMPLE - Companies & Orders
You can go ahead and download it, and see how it works.
In this set up - you can relate multiple Companies to a single Order, and you can see which Orders are related to a given Company. You can also add a new "Type" for a Company, if you want to add additional categories beyond Customer, Vendor, Freight, Storage.
Let me know if you have any questions!
If a Contact could belong in multiple Companies at the same time, then you will want to set up a similar many-to-many relationship with a join table. I'd say this is probably more unusual.
- all "Order Relationships", sorted and grouped by Order
- all "Order Relationships" where Type = Vendor
From the Orders table - you'll see the information as an embedded report, or as a summary field (# of Relationships, # of Customers, etc.).
You may be able to pull some limited data from the child table ("Order Relationships") up to the Order table - but it depends on what you're trying to do.
- the parent table would be the "Order Relationship" table
- the child table would be the "Order" table
You can search the community posts for "reverse relationship" - one explanation is here in this link:
Then you can use the "maximum" or "minimum" of the Record IDs - to select the "last" Customer, the "last" Vendor, etc. - and then do a lookup on the reverse relationship to pull it back into the Order table.
What exactly are you trying to do? e.g. are you trying to display "Customer C - Vendor V - 3/19/2015" on the Order table? Will that format always be the same? (always the first Customer, always the first Vendor, etc.) What happens if you have more than 1 Customer, more than 1 Vendor - on an Order? (or you don't anticipate doing that?)
So you have to weigh the pros and cons. If you go back to doing multiple relationships (to accommodate multiple Customers, Vendors, additional categories, etc.) - then that's somewhat more work to maintain, but then you can create fields such as "Order # - Date - Customer 1 - Customer 2 - Vendor 1 - Vendor 2".
If you use the join table, then it's easier to maintain and run reports in a standard way, but you can only see the full list in an embedded table (or as a link to the list of Order Relationships for a particular Order).