How to create a single View and support Reporting Requirement with a multiple table Join

DataGuru · ‎Jan 19, 2023

Background: I am creating a member database for a homeowner's association. The database has a PEOPLE table, that contains contact information, a SITE table, that contains information about the property (e.g., street address), and a Junction table, PEOPLE_SITE_ROLE, supporting the many-to-many relationship between People and Site. This junction table contains role information, such as Owner, Renter, etc.

Question: I need to join the information from these three tables into a single view for reporting. It appears that Airtable Views are limited to a single table. Is that truly the case? What is needed to create this single view and support my reporting requirements?

Sherman · ‎Jan 20, 2023

Are these three tables linked through Reference fields? If you set the shared view to be expandable, all information would be in any of the three tables by clicking into any associated entry right? I'm not sure I'm understanding what you're seeing. Maybe creating a fourth table and just cut and paste everything into it? If this is a yearly report, and you like how it's organized as is, that might be the easiest.

DataGuru · ‎Jan 20, 2023

Thanks for responding. I am fairly new to Airtable, but I have an extensive database experience using SQL. I'm trying to create a View in my Airtable database, that is the union of all columns in three tables, where they have linked relationships. In SQL terms, this is the same as an Inner Join.

Let me state my problem another way. Let's say there are two tables, Table A and Table B. Table A has columns a1, a2, a3, a4, and a5. Table B has columns b1, b2, b3, and b4. The primary keys for each table are a1 and b1, respectively. The two tables are linked through a relationship where a5=b1. A View of Table A shows columns a1, a2, a3, a4 and a5. A View of Table B shows columns b1, b2, b3 and b4.

Questions:

How can I create a View that shows columns a1, a2, a3, a4, a5, b1, b2, b3 and b4 for every record where a5=b1? Basically, this View will bring the information together based on the linked relationships. It would be a View that includes the information from 2 tables.
Is this even possible in Airtable?
If so, can I also create a View for a 3 table join, that shows all of the columns of Tables A, B and C, where a5=b1 and b4=C1?

Hope my examples help explain my conundrum. Thoughts?

DataGuru · ‎Jan 22, 2023

Apparently, there is no solution to this problem. How do I check whether or no Airtable is working to resolve this issue with one of its updates? If they aren’t, then how do I make a request ?