I am fairly new to Airtable and I am building a process management tool for my restaurant business.
What I am struggling with is the junction table and many-to-many relationships. I have read every example that Airtable provides, like the junction table one with students and classes. Am I understanding it correctly that the junction table has to be manually completed with all possible combinations?
In the project that I am working on I have this same issue with vendors and processes. For each vendor we have to follow many processes and each process can be associated with many vendors.
I therefor created 2 tables. Table 1 shows all vendors and is linked to Table 2 which lists all processes like Record Invoice, Pay Invoice, Place Order, etc… These processes are not the same for each vendor.
I linked Table 1 & 2 and added the processes for each vendor in the linked field which generated the opposite on Table 2. Great so far!
Now each combination Vendor 1 + Process 1, Vendor 1 + Process 2, Vendor 2 + Process 3, etc… needs to become a “Task” in Table 3 so that each task can be assigned to an employee. Vendor 1 + Process 1, might be done by Person A, where Vendor 1 + Process 2 might be done by Person B and Vendor 2 + Process 1 done by person C.
In an ideal world, I would want Airtable to generate a list with all possible combinations that I provided in the Linked Table 1 and 2 in a new Table 3. Much like the junction table referred to in the many-to-many example listed here.
I am not sure if my example here is unique in the way that it is of course much easier to add the processes for each vendor in the vendor table and therefor autofill each vendor that is associated with each process in table 2, than it would be to manually fill in each possible combination as its own record in table 3.
I attached a picture of the Table 1, vendors, and Table 2, processes, here.
I would be happy to build a Zap for this too if need be. I just need to make sure that when this is in use and we add another process to a vendor that I can generate a separate task out of it by combining the new combination in table 3.
Hope this all makes sense and thanks for reading my long question.