Skip to main content

I am a lifetime Excel user, so forgive me thinking like a spreadsheet and not a database, but…here is my problem:


I manage an Oyster farm. I have a table that logs the oysters that are harvested. I have another table for the demand for the oysters. I want to net the two against each other and see whether I have enough.


As my table of Harvested oysters is set up so each day the team either enters the number of oysters they harvested (a positive number) or the number of oysters they delivered (a negative number). Thus, choosing “sum” at the top/bottom of each column will show me exactly how many oysters I have (summing all the postive/negative numbers to date).


And as my table of Demand for oysters shows the number of oysters requested by each client on each date, I can group those by day/week/month, and look again to the “sum” of the column to see how many oysters are needed.


But I can’t see how I create the Sum of all the oysters I have (the sum of Harvested Oysters table) less the sum of the Demand for oysters table. If I was working in Excel I would simply enter the formula: sum(column 😵 - sum (column Y).


I assume I cannot directly access the numerical “sum” that Airtable shows me for a column, but is there anyway to get that number?


Again, all I want to do is subtract the sum of one field over a bunch of records from the sum of another field over a bunch of records.


Any ideas?


Thank you.

Here’s one idea:


You would have 3 tables:



  1. Harvested/Delivered Oysters

  2. Demand

  3. iInsert Desired Name Here] where you would do all the calculations


What this requires is that you would have to link tables 1 & 2 above to a record in table 3. Once all records are linked in a single table, you could then use a Rollup to sum whatever fields you want from tables 1 and 2, respectively, and compare/add/subtract these aggregated numbers in table 3.


Just my quick two cents, hope it helps! I have used this method to create a type of ‘dashboard’ if you will. It just requires that every record you want to calculate gets ‘linked’ to the dashboard (in this case a record in table 3) in order for you to pull the data.

Here’s one idea:


You would have 3 tables:



  1. Harvested/Delivered Oysters

  2. Demand

  3. iInsert Desired Name Here] where you would do all the calculations


What this requires is that you would have to link tables 1 & 2 above to a record in table 3. Once all records are linked in a single table, you could then use a Rollup to sum whatever fields you want from tables 1 and 2, respectively, and compare/add/subtract these aggregated numbers in table 3.


Just my quick two cents, hope it helps! I have used this method to create a type of ‘dashboard’ if you will. It just requires that every record you want to calculate gets ‘linked’ to the dashboard (in this case a record in table 3) in order for you to pull the data.


Thank you Alex. I have played with this solution, and keep retreating from it because, as you say, it seems I have to manually link every record in both tables. As the months go by, that is a lot of records! And, forgetting just one of them would give me the wrong answer. (I realize its not that hard to click in the harvested table, look at the sum, then click in the demand table, look at the sum, and see which is bigger. But, I have simplified the example to find out if there is an automatic solution. In reality, I have four different styles of oysters, so there are four fields of harvested, four of demand, and thus the potential for human error is four times bigger!)


Thank you again for your thoughts. If there is no other solution, I may go this route - ensuring I have linked every possible day of the year before I do my rollup sum!


Best,

Thank you Alex. I have played with this solution, and keep retreating from it because, as you say, it seems I have to manually link every record in both tables. As the months go by, that is a lot of records! And, forgetting just one of them would give me the wrong answer. (I realize its not that hard to click in the harvested table, look at the sum, then click in the demand table, look at the sum, and see which is bigger. But, I have simplified the example to find out if there is an automatic solution. In reality, I have four different styles of oysters, so there are four fields of harvested, four of demand, and thus the potential for human error is four times bigger!)


Thank you again for your thoughts. If there is no other solution, I may go this route - ensuring I have linked every possible day of the year before I do my rollup sum!


Best,


Just a note, but with the 3-table solution, you could also aggregate the data by oyster type in Table 3 - if that is valuable to you. You could do this by adding a formula to Tables 1 and 2 to separate the costs out by oyster type, month, year, etc. and roll them up in Table 3 as needed (granted, that’s separate fields/columns for each type/month/year/etc.)


Also, Airtable has the handy drag-and-insert type of function of an Excel cell (bottom right corner when you click a cell) which I use to link multiple records at the same time (link one, drag to the others). Once you get used to it it becomes second nature when creating new records - but maybe one day there’ll be a more automated version of this in Airtable - however, for now, that’s my best guess!


Good luck

Just a note, but with the 3-table solution, you could also aggregate the data by oyster type in Table 3 - if that is valuable to you. You could do this by adding a formula to Tables 1 and 2 to separate the costs out by oyster type, month, year, etc. and roll them up in Table 3 as needed (granted, that’s separate fields/columns for each type/month/year/etc.)


Also, Airtable has the handy drag-and-insert type of function of an Excel cell (bottom right corner when you click a cell) which I use to link multiple records at the same time (link one, drag to the others). Once you get used to it it becomes second nature when creating new records - but maybe one day there’ll be a more automated version of this in Airtable - however, for now, that’s my best guess!


Good luck


Final thing, here’s the best example / where I learned this from (see the dashboard in this base):


Thank you Alex. I have played with this solution, and keep retreating from it because, as you say, it seems I have to manually link every record in both tables. As the months go by, that is a lot of records! And, forgetting just one of them would give me the wrong answer. (I realize its not that hard to click in the harvested table, look at the sum, then click in the demand table, look at the sum, and see which is bigger. But, I have simplified the example to find out if there is an automatic solution. In reality, I have four different styles of oysters, so there are four fields of harvested, four of demand, and thus the potential for human error is four times bigger!)


Thank you again for your thoughts. If there is no other solution, I may go this route - ensuring I have linked every possible day of the year before I do my rollup sum!


Best,


Something I’ve found that seems to simplify (at least mentally :winking_face: ) the task of ensuring all data records are linked to an aggregation record has been to set the primary field of the aggregation record to equal ‘ ✅ ’ — the White Heavy Check Mark emoji (which often renders as a white check against a green field). Then, after creating a new data record, the user selects the ‘plus’ sign within the linked record field. This causes him or her to drill through to the aggregation table, which contains only a single record. The user selects that existing record; the drill-through window closes; and the link field in the data record displays as a check mark — a handy and easily recognized indicator the record was correctly linked.


I’ve used this in several bases; one of the first, I believe, was a demonstration base I created to illustrate how to manage a running calculation (e.g., difference in value between today and yesterday) in Airtable. Unfortunately, due to all the normally hidden fields being exposed for the demo, the check box doesn’t have the same visual impact as usual, but you can get an idea of how this might serve as a mnemonic device.


Thank you Alex. I have played with this solution, and keep retreating from it because, as you say, it seems I have to manually link every record in both tables. As the months go by, that is a lot of records! And, forgetting just one of them would give me the wrong answer. (I realize its not that hard to click in the harvested table, look at the sum, then click in the demand table, look at the sum, and see which is bigger. But, I have simplified the example to find out if there is an automatic solution. In reality, I have four different styles of oysters, so there are four fields of harvested, four of demand, and thus the potential for human error is four times bigger!)


Thank you again for your thoughts. If there is no other solution, I may go this route - ensuring I have linked every possible day of the year before I do my rollup sum!


Best,



Me too! I can’t believe this is the only solution available for this very common problem. Have you found anything better in the last two years since this was posted?

Reply


Terms & Conditions