Skip to main content

I haven’t found a good way to Lookup (or Rollup) Multiple Records on other Tables, without having to manually select the records in the Link field.



Very simply: Assume I have a number of Records with the Column “Cost” in Table #1.



To reference the SUM of that “Cost” Column for use in Table #2, it seems that I first have to create a Link Column in Table #2 and then manually link all the Records from Table #1 to a particular Record in Table #2. And the resulting Link cell in Table #2 has then to be added to a Rollup to display the SUM of the “Cost” Column in Table #1.



This is of course very cumbersome, especially if you want to do the same thing for “Table #3” and so forth. There seems to be no way to create intersecting “Summary” Records, and the Summary Bar in the bottom cannot be referenced AFAIK.



A subset use case here, is when I don’t want to reference the entire “Cost” Column but only those that belong to a special Group (for instance a Single Select called “Picnic”) – ideally those records could be automatically selected, formulaically.



So my questions are:


A) Is the above correct, or am I missing a simple way to Summarize Records on “Table #1” for use in "Table #2?


😎 And is there some smart formulaic way, e.g. "If Single Select (a Group) is ‘Picnic’ in Column ‘Cost’ on ‘Table #1’ THEN SUM to Record on ‘Table #2’.



My apologies – this is a bit convoluted to explain in writing, but my question might be obvious for those who have used AirTable to do reporting. The difference between AirTable and regular spreadsheet programs seems to be that Vertical Summation and Referencing is not really possible without Manual Linking and adding Links and Lookup/Rollups.

If the items in the Single Select column are represented by the primary column in another table, simply convert the Single Select column into a Link column without clearing the contents of the column and it will automatically associate the records from the target table. From that point on, you simply use the linked records in the linked table as your options in place of the options in the single select column. Just make sure that you ensure multiple links per record on the categories table. Then you can do a sum(values) rollup on the Cost column of the table that you linked from.



Thanks Thomas, I think I understand your explanation.



I converted the Single Select Column in Table #1 to a “Linked Records” Column, with a link going to Table #2. This created a new Linked Column in Table #2 with the Table #1 Records pre-selected.



Since I already have a “Cost” Column dedicated for Rollup in Table #2, I then copied the entire Cell contents of the newly created Link Column’s first Record, and pasted the references into the “Cost” Rollup Column. I was then able to delete the new Linked Records Column whilst keeping the References in my “Cost” Column. Summation seems to have worked. One caveat is that the Single Select Column in Table #1 (which was converted to a Link Column) reverts to a Single Line Text Column when the new Link Column in Table #2 is deleted. So if one wants to make it a Single Select Column again, it has to be converted.

Reply


Terms & Conditions