Calculate from two Tables Monthly Rev against Monthly Exp

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.

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.

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

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.

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.