Calculate from two Tables Monthly Rev against Monthly Exp

[Janet_Shivell]

Hi There, I am trying to find a way to compare Monthly Rev against Monthly Exp. Each one is from a different table. There are other tables that link off of these tables hence the Primary Field has a unique client name (Table 1) or unique contractor name (Table 2)

I have two tables:

Table One - Client Table (REV)
Client Unique (Primary Field)
Invoice Amount
Invoice Date
Month of Invoice

Table Two - Contractor Table (EXP)
Contractor Unique (Primary Field)
AP Amount
AP Date
Month of AP

I am making a third table that calculates the Monthly Rev against the Monthly Exp. I have done lookups and rollups but don’t quite have it yet.

Table Three: (Looking to do the following)
Invoice Amount - AP Amount by Month
I have tried linking the tables by Month that that is not working as the Month is not the primary field in Table 1 and 2. I am getting extra fields in those tables when I link table 3 to them. Also if I try doing a Rollup in Table three of any amount by month, I am only getting one amount for that month and not all the amounts.

Any suggestions?

[Justin_Barrett]

Janet_Shivell:

I have tried linking the tables by Month that that is not working as the Month is not the primary field in Table 1 and 2.

It doesn’t have to be. I suggest making the month your primary field in Table 3 (I’ll refer to this as [Summary]), and linking to these month records from the other two tables. Name the months either fully spelled out (January, February, March, …) or abbreviated (Jan, Feb, Mar, …), which ever you prefer.

Janet_Shivell:

I am getting extra fields in those tables when I link table 3 to them.

That’s normal. When you make a link from one table to another, you get a link field in both tables. While you’ll often only add links from one of those tables, you can actually edit the links from either side, and you need links on both tables in order to do rollups.

Using your [Client Table] as an example, you already have a field calculating the month of each invoice, I’m guessing using a formula. If that formula’s output doesn’t already match the month format used in your [Summary] table, make that change. For example, if you have fully spelled out the months in [Summary], your {Month of Invoice} formula would be this:

DATETIME_FORMAT({Invoice Date}, "MMMM")

Now you can copy these months from that field into the link field pointing to the [Summary] table, and Airtable will turn them into links to the relevant month records. With those links built, you can add a rollup field in [Summary] that sums all of the invoice values, so that each month record will have its own summary. Make a similar setup for the [Contractor Table], and you have the data required for your monthly comparisons.

While doing the copying and pasting of the months can be done manually, you could also automate it with the help of Zapier or Integromat, running a process (perhaps once a day) that automatically does the copying from the formula field to the link field for any record that doesn’t yet have a link.

[Janet_Shivell]

I still can’t get the it to work correctly here are a few screenshots of trying to pull the correct data from table 1 into table 3 (sum of inv amount by date)

Table 1:

Grouped by month total inv amount for that month is $183,761

Changed primary field to month

Table 3: Linked to Table 1 by month, Did roll up but rollup amounts don’t match

[Justin_Barrett]

Two problems that I see…

1. You said earlier that your primary field for [Table 1] was a “unique client name,” but in your screenshot of [Table 1], it has a date formula.
2. Based on the rollup value you showed in the [Table 3] screenshot, you’ve only linked the first record for January 2019 to the “January” record in [Table 3]. You need links for all relevant records (records 40-56 based on your screenshot). Only then will the rollup be accurate. Even though you’ve activated the setting for the {Rev Month} field that says “Allow linking to multiple records,” that doesn’t automatically make links to all records that match a single link’s pattern. What that does is allow you to make multiple manual links.

EDIT: Just to be clear, every record in [Table 1] that is for January has to point to the “January” record in [Table 3], every February record in 1 has to be linked to “February” in 3, etc. That’s why I recommended copying the {Month of Invoice} field values over to the link field, but not just for one record. That needs to be done for all records in the entire table. That’s also why I suggested considering using automation to make the links, so you wouldn’t have to do so much copying and pasting.

[Janet_Shivell]

Thank you Justin.

1. Table 1: I tried changing the primary field to month to see if that would work instead of a unique client name.

2. Rollup: I see It is more of a many process to bring in all the records. I will do a master cut and paste for now to see if I can make it work. we only have a few a month so it won’t be that much work monthly to add new ones once the back data is cut and pasted in.

Thanks for your help.