Linking multiple records

[Rena_O_Brien]

Hey there! I’m currently moving my company’s DB over to a new system and have decided to give AirTable a try. We’ve been on Googlesheets for far too long.

I currently have a DB of about 250 customers, with multiple columns for their info. The most important one is the length we are working with them, which I am trying to populate from a Master sheet into their respective months.

For example, master sheet has the 250 clients, and then the month(s) categories they are in. So Sally is in Cat A for ‘March, Apr, May’ and then moves into Cat B for ‘June, July, Aug’. I have 12 months of tabs, and am trying to figure out a way that if I put ‘March’ under Sally in the Master, she will autopopulate to the March tab. Is this possible? Like I said, very new to this, so TIA!

[W_Vann_Hall]

Typically with Airtable, you wouldn’t track two different types of data — people and service dates — on a single table. Instead, you’d have a [People] table and a [Service Dates] table, and you would use a linked records field to relate the two.

Or is that what you mean by “I have 12 months of tabs” — that you have 12 month tables? If you can post a little more about what you’re trying to do, I’m sure someone here can help.

(Easiest way is to post more is to click ‘share’ on your base, select ‘Create a shared link to the whole base,’ select ‘Create a private, read-only link,’ and post the URL, making sure you selected ‘Allow viewers to copy data in this base.’ You probably want to duplicate the base first [click that drop-down by the base name and choose ‘Duplicate base’] and strip out or obfuscate any sensitive data; if that’s not feasible, feel free to send me the link by private message [click on my name and select ‘Message’] and I’ll take a look.)

[Rena_O_Brien]

Thank you! Here is the test DB. https://airtable.com/shrDbKKI9MAYDALK1

I’d essentially want those rows with a certain month (so say March) to autopopulate from the Master. If they are on multiple months, they would populate to their respective sheets (April, May, June).

I’m also trying to wrap my head around getting certain columns to populate along with it, as it seems unnecessarily complicated, where as in sheets you can just say A:G and all that info will be populated in their respective sheets.

Thanks again!

[W_Vann_Hall]

So here’s how I’d do it. I’d keep only two tables: [Master] and [Trips]. (I didn’t delete the months, but you can.) I’d also separate data about the clients from data about their trips. When you add a new trip for a client, click on the plus sign in {Link2Trips} and select ‘Create new record.’ The [Trips] record will pick up the email from [Master] and, once you enter {Trip Name} and {Trip Start Date}, will give the record a unique key.

I made a few command decisions — for instance, instead of using separate month tables, once you enter the start date and duration, the first and last months are calculated automatically and can be used to pull reports, track clients, and so forth. I also added a {Current Trip} checkbox; it simply allows information about the current trip only for repeat customers to bubble up to the [Master] table. In practice I’d probably select the current trip based on date or some other data field, but a check box demonstrates the functionality.

Almost none of the violence I wreaked upon your base is mandatory. :winking_face: This is merely a serving suggestion. If you have questions or need assistance, don’t hesitate to ask.

Airtable

Trips Rework demo - Airtable

Explore the "Trips Rework demo" base on Airtable.

[Rena_O_Brien]

Thank you!! Unfortuately won’t work for what I’m looking for but I appreciate the help!

[W_Vann_Hall]

Well, in that case just steal the code that pulls data from one table to the other. (Look for the fields having to do with your clients – name, etc. – in the [Trips] table, and the fields having to do with the trips in the [Master] table. Those are the routines you need to do what you originally asked and replicate data from the master table to the individual months.

I know it seems like a lot of trouble to go through up front as compared to simply referring to another cell elsewhere on a spreadsheet, but I promise you it will be less work and less heartache in the long run. When you replicate data the way spreadsheets do — to say nothing of the issue of linking two items together not semantically but by random alpha pointers — you’re increasing the number of places where data can get out of sync between copies. With a RDBMS such as Airtable, on the other hand, there is only a single instance of any given data item [in a well-built base]; anything that needs to know the value of that item does so by referring to the item. As a consequence, if something changes — a phone number, perhaps, or contact info — you change it once, and it’s instantly updated everywhere.

I once bought a newspaper (not from a vending machine, but from the aging hippies who’d managed it since the mid-60s) where the accounting ‘system’ was an incredibly baroque scaffolding of spreadsheets. As it had developed, every time a data integrity problem had occurred, it had been ‘fixed’ by building another set of sheets where the same data had to be entered a second (or third, or fourth) time. The system compared these various inputs as a way of making sure nothing had been missed. It wasn’t double-entry bookkeeping: It was quintuple, maybe even sept- or octuple-entry bookkeeping. We had to keep one of the former owners on the payroll for a year to do nothing but validate one part of the system against another.

One of the first things I did was begin to move accounting to a database-, um, -based system. While I never succeeded in killing off the old application entirely (a few things came up, like being sued for $3.5 million by a self-styled competitor who used to leave me long, rambling messages claiming an employee of mine — in reality, at the time dead for 20 months — had broken into his office or stolen newsracks from the back of his truck), but the sections I did replace were so much easier to maintain.

OK, end of commercial! :winking_face: