I work in film and video production. When I bring on a crew, I have them fill out a form which lists name, e-mail address, mobile phone, food allergies, scheduling conflicts, their production department, and their production title. The problem is, this information needs to be distributed into two different tables:
One table holds name, e-mail, mobile phone, & food allergies-- that information tends to stay the same from production to production, so I keep that in a “master contact table” (which I also use as my general address book)
Another table (the “Roster” table) holds the person’s scheduling conflicts, production department, and production title-- that information differs from production to production, so I don’t want to keep it in my master contact table because the same contact might work on different productions in different roles and I need to keep track of who did exactly what on each production.
(Yes, I could just keep it all in the “Forms table” and try to manage everything with filtered views, but that would be unwieldy because each contact could be entered multiple times-- making it clunky as an address book)
Because air table forms only allow information to go into a single table, I have to go through some crazy gymnastics in order to get the information from the crew form into the appropriate fields. Currently, I do this:
I have a THIRD table, a “Forms” table which collects information for all of the fields.
I have a Zap which 1) pulls the name/phone/e-mail/allergies info from the forms table into the master contact table (updating an existing record or creating a new record as needed) 2) copies the name/department/role/scheduling conflicts info into the rosters table 3) links the name record in the rosters table to the name record in the master contact table (and finally) 4) deletes the record in the forms table once all of the information has been moved into the correct place.
This multi-step Zap costs $25/month.
However, if I could create a form with field inputs from multiple tables (instead of being limited to my form only inputting information into a single table), I could save myself a lot of money and headaches.
