Re: Formula to Copy into Multiple Other Fields

Scott_Hedges · ‎Jul 17, 2022

I have two Tables, each have a Primary Field of Date.
Table 1 has a row for each day of the month.
Table 2 has multiple rows for each day of the month.

My Challenge - How do I take a single field’s data in Table 1 and apply/copy it to a field in Table 2 for each row that has the same date?

For example, in Table 1 I have a column called Invoices Processed. I want to take that number of Invoices Processed from July 15 in Table 1 and copy it to each field in Table 2 with the date of July 15.

Please Help!

Ben_Young1 · ‎Jul 17, 2022

Hey @Scott_Hedges!

So, I find that thinking about the “design” of your data is one of the most essential parts of working with Airtable.

I’m curious as to whether you’re utilizing linked records since copying and pasting information is more in line with the usage of a spreadsheet rather than a database like Airtable.

I’m not sure I fully understand exactly what you’re trying to build, so I created something similar to what I imagine your base looks like.

Toss some clarification and more specific requirements to me, and we will get you precisely what you’re looking to achieve.

First, I created a " Calendar " table with records for individual calendar days.

From there, I created a “Transactions” table.
(I know you’re tracking invoices, but I went with transactions for simplicity.)

Here, each record is for an individual transaction.
There’s the transaction name, the amount, and whether it’s classified as inflow or outflow. They are then linked to the date they occurred on.

I used a formula field to quickly identify the records to build a unique tag for each transaction record.

Since all my transactions are cleanly related to a date record, I can build some powerful reporting tools such as income reporting. It’s just a question of what you want to see once you get to that point.

Here, I used some rollups and a few formula fields to do some quick math and formatting to provide critical information on daily performance metrics.

Again, take a look and toss back anything if I’m missing something or have misunderstood what you’re looking to build.
If something doesn’t make sense, don’t hesitate to let me know, and we can go into more detail.

Scott_Hedges · ‎Jul 17, 2022

This is on the right track, Ben. Thank you!
In the sample you built, if you add a column that is an input for the number of Sales People that day. Then I want to be able to have that number of Sales People be copied on the Transaction table for each row that matches that same date.

My goal is track how many transactions we had that day per Sales People. Make sense?

Ben_Young1 · ‎Jul 18, 2022

Here’s a link to the base I built this in.
Feel free to copy it into your workspace and poke around.

So there are probably a few ways to go about achieving that.

No matter what you choose, you’ll want to create a table for your sales team like this:

Once you do that, you can either choose to link them to a business day, or you can link them to an individual transaction.

If you choose to link them to a business day record, then you’ll have more flexibility over things like staffing, but you lose a few reporting metrics.

Here you can see that the sales team records are linked to the business days.
You can now create a lookup in the transactions table that will show every team member working that day.

Here’s the limitation:

You can’t easily identify and associate individual transactions with sales team members.
This isn’t a problem if sales performance isn’t attached to a given team member.

Going with the method does allow you to say:

I have an employee assigned to these days, and I want to see the historical team sales performance of the days that they worked.

So if I take the performance metrics from the business days, I can create a rollup that breaks down that data to the days they’ve been assigned. This is probably a significant advantage in looking at macro-trends in how your team is assigned.

e.g. Team A performs 62% better than Team B. I can switch people between teams in order to balance their performance since this team member has a high historical trend etc.

If you decide that you want to link team members to individual transactions, you get a new suite of advantages.

You can now break down how many lifetime sales someone does, the average amount of all their sales, or look at the average amount of all of their sales.

Same as the previous example, you can do a lot with it.

Again, let me know if you have any questions or want to go into something a bit more.

Quick Edit

There is a third option that actually allows you to have everything linked, unlike the two methods I displayed.

It would allow you to link a transaction to a team member and business day whilst also having the team member record link to the business day record.

There’s a bit of nuance that goes into it.
It’s not the most technically involved thing, but it’s more of whether you understand how it all works and how the relationships are drawn.

When you start creating many-to-many relationships that are layered on other many-to-many relationships, you can quickly build data bomb that wrecks your single source of truth.

If you know exactly how your base is built, then you’ll be able to keep a leash on it and maintain clean data.

If you want, I can walk you through how to implement it. Just beware of the potential complications.

Scott_Hedges · ‎Aug 01, 2022

Ben!

May I hire you to do this for me? I can’t get your suggestions to work in my existing base. Please let me know, thanks!