Skip to main content

I have two tables: Tenders and Companies. Each record in the tenders table is a tender (contract) between a governmental entity and a company. The tender table has a column "keyword" which is a way to group similar tenders.

I'll try to explain my problem with a demo base. Here are the two tables:

As you can see, the companies table has three columns for each keyword. It's of type 'count' (it is filtered only to count each keyword)

Now, to turn the table from wide data schema into a narrow one, I can just use the table optimizer script available in the marketplace. 

The issue appears when we talk about my real data and real base. I have 81 Keywords and the tenders table is updated every week so a new keyword might be added anytime. In order to follow the same procedure I explained with the demo base above, I need to create 81 columns for each keyword, and since the table optimizer script doesn't take count field (it needs to be text field) so you need to change these count fields to text fields. Oh, even though you then will be able to make the new narrow table, you still need to run the script on a weekly basis because the tenders table is populated with new records every week. 

All I want is a way to see how many tenders each company won in each keyword group. 

Any ideas?

 

 

 

 

 

You need to use a many-to-many relationship:

https://support.airtable.com/docs/understanding-linked-record-relationships-in-airtable

You need to use a many-to-many relationship:

https://support.airtable.com/docs/understanding-linked-record-relationships-in-airtable


Hi Scott, 

Thank you for tour response. 
But even with many to many relationship I still need to create columns thst counts a company’s tenders for each keyword. Could you elaborate on how that would solve the issue?

 

No you wouldn’t… you would just group by keyword in your junction table and you would get automatic summaries onscreen for each keyword. You’re missing the 3rd table that acts as the junction table. Check out the article above.

No you wouldn’t… you would just group by keyword in your junction table and you would get automatic summaries onscreen for each keyword. You’re missing the 3rd table that acts as the junction table. Check out the article above.


Got it. I can see the benefits of using a junction table. But I'm struggling to see how it would get me to the main piece of data I'm trying to reach: 

what companies won the most tenders in each keyword and how many tenders they won?

With the approach I demonstrated in my demo, I can just filter by keyword, sort by count and I get a list. And for more context, I'm using this data for a Softr website. So it's not just for internal use. If that were the case, I believe the pivot table extension can help me achieve my goal here.

 

 

 

 

 

Reply


Terms & Conditions