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! :slightly_smiling_face:
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
