Relations (Advanced... I think)

So I understand how to connect one table to another - can you connect a table to itself for a many-to-many relationship?

In my app there will be multiple companies and each company will have multiple users. Thinking of this as a directory, how would I connect the companies to each other.

Assume that I am a Flooring Contractor and there are 100 other Contractors in the app. My company works directly to property owners, we Subcontract work to other companies, and we also subcontract work from other Contractors (companies). In some cases I might subcontract X improvement services TO a company while subcontracting Y improvement services FROM that same company.

Is this done with a company-to-company relationship within the same table, or do I set up one table for “My Company” and another table for “Other Companies” and use some sort of filter, or something else… I am SO confused!

1 Like

No, have all your users (companies, contractors, subcontractors, and clients) in one sheet… Users Sheet.

What you need to do is to create a contracts sheet…
Now, each contract will be a separate row that will have a user row ID column for the user giving a contract, and the user executing that contract… plus all columns describing this contract: date start, date end, price, the scope of work, location, payment agreement, terms & conditions, etc…

Then all events, like payments, expenses, messaging, workers’ salaries, and time cards… relate to the contracts row IDs that have these two users’ rows IDs

You can also add the Job sites sheet, and add their row IDs to contracts, for easy tracking all contractors in the building

Thanks for your response. I have a question or two but I am ADHD and can’t unlock my focus on something else long enough to word it so I’ll be back with them when I can redirect that focus! I just wanted to let you know that your reply was greatly appreciated!

1 Like

Yes you can. The basis for relations is that you are taking a cell value in a row and looking for any rows in any other table where it has that same matching cell value. You can create self relations to the same table if you have a value to match to.

So this is company to company, or company to users? Assuming company to company, you need some sort of value to match the companies together. What I would suggest is to first add RowIDs to your company table. Then add a Basic Text column to your table that will hold a comma delimited list of RowIDs. (Populating this column of comma delimited row ID’s can be done many ways. One way is with a multi-select choice component.) Then create a Split Text column to convert that comma delimited list into an array. That array can then be used to build a relation to other companies in the same table.

This is the basics of connecting multiple rows in the same table. Now if you are talking about contracting a specific job,I would probably have a separate Jobs/Projects table to enter each job. In there you could probably list the main contractor RowID in one column, and a comma delimited list of all subcontractors in another column. Then you can build relations to the Companies table the same way I outlined above.

But if you need specific details for each subcontractor on a job, then I would probably create a SubcontratorJob table.

Ultimately you would have a SubContractorJob table to record the Job/Project they are being subcontracted for, and the details about the work they are being hired to do…a Job/Project table to record specifics about the main job and the head contractor…and a Companies table to record details about each company.

strongly disagree with @Jeff_Hager, keep only 2 tables for this App, you will overcomplicate it for no good reason, I have done so many construction Apps… plus I have my own construction company.