Autofilling one table based on a master database

[Nathaniel_Duebe]

I work as a freelance production manager which means working a lot of different jobs with different crew members and vendors.

I have a “master” crew and vendor database, which is everyone I know.

When a gig comes around I want to be able to make a separate crew list (table) that will pull records from the master database and autofill their info across various columns.

For example if I write “John Smith” in the “Name” section of my job table I want it to search the master database and automatically fill their job, department, email, cell, dietary restrictions, etc. that correspond to their record.

Is there any way to achieve this?

[W_Vann_Hall]

Yes; take a look at the section on linked records. In your base, you’ll have a [People] table containing all the person-linked data you mention in your last paragraph.

You’ll also have a [Jobs] table where each record contains job-related fields plus one or more linked records fields that link from [Jobs] to [People]. Rather than entering ‘John Smith’ in your job record, you’ll select the linked record field, while will drill through to the [People] table, and click on ‘John Smith’'s record.

To access details concerning ‘John Smith,’ you can either follow the link as needed; surface the person-specific data in [Jobs] by using rollup or lookup fields, or a combination of the two methods.

To make things easier to manage for jobs with a large number of crew members, you may want to use a junction table. Instead of linking directly from [Jobs] to [People] you would link from [Jobs] to a record in the, say, [Gigs] table. Each [Gigs] record would in turn link to a [People] record. You would also define the lookup and rollup fields surfacing [People] data in [Gigs].

Alternatively, you could retain the [Jobs] :left_right_arrow: [People] link and use such ancillary tables as [Lunch Catering], [Parking Permits], or whatnot to surface the appropriate data in a usage-specific context. (As I guess is obvious by now, base design should be usage- and process-driven as much as, if not more so, data-driven.)

[Nathaniel_Duebe]

I’m having a little trouble wrapping my head around having a table just titled [Jobs], would there be confusion having every job in single table? For perspective, my current workflow is basically having a crew list in an individual excel/sheets spreadsheet w/ all the crew/vendor information pertaining to just that job.

Initially, I thought I’d have a separate table for each job.

Say for instance I have 2 jobs running at the same time, “Music Video” and “Commercial”, each one has different crews and vendors I want to keep separate. Wouldn’t it make more sense to do a different table for each one to keep the information separate?

Re: Accessing crew details, I don’t want to have to follow the link. I’d need all the crew member’s information to appear across the columns in the table for that given job.

Basically, I want to be able to export a crew list pertaining to particular job to either a .csv or a PDF once the job is finished so we have it for our records. Once that job is wrapped and I have the final crew list exported I wouldn’t need that unique database anymore.

[W_Vann_Hall]

Well, if you’re reimplementing an existing system, you might be more comfortable emulating the known application. If I was building it from scratch, though, I’d follow something similar to the architecture I laid out.

Right now you have a separate spreadsheet per job. In the system I proposed, you’d have a separate [Jobs] record per job. The analogy isn’t that good in either direction, but an Airtable record is structurally more kin to an Excel spreadsheet than it is to a spreadsheet row. (The spreadsheet metaphor is both blessing and curse for Airtable. On one hand, it makes it very easy to grasp the concept of Airtable; on the other, though, it can lead to a very incomplete understanding of what the product is, does, and is capable of.)

The problem with using a new table for each iteration of something (in this case, each new job) is that table creation is fundamentally a development task. That is, each time you create a new table, you have to define each field, each formula, every relationship, and so forth. In comparison, creating a new record is an organic process encountered as an unexceptional occurrence during normal operations. Each newly created record incorporates the same complement and arrangement of fields, ready to be populated without additional development required. And since each record is a discrete entity, your music video record will overlap your commercial record only as much or as little as you desire.

Again, though, ultimately the best solution is the one you’ll actually use. A higher comfort level might, in the long run, outweigh any negative effects from using a less-than-optimal architecture.