Keeping a junction table up to date

Hi guys,

I hope you can help me with this Airtable I’m creating for a client of mine who is an accountant. I’m still a beginner (only used Airtable in my own small business for basic projectmanagement/to do stuff).

Outline:
My problem has to do with keeping a junction table up to date.

Goal:
My client uses an extensive checklist to keep track of vat-tax returns he does for his clients. All his clients are companies. Some companies have to report their vat-taxes monthly, some yearly, most quarterly. He wants to keep records of this checklist per year per company per term.

My solution:
I created a table ‘companies’ which stores all basic information. Then I learned about junction tables and gathered I would need to use them in this case. I created a table ‘year’ and a table ‘term’. The term table contains all term options (‘monthly’, ‘quarterly’ and ‘yearly’). The ‘year’ table contains… years! :slight_smile:

Then I created the junction table itself. It’s called ‘VAT-taxes’. I linked to the relevant other records (‘company name’, ‘year’ and ‘term’) and the primary field consists of a formula (‘company name’ + ‘year’ + ‘term’). Then I filled this table by copy and pasting the relevant companies into the linked field ‘company name’.
So far, so good. I can group fields to my hearts content to create really helpful views.

The Problem:
The problem arises when:
A. a new company is added (which happens quite often).
B. a new term begins (either year or quarter) .
C. a company changes their term.
In all these cases the table ‘Vat-taxes’ is no longer up to date.

For problem A. I tried using an automation to create a new record in the table ‘Vat-taxes’ whenever a new company is added to the table ‘Company’. However, I can’t find an option to make this creation conditional. Not every new company needs to be in this table. Some of the companies do their own vat-tax.

For problem B. I could only come up with a ‘copy and paste’ solution: When a new term begins I create a new record within the junction table with the updated values for ‘term’ and ‘year’. I then copy and paste all the relevant companies from the ‘companies’ table to the just created empty ‘linked to company name’ field, and let Airtable expand this table. I got this nagging feeling this is not the right way to go about this. Feels very makeshift, workaroundish.

Problem C. does not actually seem to be a problem. Changing the term in the table ‘companies’ does not affect the already existing entry in the junction table.

Thanks very much in advance for anyone trying to help. I really appreciate it. I am sorry if this has been answered before. I did do a search. I’m beginning to wonder if I bit of more Airtable than I can chew…
I cannot provide you with the base in question because of privacy concerns but I can make a replica of my base with fake data if that would be helpful.

Kind regards,
Tijs

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.